Issues with PgBouncer on DigitalOcean - `CODE: 26000 ERROR: prepared statement \"PGBOUNCER_34\" does not exist`

[guyromb]

Bug description

I use prisma 5.5.2, with pgbouncer=true in the connection string. I get the following error: CODE: 26000 ERROR: prepared statement \"PGBOUNCER_34\" does not exist max_prepared_statements is as default (0)

Strangely enough it used to work before with pgbouncer=true without any issues. The issue started a few hours ago. DigitalOcean is also investigating this meanwhile

How to reproduce

Using digitalocean postgres pool with prisma does not work.

Expected behavior

No response

Prisma information

N/A

Environment & setup

• OS: macOS or linux both not working
• Database: PostgreSQL
• Node.js version: 18 LTS

Prisma Version

5.5.2

[JelteF]

It very much sounds like max_prepared_statements is not actually set to 0. The PGBOUNCER_36 name for the prepared statement is only used by PgBouncer when max_prepared_statements is set to a non-zero value.

[janpio]

Indeed, from the description it also seems you are talking to PgBouncer 1.21.0 with max_prepared_statements configured to a value >0.

The prepared statement not existing indicates that possibly someone is executing DEALLOCATE ALL on the pooled connection. Are there also clients connected that are still using pgbouncer=true? You will have to fully remove those.

Then you potentially also need to restart/reset PgBouncer so it resets cache of the prepared statements so it gets out of this broken state where it thinks a prepared exists on a connection although it does not any more.

[ikoichi]

I am affected by the same issue.

I resized the database on DigitcalOcean and suddenly the error CODE: 26000 ERROR: prepared statement \"PGBOUNCER_34\" does not exist started to happen. I was using Prisma 4, I upgraded to Prisma 5.5.2 and removed pgbounder=true from the connection string.

The error now happens randomly, it might work fine for ~8 hours, and then start to happened. The only immediate fix Iàve found it to terminate the connection in the Digital Ocean configuration panel, but itàs not sustainable.

I tried to call prisma.$disconnect() when the error occurs, but it doesn't seem to work.

Might it be the case that during the database resize, something changed in the configuration of the Postgres database?

Unfortunately, I don't see any way to update max_prepared_statements on my own.

I need this to be fixed ASAP, because it's affecting the customers. Any help from you that know how Prisma works, is very much appreciated, thanks!

[JelteF]

Sounds like an issue with DigitialOcean, changing max_prepared_statements to a non-zero value by default. You should contact their support. Or possibly you can remove pgbouncer=true from the connection string and have everything work, but if they then change max_prepared_statements back to 0 you will get errors again.

[ikoichi]

Sounds like an issue with DigitialOcean, changing max_prepared_statements to a non-zero value by default. You should contact their support. Or possibly you can remove pgbouncer=true from the connection string and have everything work, but if they then change max_prepared_statements back to 0 you will get errors again.

Thanks Jelte, I already removed pgbouncer=true but the errors persist (randomly), if I add it again, the errors are always present. I opened a ticket to DO.

I suppose max_prepared_statements=0 without pgbouncer=true is the optimal setup, isn't it?

[JelteF]

I already removed pgbouncer=true but the errors persist (randomly)

Did you remove it from all clients/applications talking to PgBouncer, because if one still has pgbouncer=true then that poisons connections for all other clients. After you removed it everywhere you should restart pgbouncer or run RECONNECT in its admin console. To make sure that all existing server connections are removed.

I suppose max_prepared_statements=0 without pgbouncer=true is the optimal setup, isn't it?

max_prepared_statements >= 0 without pgbouncer=true is the fastest setup as reported by some prisma users. But it's also new, so a bit experimental. max_prepared_statements=0 with pgbouncer=true is slower, but known to be working well as this is what you've been using before.

[janpio]

I suppose max_prepared_statements=0 without pgbouncer=true is the optimal setup, isn't it?

@JelteF already said it, but let me restate the two configuration that currently work with Prisma and PgBouncer 1.21.0:

• max_prepared_statements >= 0 + NO pgbouncer=true
• max_prepared_statements=0 + pgbouncer=true

@ikoichi

I already removed pgbouncer=true but the errors persist (randomly)

This is interesting and concerning. Are you sure that no Client still connects with pgbouncer=true? This should only happen if some Client sends an DEALLOCATE ALL.

