Expected good QueryExecMode configurations with PgBouncer 1.21.0?

[dropwhile]

pgbouncer 1.21.0 release has support for prepared statements.

The pgbouncer docs also have this note:

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.

What are expected working combinations for pgx QueryExecMode and pgbouncer 1.21.0, assuming no explicit calls to Conn.Prepare?

QueryExecMode	pgbouncer pool_mode	works
QueryExecModeCacheStatement	session	?
QueryExecModeCacheStatement	transaction	?
QueryExecModeCacheStatement	statement	?
QueryExecModeCacheDescribe	session	yes?
QueryExecModeCacheDescribe	transaction	?
QueryExecModeCacheDescribe	statement	?
QueryExecModeDescribeExec	session	yes?
QueryExecModeDescribeExec	transaction	?
QueryExecModeDescribeExec	statement	no?
QueryExecModeExec	session	yes
QueryExecModeExec	transaction	yes
QueryExecModeExec	statement	?
QueryExecModeSimpleProtocol	session	yes
QueryExecModeSimpleProtocol	transaction	yes
QueryExecModeSimpleProtocol	statement	?

[jackc]

I don't think the new PgBouncer feature actually changes anything with regard to pgx. QueryExecModeCacheStatement should not be used. I would expect any of the others modes to behave the same as when directly connected to PostgreSQL. And explicit prepared statements should not be used as Deallocate is implemented with a SQL query which PgBouncer won't detect.

[dropwhile]

@jackc thanks for that info!
Does the following look correct to you then (assuming no explicit prepared statements)?

QueryExecMode	pgbouncer pool_mode	a usable config?
QueryExecModeCacheStatement	session	no
QueryExecModeCacheStatement	transaction	no
QueryExecModeCacheStatement	statement	no
QueryExecModeCacheDescribe	session	yes, assuming scheme does not change
QueryExecModeCacheDescribe	transaction	yes, assuming scheme does not change
QueryExecModeCacheDescribe	statement	no (not transaction safe)
QueryExecModeDescribeExec	session	yes
QueryExecModeDescribeExec	transaction	yes
QueryExecModeDescribeExec	statement	no (not transaction safe)
QueryExecModeExec	session	yes
QueryExecModeExec	transaction	yes
QueryExecModeExec	statement	no (not transaction safe)
QueryExecModeSimpleProtocol	session	yes
QueryExecModeSimpleProtocol	transaction	yes
QueryExecModeSimpleProtocol	statement	no (not transaction safe)

[jackc]

Yes.

[dropwhile]

thanks again @jackc <3

[jaitaiwan]

Would it be possible to see this table added to the official documentation?

[michalk-k]

I believe this table has to be revised.
Based on my recent tests, QueryExecModeCacheStatement works as expected in transaction pooling mode. In fact, it should be the most preferred option.

[michalk-k]

I don't think the new PgBouncer feature actually changes anything with regard to pgx. QueryExecModeCacheStatement should not be used. I would expect any of the others modes to behave the same as when directly connected to PostgreSQL. And explicit prepared statements should not be used as Deallocate is implemented with a SQL query which PgBouncer won't detect.

Not sure why you discourage from using CacheStatement mode?
I've tested it with pgbouncer 1.22.1 which has the same feature-set as 1.21.0 in regard of prep statements in transaction pooling. And it works just correctly (on the table at least).

What bad behavior should I focus on when testing this mode?

[jackc]

Since my original reply, Deallocate is now implemented at the protocol layer instead of as a SQL statement. This presumably improved PgBouncer compatibility.

Not sure why you discourage from using CacheStatement mode?

I don't use PgBouncer myself. But I know it didn't use to work. Maybe it does now with the right version of PgBouncer or incantation of settings.