Transaction Pooling

Why this stage exists

Session pooling saves little when clients stay connected. A web worker keeps its connection for hours and uses it a few milliseconds per request. The real unit of work is the transaction: between two of them, the connection does nothing. Transaction mode lends a server connection for one transaction and takes it back the instant that transaction ends. Ten clients that each spend one percent of their time in a transaction can then share a single connection.

The pooler doesn't parse SQL to find where a transaction ends. The server already says so, after every answer, in ReadyForQuery's status byte (section 96):

An autocommit statement is a transaction of its own: its answer ends with I, so it holds a connection only while it runs.

A transaction never moves

A connection is lent for a whole transaction, and a transaction never moves to another connection halfway. Its locks, its undo records and its read view all live in the server session that began it. So T keeps the connection with its client. So does E: a failed transaction still has to be rolled back on the session that holds it.

What it costs

The convenience has a price, and both of this stage's last two cases pin it down. A server connection carries session state: the statements prepared on it and the session's isolation level. In session mode that state belonged to one client and was reset when it left. In transaction mode, consecutive transactions of one client may run on different connections, and one connection serves many clients in turn:

• a client prepares p on server 1, and its next transaction runs on server 2: p is unknown there (26000);
• a client sets READ COMMITTED on server 1 and gives it back, and the next client to get server 1 is now at READ COMMITTED.

PgBouncer documents the same list of features that break in transaction mode. The next stage fixes the first, for the prepared statements drivers make with Parse.

Output contract for this stage

These are the exact lines and messages the tests check. The full definition is on the output contract page, section 102.

What you should see when done

$droid-pool --port 6432 --server-port 5433 --pool-size 1 --mode transaction
[POOL] listening on 127.0.0.1:6432
[POOL] client 1 connected
[POOL] client 2 connected
[POOL] server 1 connected
[POOL] client 1 uses server 1
[POOL] client 2 waits
[POOL] client 1 released server 1
[POOL] client 2 uses server 1
[POOL] client 2 released server 1

Common bugs in this stage

• Releasing at COMMIT in the SQL. A COMMIT can fail, and a transaction can end by a ROLLBACK or by an error in an implicit transaction. Release on the status byte, never on the SQL.
• Releasing on E. The failed transaction still has to be rolled back where it lives.
• Resetting at every release. That would wipe what the client meant to keep, and it costs a round trip per transaction. Transaction mode resets nothing; only a client that leaves gets the reset.
• Releasing in the middle of a batch, or of a pipeline. The extended protocol sends its ReadyForQuery only at Sync, but a client may pipeline several Queries or Syncs: release only when every one you forwarded has had its ReadyForQuery.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: the forwarding loop already sees each whole message; check type == 'Z' and the byte after the length.
• C++: the same, on your message struct.
• Rust: a match on the message type in the server-to-client direction, with the release as one arm.
• Zig: a switch on the type byte.