droid
What Transaction Pooling Breaks, and the Fix
Pooling · Stage 11 of 11

What Transaction Pooling Breaks, and the Fix

Make prepared statements survive a change of connection: remember each client's Parse, give it a name of its own on the server, and replay it where it is missing

With --track-prepared in transaction mode, keep every named Parse a client sends; rename the statement on the server to p<c>_<name> in Parse, Bind, Describe and Close; before a Bind or Describe on a connection that lacks the statement, send it the kept Parse and swallow the ParseComplete; answer an unknown or closed name with 26000 yourself; and know what still breaks: SQL PREPARE and session settings, which the pooler cannot see without reading SQL.

Run the tests on your machine

This part needs a network, which the platform's test sandbox does not have, so its tests run on your computer. Download the test kit (it needs only Python 3) and your project, build your program, and run:

python3 run.py --stage pool-breakage --bin ./c-db
Download the test kit

Sign in to download the code you wrote here.

Concepts in this stage
  • The client's view and the server's view of a statement name can differ, and the pooler translates
  • Replay: a statement is cheap to prepare again, so prepare it again wherever it is needed
  • A pooler that hides the replay: the client never sees a ParseComplete it did not ask for
  • Names must not collide: two clients' p on one connection are two statements
  • The limit of the fix: what the pooler cannot parse, it cannot track
Where this stage sits in the engine
Talking to the user
REPLSessionsPrinter
Server
Wire protocol
Understanding SQL
LexerParserValidator
Answering a query
PlannerExecutor
Keeping changes safe
TransactionsWrite-ahead logUndo logLock managerRead views
Storing rows
Row formatSlotted pageB+Tree
Reaching the disk
PagerDatabase file
this stagebuilt alreadystill to comeA statement enters at the top; rows come back up.

Why this stage exists

The last stage ended with a failing case, pinned on purpose. In transaction mode, a client parses p on server 1. Its next transaction runs on server 2, where p was never prepared, and the Bind fails with 26000. Every driver that prepares statements (and most do, for every query with parameters) breaks this way behind a transaction pooler. For years the advice was "turn off prepared statements, or use session mode". PgBouncer fixed it in 2023 by tracking them, and this stage does the same.

Drawing the diagram…

Two names for one statement

A pool of connections serves many clients. If client 1 and client 2 both call their statement p, and both run on server 1, the second Parse fails: 42P05, it already exists. So the pooler gives every kept statement a name of its own on the server: p1_p for client 1's p, p2_p for client 2's. The client keeps using p. The pooler translates in every message that names a statement, Parse, Bind, Describe and Close, and writes the message's length again, since the name changed size.

Replaying where it is missing

The pooler remembers, for each kept statement, which server connections have it. Just before it forwards a Bind or Describe that needs the statement on a connection without it, it sends that connection the kept Parse first. Parsing again is cheap, and the client must not notice: the pooler reads that ParseComplete itself and passes on only what the client asked for.

What still breaks

The fix covers prepared statements made by Parse, because the pooler reads protocol messages anyway. It does not cover:

  • SQL PREPARE. PREPARE p AS ... arrives inside a Query, as text. To track it the pooler would have to parse SQL, and then follow every statement that might create, change or drop session state. PgBouncer does not, and neither does droid-pool. The last case pins this down.
  • Session settings, such as the isolation level set with SET TRANSACTION, for the same reason. Clients behind a transaction pooler set what they need inside each transaction instead.

Pooling always trades something away. The skill is knowing exactly what.

The algorithm in words

1

Keeping and renaming

Only named statements made by Parse are kept: those are what drivers use.

  1. 1.When a client sends a Parse with a name, keep the whole message under the client and the name, and compute the server's name: p, the client's number, an underscore, the client's name.
  2. 2.Forward the Parse with the server's name in place of the client's, and remember that this server connection has it. Log client <c> prepares <name> on server <s>.
  3. 3.In every Bind, Describe S and Close S the client sends, put the server's name in place of the client's. The message gets longer or shorter: write its length again.
2

Replaying

Just before the statement is used on a connection that does not have it.

  1. 1.Before forwarding a Bind or a Describe S, check that the connection has the statement for this client. If it does not, send it the kept Parse with the server's name first, log the prepares line, and read the ParseComplete yourself: the client must not see it.
  2. 2.If the client never parsed that name, or has closed it, answer an ErrorResponse 26000 yourself and skip the client's messages to Sync, as a server would.
  3. 3.Close S of a kept statement: forget it and answer CloseComplete yourself. The copies on server connections stay, unused.
  4. 4.A Parse of a name the client already has is 01910, 42P05, from you; it is not forwarded. A statement is kept from the moment you forward its Parse, so a Bind in the same batch finds it; drop it if the server answers that Parse with an error.
  5. 5.Your own answers keep their place: send each one after every answer the server still owes for the messages before it. If nothing of a batch reached a server, answer its Sync with ReadyForQuery yourself.

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 103.

Client sends, with --track-prepareddroid-pool
Parse pforwards it as p<c>_p; logs [POOL] client <c> prepares p on server <s>
Bind or Describe S of p, on a connection without itsends the kept Parse first, swallows its ParseComplete, logs the prepares line, then forwards as p<c>_p
Bind or Describe S of a name never parsed, or closedErrorResponse 01911 (26000) itself; skips to Sync
Close S of pCloseComplete itself; forwards nothing
Parse of a name already keptErrorResponse 01910 (42P05) itself; not forwarded
a batch nothing of which reached a serverthe Sync's ReadyForQuery from the pool itself
SQL PREPARE in a Queryforwarded unchanged, not tracked

What you should see when done

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

Common bugs in this stage

  • Forwarding the replayed ParseComplete. The client gets an answer it never asked for, and every later answer it reads is one message off.
  • Keeping the client's name on the server. Two clients' p then collide on one connection.
  • Rewriting the name and not the length. The server reads the next message from the wrong place.
  • Replaying for every Bind. Track which connections already have the statement; replay only where it is missing.
  • Forgetting the client's statements when it leaves. Their server copies may stay; the pooler's record of them must go.

Language notes

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

  • C: keep each tracked Parse as a copy of the message bytes, and a small bitmap of server connections that have it. Rebuilding a message with a new name is a copy into a fresh buffer with the length written last.
  • C++: a std::map<std::pair<int, std::string>, Tracked> from (client, name), each with a std::vector<bool> of connections.
  • Rust: a HashMap<(u32, String), Tracked>; a HashSet<u32> of server numbers per statement.
  • Zig: a std.AutoHashMap keyed by client number, each holding a std.StringHashMap(Tracked).

Checklist

One line per thing the tests check. The tags name the test cases.

  • With --track-prepared the pooler prepares the statement again where it is needed, and the Bind works
    prepared-statement-tracked
  • Two clients' statements of the same name live side by side on one connection
    names-do-not-collide
  • A statement the client closed is gone, and only that client's
    close-tracked
  • A statement made with SQL PREPARE is still lost: the pooler does not read SQL
    sql-prepare-not-tracked
PreviousTransaction PoolingNextExtended Type System