How to get rid of prepared statement error when working with PostgreSQL via PgBouncer?

Question

PostgreSQL

Sometimes there is something like this error when trying to make a request in the code:

Invalid sql statement name: 7 ERROR: prepared statement "some name" does not exist

The database lives on a remote server, PgBouncer in a Docker container, the application too.
There is a feeling that PgBouncer prepares the expression to speed up the work on the first run, but after stopping execution somewhere in the middle and re-starting, PgBouncer still thinks that the expression should remain on the server, but the server no longer thinks so.

How can this be avoided, or how can it be treated?

Answer 1

There is a feeling that PgBouncer prepares the expression to speed up the work on the first run, but after stopping execution somewhere in the middle and re-starting, PgBouncer still thinks that the expression should remain on the server, but the server no longer thinks so.

Replace "PgBouncer" with "your application" and you'll get a relatively accurate description of what's going on.
With almost 100% probability, you have pgbouncer configured in transaction pool mode. And the execution of any new request in the extended protocol (prepared statement) consists of three calls:
- prepare
- bind
- execute
There is nothing wrong with the fact that pgbouncer configured in transaction pool mode can send each of these 3 messages to different base connections. This is a clearly documented limitation of the transaction pool. The result is natural, the corresponding processes of the base do not understand what it is about and express surprise at such errors.
Disable the use of server-side prepared statements on the application.