#1239
Issues/stylessh/supavisor/#1239

Transaction mode: 4+ pipelined statements on one client connection hang forever

Openrichardwattieopened 13d ago

Summary

In transaction pool mode, if a client sends four or more top-level statements on one connection without waiting for each result (pipelining), the connection stops answering and every pending query waits forever. With two or three statements it completes. The same statements succeed when run one after another, or inside one BEGIN … COMMIT. Postgres logs nothing, and the backend sits idle.

We hit it in production-like code on AWS Lambda, where each database client has one connection (max: 1) and a page issued ~16 reads with Promise.all. The Lambda waited until its timeout every time.

Environment

  • Supavisor 2.9.7 (Supabase CLI local stack, [db.pooler] enabled = true, pool_mode = "transaction", port 54329)
  • postgres.js 3.4.9, max: 1, prepare: false
  • Node 24
  • Postgres 17 (Supabase local image)

Minimal repro

// npm i postgres@3.4.9
import postgres from 'postgres';

const sql = postgres('postgresql://postgres.pooler-dev:postgres@127.0.0.1:54329/postgres', {
  max: 1,
  prepare: false,
});

const all = Promise.all([
  sql`select 1`,
  sql`select ${1}::int`,
  sql`select 1`,
  sql`select ${2}::int`,
]);

console.log(
  await Promise.race([all.then(() => 'ok'), new Promise((r) => setTimeout(() => r('HANG'), 4000))]),
);
process.exit(0);

Prints HANG every time. Remove any one of the four statements and it prints ok.

What we measured

Each configuration was run 2–4 times, with a 4 s budget per run.

shaperesult
2 or 3 concurrent top-level statements (half select 1, half select $1::int)completes
4, 8, 16, 32 concurrenthangs, every run, including on a warm connection
same, with postgres.js max_pipeline 1, 2 or 3hangs (postgres.js still pipelines 2 at max_pipeline: 1)
8 sequentialcompletes
8 concurrent inside one sql.begin (one transaction)completes
2 concurrent top-level + 6 concurrent sql.beginhangs
same statements serialised client-side (one in flight at a time)completes at 4, 16 and 64

The pattern suggests that statements arriving outside a transaction, while an earlier pipelined statement still holds a backend, aren't attached to or queued behind it correctly, so they wait forever.

Expected

Either the pipelined statements run in order, as they do against Postgres directly or inside a transaction, or Supavisor returns an error. It shouldn't hang silently. If pipelining outside a transaction isn't supported in transaction mode, documenting that would help. We found nothing in the Supavisor or Supabase docs.

Workaround

Serialise statements per connection on the client (a one-slot semaphore), or run fan-outs inside one transaction.

We have not yet checked whether hosted Supabase's pooler (port 6543) behaves the same way.

Activity

3
v0idpwn13d ago

Hi, we just merged #1217 which should have a pipelining fix. Would you try running supavisor main?

Otherwise, we will a release candidate soon with this change.

richardwattie12d ago

Thanks! I tested main and it fixes this for us.

I think the PR you meant is #1207 (fix: rework protocol handling to better handle pipelining, merged 2026-10-16 as e7c72bd9). #1217 is the Tidewave MCP change.

Setup: a standalone harness with Postgres 18 and Supavisor, one tenant, one transaction-mode user (pool_size: 3). Client: postgres.js 3.4.9, max: 1, prepare: false, a fresh process per run, 4 s budget, every shape run 3 times. I tested three builds:

  • 2.9.7: published image
  • 2.9.13: published image, the latest release
  • main at e7c72bd9: built from source with the repo Dockerfile

Each cell is how many of the 3 runs completed (the rest hung):

shape2.9.72.9.13main e7c72bd9
original 4-statement repro1/30/33/3
2 concurrent top-level3/33/33/3
3 concurrent top-level3/33/33/3
4 concurrent top-level0/31/33/3
8 concurrent top-level0/30/33/3
16 concurrent top-level0/30/33/3
32 concurrent top-level0/30/33/3 (~45 ms)
8 sequential3/33/33/3
8 concurrent inside one sql.begin3/33/33/3
2 top-level + 6 concurrent sql.begin1/30/33/3

So 2.9.13 still hangs, and main completes every shape every time.

On 2.9.7 the 4-statement shapes sometimes passed. I'd read that as flakiness, not a partial fix. In the original report they hung every time.

Is there an estimate for a release that includes #1207? Separately, do you know when it might roll out to the hosted pooler (port 6543)? Until it does we'll keep serialising statements on the client. I haven't tested hosted yet.

rahulsingh0011d ago

Confirming that this still reproduces on the 2.9.13 release, in case it helps prioritise a release with #1207.

Setup: supabase/supavisor:2.9.13 in docker, a transaction-mode user with pool_size: 3, Postgres 15, postgres.js 3.4.9 with prepare: false.

  • With max: 1, issuing 4 parameterless queries at once (sql.unsafe('select pg_sleep(0.2), n')) leaves 2 that never settle. Issuing 3 completes.
  • Mixing in parameterised queries, the stuck backend shows application_name=Supavisor, state=active, wait_event=ClientRead, with the parameterised query's text. That is the same signature we see on the hosted pooler, where Postgres later logs unexpected EOF on client connection with an open transaction.
  • Connecting directly to Postgres, the same script never hangs.

Is there a rough timeline for a release, and for when the hosted Supabase pooler picks up the fix?

Until then we work around it on the client by patching postgres.js so it never pipelines outside a transaction. The patch is in https://github.com/porsager/postgres/issues/970.