script-tx pins a pooled connection for the whole script; deadlocks at default_pool_size/2

[Hazzng]

Summary

A script scope holds one Postgres transaction open for the entire duration of a request or bash script, and each in-flight write needs a second connection concurrently. Under transaction-mode pooling — which is how we connect in production, always — that pins pooled server connections for the whole script and deadlocks once concurrency reaches roughly default_pool_size / 2.

Mechanism

• SqlFs.#openScriptTx (src/sql-fs/sql-fs.ts:279-319) opens a transaction and holds it awaiting an end promise. In transaction mode PgBouncer pins a server connection for a transaction's whole lifetime, so the backend sits idle in transaction for as long as the script runs — including any sleep, Python/JS step, network fetch, or agent turn.
• While that transaction is open, every writeFile first calls commitBlob, which deliberately uses a separate pool connection (src/sql-fs/dialects/postgres.ts:615-629, "Self-committing single-statement INSERT on its OWN pool connection").
• So each in-flight write script needs 2 server connections simultaneously, and it will not release the first until it gets the second.

That is a textbook pool deadlock: it does not drain and it does not degrade, it wedges.

Measured (real PgBouncer, transaction mode, 12 concurrent 8-second write execs)

default_pool_size	Result
5	0/12 succeeded, all hung ~128 s then timed out; cl_waiting=13, maxwait 0→120 s
12 (= concurrency)	permanent deadlock — 12 backends idle in transaction, transaction age 69 s for an 8 s script; whole replica unusable, even GET /v1/sandboxes hung
40	12/12 OK in 8.1 s, cl_waiting=0

pool_size == concurrency is not enough. The floor is 2x; 4x is the safe margin.

Impact

• A replica can wedge entirely, not just slow down. Health endpoints hang with it.
• The exposure window is as long as the user's script, which we do not control.
• It interacts with Migrations use a session-scoped advisory lock that breaks under transaction pooling #164 and with connection-loss handling: a pooler or Postgres killing an idle in transaction backend is what triggers the torn-commit class of bug (fixed to fail closed in 52cc836, but the failure still happens).

Options

1. Operational, immediate: set pgbouncer.default_pool_size >= 4 x peak concurrent write execs per replica, and query_wait_timeout low (15 s, not the 120 s default) so saturation fails fast into a retry instead of hanging a script for two minutes.
2. Structural, the real cure: stop holding a transaction open across arbitrary user code. Buffer a script's mutations and flush them in one short transaction at endScriptScope, or checkpoint at safe points. This is what makes the bug window milliseconds instead of minutes.
3. Narrower: let commitBlob reuse the script-tx connection when a scope is open, removing the 2-connections-per-write requirement and halving the deadlock pressure. Needs care — the separate connection exists so blob writes self-commit and dedup outside the script's rollback scope.

Provenance

Found during pre-merge load testing of #162. Not introduced by that PR. Related prior art: thoughts/shared/plans/2026-05-02_bulk-fs-ops-script-tx.md already recorded that the Neon transaction pooler "terminates long-lived transactions and is incompatible with script-tx".