TML-3108: serialize queries per pinned pg client in the Postgres runtime driver - #29839

Consider a Cloudflare Worker handler that fans out two independent reads on one serverless handle:

const db = postgres(context).connect({ url });
const [users, posts] = await Promise.all([
  db.sql.execute(userPlan),
  db.sql.execute(postPlan),
]);

Both reads share one pg.Client. Today this works because pg@8 silently queues the second query behind the first — a deprecated internal that pg@9 removes: a query sent while another is in flight will throw (maintainer-confirmed in node-postgres discussion #3598). pg only warns when a third call overlaps, so the common two-wide Promise.all shape gives no warning at all today and becomes a hard error on the pg@9 upgrade.

This PR makes the driver own that flow control instead: a per-physical-client FIFO mutex (a WeakMap keyed by the underlying client, reusing the existing AsyncMutex) serializes statements on every pinned-client path — direct { kind: 'pgClient' } drivers, pinned connections from acquireConnection(), and transaction handles, which covers the serverless facade and the Supabase runtime that inherits it. Pool-level calls are untouched: each checks out its own PoolClient, so keying by physical client means they never contend.

The lock is held for exactly as long as the client is protocol-busy, and no longer:

• Buffered execution (the production path — both shipped runtimes disable cursors) locks only the awaited client.query(). Row iteration is lock-free, so a nested query on the same client inside for await still works. An earlier draft held the lock across the yields; review caught that it hands lock lifetime to the consumer and deadlocks that nested shape, confirmed by execution before narrowing.
• Cursor streams hold the lock for the whole BEGIN…COMMIT span, because the cursor genuinely occupies the connection for its lifetime. This subsumes the previous streamSpanLocks (deleted): the same mutex now guarantees concurrent streams cannot interleave their transaction wraps.
• Transaction boundaries (BEGIN/COMMIT/ROLLBACK) each take the lock around their own statement — previously they bypassed all locking and could interleave into a concurrent statement.

Semantics are unchanged for working code: FIFO order at statement granularity is exactly what pg@8's queue provided, including its one deadlock shape (awaiting a query inside iteration of a cursor stream on the same client), which remains and is now documented on PostgresBinding. Statement-granularity locking deliberately does not fence other callers out of an open transaction on a shared client — that also matches pg@8, and no test claims otherwise.

Tests were written red-first: max-in-flight spies read 3/4/2/3/2 overlapping queries across the direct driver, mixed methods, pinned connection, transaction, and lease-overlap cases before the fix (all now 1), and the integration harness (real pg.Client over a PGlite socket server) captured the pg deprecation warning verbatim before and none after. Guard tests pin the other direction: pool-level queries must stay concurrent, and four timeout-guarded regressions pin the nested-query/abandoned-generator/commit-during-stream shapes that the narrowed lock must not break. The warning assertion lives in a single dedicated test because util.deprecate fires once per process.

Changes are confined to @prisma-next/driver-postgres (runtime driver + two test files). The CLI's control-plane driver needed no change — investigation on the linked ticket verified it already runs strictly sequentially (max 1 query in flight across a full migrate journey).

Refs: TML-3108

🤖 Generated with Claude Code

Summary by CodeRabbit

• Bug Fixes
  • Reworked PostgreSQL driver concurrency to serialize all operations that share the same underlying physical client, preserving strict FIFO execution and preventing interleaving.
  • Ensured cursor streaming and transaction control statements (BEGIN/COMMIT/ROLLBACK) remain consistent and don’t break during concurrent activity.
  • Kept parallelism for separate pooled connections.
• Tests
  • Added a pinned-client FIFO/serialization test suite covering queries, prepared/explain, transactions, streaming (including abandoned streams), and proper unlock behavior.
  • Updated integration teardown and tightened warning assertions to reliably target the expected pg overlap warning.