[PHP/PBO] 'prepared statement "pdo_stmt_00000001" does not exist' with transaction pooling mode and max_prepared_statements > 0

[lucaspouzac]

With pgbouncer 1.21.0 configure with transaction pooling mode and max_prepared_statements enabled, php application generate PG error for each request, because it try to deallocate prepared statement with the PDO name.

# LOGS PGBOUNCER
2023-12-05 16:41:02.774 UTC [1] DEBUG C-0x7f4aa87473a0: postgres/dbuser@192.168.144.1:57944 handle_parse_command: creating mapping for statement 'pdo_stmt_00000001' to 'PGBOUNCER_1' (query '    SELECT *
 	    FROM towns t
 	        INNER JOIN departments d ON d.code=t.department
 	        INNER JOIN regions r ON r.code=d.region
 	    WHERE d.name = $1 AND r.name = $2;')
2023-12-05 16:41:02.774 UTC [1] NOISE S-0x7f4aa86d3590: postgres/dbuser@192.168.144.3:5432 prepared statement PGBOUNCER_1 added to server cache, 1 cached items
2023-12-05 16:41:02.775 UTC [1] DEBUG C-0x7f4aa87473a0: postgres/dbuser@192.168.144.1:57944 handle_bind_command: mapped statement 'pdo_stmt_00000001' (query '    SELECT *
 	    FROM towns t
 	        INNER JOIN departments d ON d.code=t.department
 	        INNER JOIN regions r ON r.code=d.region
 	    WHERE d.name = $1 AND r.name = $2;') to 'PGBOUNCER_1'

# LOGS POSTGRES
2023-12-05 16:41:02.778 UTC [73] LOG:  execute PGBOUNCER_1:     SELECT *
 	    FROM towns t
 	        INNER JOIN departments d ON d.code=t.department
 	        INNER JOIN regions r ON r.code=d.region
 	    WHERE d.name = $1 AND r.name = $2;
2023-12-05 16:41:02.778 UTC [73] DETAIL:  parameters: $1 = 'Guyane', $2 = 'Guyane'
2023-12-05 16:41:02.785 UTC [73] LOG:  statement: DEALLOCATE pdo_stmt_00000001
2023-12-05 16:41:02.785 UTC [73] ERROR:  prepared statement "pdo_stmt_00000001" does not exist
2023-12-05 16:41:02.785 UTC [73] STATEMENT:  DEALLOCATE pdo_stmt_00000001

If we configure PBO connection as explained in pgbouncer documentation (PDO::ATTR_EMULATE_PREPARES ==> true, https://www.pgbouncer.org/faq.html#how-to-use-prepared-statements-with-transaction-pooling), are not activated.

Only "work" with PDO::PGSQL_ATTR_DISABLE_PREPARES => false option, but with the previous errors.

In attached files, you could find the full test with docker compose and php app.
pgbouncer.tar.gz

[Melkij]

https://github.com/php/php-src/blob/PHP-8.3.1/ext/pdo_pgsql/pgsql_statement.c#L76
PHP uses DEALLOCATE as an SQL statement, not as a protocol command. This is documented as not supported in pgbouncer:

Note: This tracking and rewriting of prepared statement commands does not work for SQL-level prepared statement commands such as PREPARE, EXECUTE, DEALLOCATE, DEALLOCATE ALL and DISCARD ALL

[ale-dd]

Please correct me if I am wrong... I would assume this error would be sent back as a postgres wire frame. If so, couldn't DEALLOCATE statements be "supported" with one extra round-trip to the server by detecting that specific error and parsing the name of the statement being deallocated? I.e.: don't look at the statement coming from the client, but rather at the "backscatter radiation" coming back from the server...

[Melkij]

I see two huge drawbacks:

• there will be a lot of noise in the database logs
• cannot work in a transaction (an erroneous deallocate will switch the transaction to the aborted state)

[lucaspouzac]

Maybe add a pgbouncer option to allow ignore deallocate query from client if the name of prepared statement to deallocate is bind to pgbouncer prepared statement ?

sequenceDiagram 
  participant Php as php-pgsql
  participant B as PgBouncer
  participant PG as PostgreSQL
  Php->>B: Send "DEALLOCATE pdo_stmt_*"
  B->>B: search if "pdo_stmt_*" is map to PGBOUNCER_* prepared statement ?
  alt "ignore_deallocate=true" pgbouncer.ini option && pdo_stmt_* is map with PG_BOUNCER pre_stmt
    B->>Php: response OK, but ignore send DEALLOCATE to PostgreSQL
  else Others (as actually)
    B->>PG: Send DEALLOCATE
    PG-->>B: result DEALLOCATE
    B-->>Php: result DEALLOCATE
  end

Loading

[Melkij]

if pgbouncer maintainers agree to inspect query texts, then there will be no point in ignoring deallocate statement instead of processing it same way as a protocol command.

Tracking prepare calls leads to a very significant increase in CPU utilization (we started twice more pgbouncer processes after enabling max_prepared_statements to process the same tps). Inspecting all queries will most likely lead to such a large CPU consumption that it will eliminate the whole point of using prepare.

[janpio]

I think this has already been fixed by fully handling DEALLOCATE, just not released yet: #972

[Melkij]

No, 972 PR only processes two exact commands: DEALLOCATE ALL and DISCARD ALL. Nothing more, even not DISCARD ALL (with two whitespaces)

[JelteF]

No, 972 PR only processes two exact commands: DEALLOCATE ALL and DISCARD ALL. Nothing more, even not DISCARD ALL (with two whitespaces)

Correction: It does handle DISCARD ALL (with two whitespaces), because it doesn't look at the query but only at postgres its response.

But indeed it wouldn't fix this, since it doesn't handle DEALLOCATE.

[JelteF]

Parsing SQL queries is something that PgBouncer will almost certainly never do. This is by design for performance and maintability reasons. And parsing error messages in the way suggested has the same problems (and more as @Melkij mentioned). I think there's a few possible "fixes" for this, listed from short to long term options:

1. Update the FAQ that PgBouncer its prepared statement support currently doesn't work with PDO
2. Add an option to PDO that disables sending DEALLOCATE, this shouldn't be a big deal perf/resource wise since pgbouncer already closes statements on postgres based on max_prepared_statements.
3. Change PDO to use PQclosePrepared instead of DEALLOCATE. Note: This will only be released in libpq 17

[B-Galati]

Hi @JelteF @lucaspouzac ,

It looks like this is true with the driver pgsql but not with pdo_pgsql which uses unnamed prepared statements and does not send any DEALLOCATE (at least from the test I've done).

Maybe worth updating the doc about this detail.

By the way, is there any risk/edge-cases to use unnamed prepared statements with PgBouncer?

I enabled PDO::ATTR_EMULATE_PREPARES in doubt but I would be very happy to disable it if not needed 👍