Expected good QueryExecMode configurations with PgBouncer 1.21.0? #1784

dropwhile · 2023-10-29T23:01:54Z

dropwhile
Oct 29, 2023

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 · 2023-10-30T14:06:16Z

jackc
Oct 30, 2023
Maintainer

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.

5 replies

dropwhile Oct 30, 2023
Author

@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 Oct 30, 2023
Maintainer

Yes.

dropwhile Oct 30, 2023
Author

thanks again @jackc <3

jaitaiwan Apr 5, 2024

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

michalk-k Apr 5, 2024

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 · 2024-04-03T17:22:26Z

michalk-k
Apr 3, 2024

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?

0 replies

jackc · 2024-04-07T14:16:44Z

jackc
Apr 7, 2024
Maintainer

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.

0 replies

Expected good QueryExecMode configurations with PgBouncer 1.21.0? #1784

Uh oh!

Uh oh!

dropwhile Oct 29, 2023

Replies: 3 comments · 5 replies

Uh oh!

jackc Oct 30, 2023 Maintainer

Uh oh!

Uh oh!

dropwhile Oct 30, 2023 Author

Uh oh!

jackc Oct 30, 2023 Maintainer

Uh oh!

dropwhile Oct 30, 2023 Author

Uh oh!

jaitaiwan Apr 5, 2024

Uh oh!

Uh oh!

michalk-k Apr 5, 2024

Uh oh!

michalk-k Apr 3, 2024

Uh oh!

jackc Apr 7, 2024 Maintainer

dropwhile
Oct 29, 2023

Replies: 3 comments 5 replies

jackc
Oct 30, 2023
Maintainer

dropwhile Oct 30, 2023
Author

jackc Oct 30, 2023
Maintainer

dropwhile Oct 30, 2023
Author

michalk-k
Apr 3, 2024

jackc
Apr 7, 2024
Maintainer