sql-pg: send several statements in one extended-query Sync (transaction-local context without extra round trips)

What is the problem this feature would solve?

We run Postgres row-level security behind PgBouncer in transaction mode. Session state cannot be trusted there, so every operation sets its context transaction-locally with set_config(..., true) and then runs its query.

With @effect/sql-pg 4.0.6, every statement on a pinned connection or in a transaction is its own extended-query cycle: one Sync, then a wait for ReadyForQuery. A single scoped query therefore costs four round trips:

import { SqlClient } from "effect/sql/SqlClient"
import { Effect } from "effect"

const scoped = (org: string) =>
  Effect.gen(function* () {
    const sql = yield* SqlClient
    return yield* sql.withTransaction(
      Effect.gen(function* () {
        yield* sql`SELECT set_config('app.organization_id', ${org}, true)`
        return yield* sql`SELECT * FROM item`
      })
    )
  })

On the wire: BEGIN + Sync, set_config + Sync, SELECT + Sync, COMMIT + Sync. The query itself is one of four round trips.

What we measured (PostgreSQL 18, PgBouncer 1.25.1 transaction mode, 0.1 ms of added latency per direction per link, pgbench extended protocol, 10 server connections):

Putting the context statement and the query in one Sync recovers about 96% of the overhead. With one Sync, PostgreSQL runs the whole cycle as one implicit transaction: set_config(..., true) dies with it, and an error in the context statement skips the query. PgBouncer also gets the server connection back after one round trip instead of four, and that is where the 4.7× throughput comes from. We checked 400 alternations between organizations on shared PgBouncer backends and saw no context leak.

Explicit transactions benefit too. BEGIN plus the context statement in one Sync saves one round trip per transaction. The core SqlClient sends BEGIN on its own through executeUnprepared, so we cannot merge it from outside.

The extended-query protocol allows any number of Parse/Bind/Execute groups before one Sync. PgConnection has no API that uses this today, as far as we can tell: query, queryValues, executeUnprepared and the multiplexed pipeline each close every statement with its own Sync.

What is the feature you are proposing to solve the problem?

An API on PgConnection that sends one or more "prelude" statements in the same cycle as a query:

interface PreludeStatement {
  readonly sql: string
  readonly params?: ReadonlyArray<unknown>
}

readonly queryWithPrelude: (
  prelude: ReadonlyArray<PreludeStatement>,
  sql: string,
  params?: ReadonlyArray<unknown>,
  prepare?: boolean
) => Effect.Effect<Result, SqlError>

• Each prelude statement is unnamed Parse / Bind / Execute, with no Describe and no Sync. The query's own frame follows, and one Sync closes the cycle: one write, one round trip.
• The query's rows come back. Prelude replies are consumed and their rows dropped.
• A prelude ErrorResponse fails the effect with that error. The backend skips the rest of the cycle, so the query never runs.
• A multiplexed connection pins for the cycle, so nothing else gets between the prelude and the query.
• A retry after a stale prepared statement sends the prelude again, because the failed cycle ran as one unit.

Higher up, SqlClient could expose a hook so that withTransaction sends its BEGIN together with caller-supplied statements in one Sync. This is related to #9573 / #9576, which make the BEGIN statement configurable.

We run this today as a pnpm patch against @effect/sql-pg 4.0.6. The patch adds about 160 lines to dist/PgConnection.js: an encodePrelude helper, a small PreludeMachine that consumes prelude replies and hands everything else to the existing QueryMachine, and an optional prelude argument on runQuery. We can contribute it if the shape fits. On top of it, a standalone scoped query is queryWithPrelude([context], query) with no BEGIN/COMMIT. An explicit transaction sends BEGIN ISOLATION LEVEL ... as the prelude and the context statement as the query. If that cycle fails, our wrapper issues a ROLLBACK itself, because the core only rolls back after a successful begin.

What alternatives have you considered?

• Session-level SET / connection-start GUCs. Unsafe behind PgBouncer transaction mode: the next client on the same backend inherits the settings.
• A multi-statement simple query (BEGIN; SELECT set_config('...', 'literal', true)). One round trip, but no bind parameters: values must be escaped by hand, and the query that follows still needs its own cycle.
• The multiplexed pipeline (multiplex: true). It pipelines independent statements, but every statement still gets its own Sync, so each one is its own implicit transaction. Transaction-local settings do not survive to the next statement. Transactions pin the connection and run one cycle at a time anyway.
• Folding set_config into each query (CTE or function call). It rewrites every query, the order of evaluation against the RLS policy is not guaranteed, and it cannot carry statement_timeout or lock_timeout for that same statement.
• Accepting the cost. At four round trips per scoped query, it is the largest per-query cost in our service: about 1.7 ms of 2.3 ms in the measurement above.