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:

  1. Prisma T1 prepares and executes protocol-level SELECT 1, name bla
  2. pgbouncer receives that statement and stores it, forwarding the prepare as PGBOUNCER_1 on session 1, storing the mapping SELECT 1=>PGBOUNCER_1.
  3. Prisma T1 is done with its transaction - pgbouncer’s session 1 is available again
  4. Prisma T2 starts, cleans up the connection and thereby executes DEALLOCATE ALL.
  5. Pgbouncer doesn’t interpret the message, and forwards it to PostgreSQL. This deallocates the prepared statement PGBOUNCER_1 without pgbouncer’s knowledge.
  6. Prisma T2 prepares and executes protocol-level SELECT 1, name bla (it doesn’t think it has any prepared statements)
  7. pgbouncer recieves that statement, finds it as SELECT 1 => PGBOUNCER_1, and forwards EXECUTE PGBOUNCER_1 as it thinks the statement is still loaded.
  8. 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

Most upvoted comments

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