transaction pooling mode: extended protocol inside explicit transaction causes bad connection (10s timeout) with all prepared_statements modes

[aleph-whip]

Environment

• pgdog: 0.1.41
• PostgreSQL: 17 (Stolon-managed, async replication)
• Client driver: lib/pq (Go), same behaviour expected from pgx and JDBC
• OS: Debian Trixie x86_64
• pooler_mode: transaction
• prepared_statements: reproduced with extended (default), extended_anonymous, and full

Summary

Any query sent via the Postgres extended query protocol (Parse → Bind → Execute) inside an explicit BEGIN / COMMIT block fails with driver: bad connection after a ~10 s hang when pgdog is in transaction pooling mode. Simple-protocol queries (psql interactive, no parameters) against the same endpoint work correctly.

Minimal reproducer

A Go probe exercising five escalating protocol patterns against pgdog at :6432 via lib/pq:

[1/5] ping (simple protocol, no params, no tx)            … ok  (160ms)
[2/5] SELECT 1 (simple protocol, no params, no tx)        … ok  (22ms)
[3/5] SELECT $1::int (extended protocol, params, no tx)   … ok  (40ms)
[4/5] BEGIN; SELECT $1::int; ROLLBACK (extended in tx)    … FAIL (10.04s)
      driver: bad connection
[5/5] (not reached)

Step 4 is deterministic. The smallest reproducer is:

BEGIN;
SELECT $1::int;   -- bound parameter via extended protocol
ROLLBACK;

lib/pq sends this as:

1. Query("BEGIN") — simple protocol
2. Parse("", "SELECT $1::int") → Bind(params) → Execute → Sync — extended protocol
3. Query("ROLLBACK") — simple protocol

The same sequence issued directly against the Stolon primary (bypassing pgdog) succeeds immediately, confirming pgdog is the variable.

Expected behaviour

In transaction pooling mode, once BEGIN is received and the backend returns ReadyForQuery with status T (in transaction), the backend should remain pinned to that client until COMMIT or ROLLBACK. All subsequent messages — including extended-protocol Parse/Bind/Execute — should be forwarded to the same pinned backend.

Actual behaviour

The connection hangs for ~10 seconds then returns driver: bad connection. Our hypothesis is that pgdog releases the backend back to the pool after the BEGIN / ReadyForQuery(T) exchange (or between Parse and Bind/Execute), so the subsequent extended-protocol messages are routed to a different backend that has no knowledge of the open transaction, causing a protocol-level error or timeout.

What we tried

All three non-default prepared_statements modes were tested — extended_anonymous, full (including 0.1.41's fix for full rewrites of extended protocol), and the default extended — none resolved the issue. This suggests the root cause is in transaction-state tracking / backend pinning rather than in prepared-statement rewriting.

Workaround: switching to pooler_mode = "session" eliminates the error, but at the cost of per-transaction R/W split and correct failover routing.

Impact

Any Go (or other) application using parameterised queries inside explicit transactions — which is the normal pattern for database/sql with BeginTx — is completely unable to use pgdog in transaction mode.

[levkk]

We do have that driver tested in CI so I'll be curious to see what you find if you dig more into this.

pgdog/integration/go/go_pq/go_pq_test.go

Line 1 in c9d594c

package main

[aleph-whip]

Thanks for the quick response and for pointing to the test file — very helpful!

I should mention that I'm looking at this from an infrastructure/ops perspective, so my ability to dig into the Go side is limited right now. I'm looping in our developers as soon as possible and we'll try to run a proper comparison.

That said, comparing TestPqCrud with our setup, I noticed a potentially significant difference: the CI tests run against pgdog_sharded, while our setup is a non-sharded configuration with two backends (primary + replica) and read_write_split = "exclude_primary" enabled. Each shard in pgdog's CI effectively has a single backend, so R/W split routing wouldn't be in play.

Our hypothesis is that the failure might be specific to the combination of transaction mode + extended protocol + R/W split across multiple backends — rather than extended protocol handling in isolation. When BEGIN arrives, pgdog pins a backend; our suspicion is that the R/W split routing logic may interfere with that pin before the subsequent Parse/Bind/Execute messages arrive.

We also noticed that TestAdvisoryLockWithTransaction explicitly sets prepared_statements = 'extended_anonymous' before running its transaction test, while TestPqCrud does not — not sure if that's intentional or whether TestPqCrud would also need it in a non-sharded R/W-split configuration.

We'll have our developers run TestPqCrud against our pgdog instance to get you a cleaner data point. Happy to share whatever we find!