Prepared statements - psycopg3, psycopg2

[joseortiz3]

The current psycopg3 documentation says:

Note however that Psycopg doesn’t use SQL statements such as PREPARE and EXECUTE, but protocol level commands such as the ones exposed by PQsendPrepare, PQsendQueryPrepared.
Using external connection poolers, such as PgBouncer, is not compatible with prepared statements, because the same client connection may change the server session it refers to.

The current pgbouncer documentation says:

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. Running DEALLOCATE ALL and DISCARD ALL is especially problematic, since those commands appear to run successfully, but they mess up with the state of the server connection significantly without PgBouncer noticing. Which in turn will very likely break the execution of any further prepared statements on that server connection.

Is the psycopg3 documentation outdated now? Is pgbouncer known to work with it?

Also, I take it, psycopg2 doesn't do protocol-level prepared statements at all, correct?

[JelteF]

Psycopg3 docs are outdated now. However, it's not completely working with PgBouncer at the moment. The reason is that to close prepared statements it uses the DEALLOCATE sql command.

You can disable the deallocation behaviour in psycopg by setting prepare_threshold to float('inf') (or some very high number).

Related links:

• Tracking issue at psycopg: Using Close protocol message instead of DEALLOCATE to help prepared statements in PgBouncer psycopg/psycopg#589
• Similar issue with pdo (PHP): [PHP/PBO] 'prepared statement "pdo_stmt_00000001" does not exist' with transaction pooling mode and max_prepared_statements > 0 #991
• Prerequisite to fix this issue that I contributed to postgres: postgres/postgres@28b5726
• Prerequisite to fix this issue that I contributed to psycopg: Add Close wrappers to PGconn psycopg/psycopg#603

[riteshgl]

@JelteF we are facing a similar issue in our project based on RoR.
In Ruby ActiveRecord also a DEALLOCATE sql command is being used. Setting the statement_limit for our service to a very high number is one the of the solutions but one thing I wanted to understand is in this case what should be the value of max_prepared_statements on pgBouncer?
Should it be higher than the limit set at RoR service level or will setting it to a lower value also work fine?

[JelteF]

Setting max_prepared_statements to a lower value on PgBouncer will work fine, and even is the recommended approach. Otherwise the connections to Postgres will probably have many prepared statements (which can result in perf issues at the Postgres side).

[riteshgl]

@JelteF Thanks for the response. I see that setting the max_prepared_statement count lower works as pgBouncer stores all the prepared statements in a global cache and uses global cache to get the prepared statement.
I wanted to know a few more things on behaviour of global cache here

1. Is the data ever evicted from global cache?
2. If my application is running high number of unique queries won't this cause the PgBouncer memory to keep increasing?

Thanks again for the help.

[JelteF]

1. Yes, when a query is not referenced by any client or server it is removed from the global cache.
2. Yes, it will continue to increase in that case, but only if you never close your client connections. Regularly closing of client connections should put a limit on the increase, e.g. closing a connection after 10 minutes. Usually client side connection poolers have settings to do this.