---

@JelteF In your experience, what is the correct way to recover PgBouncer when one gets a prepared statement \"PGBOUNCER_34\" does not exist error message, meaning that PgBouncer thinks there is a prepared statement but it does not exist any more on the connection?

[JelteF]

@JelteF In your experience, what is the correct way to recover PgBouncer when one gets a prepared statement \"PGBOUNCER_34\" does not exist error message, meaning that PgBouncer thinks there is a prepared statement but it does not exist any more on the connection?

The server connection is fried at this point so it needs to be forcibly closed. There's two ways of doing that:

1. restarting pgbouncer
2. Running RECONNECT in pgbouncer its admin console

[ikoichi]

@janpio

Are you sure that no Client still connects with pgbouncer=true?

Yes, I am pretty sure. I only have 3 clients.

@JelteF I am not aware of any way to interact with pgBouncer directly on a managed database on DO. But I opened a ticket asking for help.

Thanks for your help!

[ikoichi]

In case it's useful to others. I opened a ticket to DO and they disabled prepared statements for pgBouncer. After they made the change, I had to add again &pgbouncer=true to the connection string, otherwise I was getting the usual error. Anyway, I'll keep monitoring the situation in the upcoming days.

Thanks all for your support.

[uncvrd]

This is so odd - I also just upgraded my DO database and ran in to the same exact problem! Thank goodness on timing - I've removed pgbouncer from my connection string and I am awaiting support to respond to my ticket to set the max_prepared_statements=0

[janpio]

You might be fine with pgbouncer=true removed (and the prepared statements enabled), as the reason for it existing is fixed with PgBouncer 1.21.0 @uncvrd. We will communicate that wider as soon as we have tests to confidently prove that - so please let us know about your experience.

We were just hit with this ourselves after a minor upgrade of our PostgreSQL instance, for the test setup that ensures Prisma works with PgBouncer with the PgBouncer mode. So for us removing pgbouncer=true is not an option right now, and I need to wait for support to make the suggested change of disabling the prepared statements for now.

[ikoichi]

The issue started again without me changing anything on the DB or code side. I tried with and without pgbouncer=true but I still get prepared statement \"PGBOUNCER_2451\" does not exist.

My only option at the moment is to avoid using the connection pool at all.

[MorenoMdz]

I already removed pgbouncer=true but the errors persist (randomly)

Did you remove it from all clients/applications talking to PgBouncer, because if one still has pgbouncer=true then that poisons connections for all other clients. After you removed it everywhere you should restart pgbouncer or run RECONNECT in its admin console. To make sure that all existing server connections are removed.

I suppose max_prepared_statements=0 without pgbouncer=true is the optimal setup, isn't it?

max_prepared_statements >= 0 without pgbouncer=true is the fastest setup as reported by some prisma users. But it's also new, so a bit experimental. max_prepared_statements=0 with pgbouncer=true is slower, but known to be working well as this is what you've been using before.

Is it possible to run this RECONNECT command from PSQL?

[janpio]

The issue started again without me changing anything on the DB or code side. I tried with and without pgbouncer=true but I still get prepared statement \"PGBOUNCER_2451\" does not exist.

My only option at the moment is to avoid using the connection pool at all.

The error message indicates that the PostgreSQL server does not have a prepared statement that PgBouncer expects (so max_prepared_statements is set to a value higher than 0) and tries to use it. This can happen when you run DEALLOCATE ALL via that PgBouncer, which is the case when you use pgbouncer=true in Prisma.

If you use max_prepared_statements >0 do not pgbouncer=true anywhere with that connection string. Then restart PgBouncer to put it into a good state where it knows all the prepared statements that exist. (As soon as a Client connects again with pgbouncer=true it will fail again, or when someone manually runs DEALLOCATE ALL on that PgBouncer instance)

[nugmanoff]

Bumping

Stumbled into this while considering moving to PgBouncer for connection pooling. I am using Digital Ocean as well

[AlbinoDrought]

(As soon as a Client connects again with pgbouncer=true it will fail again, or when someone manually runs DEALLOCATE ALL on that PgBouncer instance)

For what it's worth, support for DEALLOCATE ALL was added to PgBouncer 1.22.0: https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_22_0 (PgBouncer 1.23.0 is out now)