Skip to content

Fixing 'prepared statement does not exist' under PgBouncer transaction pooling

Covers the PgBouncer-side mechanism and its two fixes: native prepared-statement tracking (1.21+) or avoiding named server-side prepared statements. Does not cover postgres.js's mirror-image 'already exists' error, which shares the same underlying incompatibility but needs a client-side fix, not a PgBouncer-side one — see the postgres.js playbook, which is not interchangeable with this one despite the similar wording.

PgBouncerPostgreSQL
Unverifiedno reproductions yetWhy this confidence?
Revision 1published by DevYou curationrevision history
Run the diagnosisEvidence and compatibility

Symptoms

The diagnostic path

8 steps. Every step is written out below in full — the interactive version simply follows the branches for you.

  1. Step 1 · StartA named prepared statement 'does not exist'

    This surfaces through PgBouncer, but the underlying mechanism is about which Postgres backend connection actually has the statement prepared.

    What happens next

    • passed → step 2, Confirm the pool is in transaction (or statement) pooling mode

  2. Step 2 · TestConfirm the pool is in transaction (or statement) pooling mode

    Connect to PgBouncer's admin console (psql -h <host> -p <port> pgbouncer) and check pool_mode for the affected database.

    Read-onlysql
    SHOW CONFIG;

    Expected result

     key        | value       | changeable 
------------+-------------+------------
 pool_mode  | transaction | yes
(1 row)

    What happens next

    • passed → step 3, Transaction pooling reassigns server connections between statements
    • failed → step 4, pool_mode is session, not transaction/statement
    • unknown → step 4, pool_mode is session, not transaction/statement

  3. Step 3 · Root causeTransaction pooling reassigns server connections between statements

    In transaction (or statement) pooling mode, PgBouncer returns the underlying server connection to the pool as soon as a transaction (or statement) ends, and a client's next transaction may be handed a completely different server connection. A named prepared statement exists only on the specific backend it was PREPARE'd on — so a later statement that references it by name, on a different backend that never ran that PREPARE, fails with 'does not exist'.

    What happens next

    • passed → step 5, Check PgBouncer's version, then use native prepared-statement tracking or turn it off client-side

  4. Step 4 · Endpool_mode is session, not transaction/statement

    In session pooling, a client keeps the same server connection for its whole session, so this specific failure mode shouldn't occur. If it's still happening under session pooling, something else is going on — out of scope here.

  5. Step 5 · FixCheck PgBouncer's version, then use native prepared-statement tracking or turn it off client-side

    PgBouncer 1.21 and later can track and transparently re-prepare named statements per server connection when maxpreparedstatements is set above 0 — this is the preferred fix if your version supports it. Edit pgbouncer.ini and reload with RELOAD; via the admin console once changed.

    Changes stateini

    Changes system or service state. Review before running.

    Once reloaded (RELOAD; via the admin console), enables PgBouncer to track and transparently re-prepare named statements per server connection, up to 200 concurrently per connection. Existing connections and in-flight transactions are not dropped by the reload.

    max_prepared_statements = 200

    What happens next

    • passed → step 6, Confirm the errors stop and the setting is active

  6. Step 6 · Verify the fixConfirm the errors stop and the setting is active

    Re-check the config after reloading, and re-run the workload that was triggering the error.

    Read-onlysql
    SHOW CONFIG;

    Expected result

     key                        | value | changeable 
-----------------------------+-------+------------
 max_prepared_statements    | 200   | yes
(1 row)

    What happens next

    • passed → step 7, Resolved
    • failed → step 8, If you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection
    • unknown → step 8, If you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection

  7. Step 7 · EndResolved

    PgBouncer is now re-preparing statements transparently per backend connection.

  8. Step 8 · FixIf you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection

    Disable automatic/named prepared statements on the client for connections that go through this pool — for example, in postgres.js this is the prepare: false client option (see that playbook for the client-side version of this same incompatibility); in a driver that names statements explicitly, switch to unnamed/simple-protocol queries for this database. Alternatively, set pool_mode to session for just this database, at the cost of losing transaction pooling's connection-reuse efficiency.

Sources

Why this confidence?

What would strengthen it: 6 more independent reproductions. Reproductions from 3 more distinct environments.