prisma: Prisma PgBouncer mode doesn't play nice with PGBouncer v1.21.0
PGBouncer 1.21 introduced some support for prepared statements: https://www.pgbouncer.org/config.html#max_prepared_statements
However, this support does not include support for parsing and interpreting DEALLOCATE ALL
/RELEASE ALL
, which is used here to clean up any prepared statements. This means pgbouncer gets confused about the state of the session with its prepared statements and tries to execute them when a new client asks to execute the equivalent statement; resulting in PostgreSQL erroring out because the statements were released:
Order of exection:
- Prisma T1 prepares and executes protocol-level
SELECT 1
, namebla
- pgbouncer receives that statement and stores it, forwarding the prepare as
PGBOUNCER_1
on session 1, storing the mappingSELECT 1
=>PGBOUNCER_1
. - Prisma T1 is done with its transaction - pgbouncer’s session 1 is available again
- Prisma T2 starts, cleans up the connection and thereby executes
DEALLOCATE ALL
. - Pgbouncer doesn’t interpret the message, and forwards it to PostgreSQL. This deallocates the prepared statement
PGBOUNCER_1
without pgbouncer’s knowledge. - Prisma T2 prepares and executes protocol-level
SELECT 1
, namebla
(it doesn’t think it has any prepared statements) - pgbouncer recieves that statement, finds it as
SELECT 1
=>PGBOUNCER_1
, and forwardsEXECUTE PGBOUNCER_1
as it thinks the statement is still loaded. - PostgreSQL receives the
EXECUTE PGBOUNCER_1
command, but then returns an error as it has already deallocated that prepared statement at 5. This error is forwarded to prisma.
Note: this assumes pgbouncer is configured with a non-0 value for max_prepared_statements
About this issue
- Original URL
- State: open
- Created 8 months ago
- Reactions: 2
- Comments: 16 (9 by maintainers)
Commits related to this issue
- Handle DEALLOCATE ALL and DISCARD ALL for prepared statements (#972) This implements a hacky/clever approach of handling the `DEALLOCATE ALL` and `DISCARD ALL` SQL statements. Instead of parsing th... — committed to pgbouncer/pgbouncer by knizhnik 8 months ago
- Handle DEALLOCATE ALL and DISCARD ALL for prepared statements (#972) This implements a hacky/clever approach of handling the `DEALLOCATE ALL` and `DISCARD ALL` SQL statements. Instead of parsing th... — committed to neondatabase/pgbouncer by knizhnik 8 months ago
The PR was merged: https://github.com/pgbouncer/pgbouncer/pull/972 Next release of PgBouncer will probably prevent this problem.
We are currently working on testing this and making sure there are not other surprises hiding.
There is now an issue for PgBouncer: https://github.com/pgbouncer/pgbouncer/issues/974 And a PR that already suggests a way how to fix this: https://github.com/pgbouncer/pgbouncer/pull/972
https://community.neon.tech/t/i-encounter-this-error-for-some-reason-related-to-pgbouncer/1295/11
You might see different stack traces here