Support for pgBouncer transaction pooling mode

[doppelrittberger]

Version

4.2.4

Context

I use pgBouncer and postgres in my project and now I´m facing issues when using transaction pooling mode in pgBouncer (https://www.pgbouncer.org/features.html). It seems that PreparedQueries are not supported (as described at the pgBouncer documentation) but most JDBC libraries still support PreparedQueries using binary parameters mode (like JDBI, explanation is here: https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/). Is there any way to configure Vertx SQL Client to also use the same approach? The transaction pooling mode is the default and performance-wise the best mode and other libraries support this use case as well. Since quarkus uses Vertx SQL Client as default JDBC library it would be nice if this would be supported as well :)

Do you have a reproducer?

I created a test project at github to showcase the behavior. I used quarkus to simplify the project but use mainly the vertx methods.
Please use the included infra-pgbouncer.yml docker compose file to create a postgres/pgbouncer instance. Then run the Start.main() and see the issues.
I included a comparison to JDBI which works with pgbouncer using the same queries in transaction pooling mode.

Steps to reproduce

1. checkout the repoducer
2. use docker-compose to setup a pgbouncer instance (docker-compose -f infra-pgbouncer up -d)
3. run the Start.main() -> Vertx code will fail with "ERROR: unnamed prepared statement does not exist (26000)", JDBI will work correctly

[vietj]

I read the page : https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/

it is not clear to me how it works since simple queries don't have parameters.

what I understand is that one is able to execute a prepared query (with $1, $2) and pass parameters but using the simple query interaction protocol ?

can you run a simple example this with a client that supports this and make a Wireshark capture so I can have a look at the wire protocol to better understand ?

[vietj]

have you tried running this with disabling the prepared statement cache ? it will still use prepared statement but not cache them.

[doppelrittberger]

vertx.log
jdbi.log

Hi vietj,
I attached 2 logs, one from vertx client and one from jdbi, both using TCPdump since I cannot install Wireshark in my env.
By default quarkus seems to disable client cache for prepared statements: https://quarkus.io/guides/reactive-sql-clients#quarkus-reactive-datasource_quarkus.datasource.reactive.cache-prepared-statements

[doppelrittberger]

I don´t know the protocol in detail but it seems that JDBI wraps all calls in BEGIN ... COMMIT (transactions) which could explain why this works since every transaction is executed using the same connection in pgBouncer transaction pooling mode.

[doppelrittberger]

I just re-validated and if I wrap every prepared statement with a transaction the code works... So the question is: does JDBI really use transactions or does it use different commands?

[vietj]

what is JDBI ?

[doppelrittberger]

The library I use for comparison: https://jdbi.org/
Its a thin wrapper around JDBC that seems to support prepared statements in pgBouncer transaction mode. I used it since with this library I was able to select/insert using prepared statements without explicit transactions

[vietj]

I found out that prepared statements are actually simple queries that emulates prepared statement by interpolating queries (i.e select $1 with "a" is rewritten as select "a").

I'm not much comfortable with this solution as it potentially introduces security issues (SQL injection) in Vert.x client.

I will check the reproducer and see if anonymous prepared statements can solve this and if it does, I'll document it.

[doppelrittberger]

Ok, sounds good. If you need any help please let me know

[vietj]

I'm trying the reproducer and I get

julien@Juliens-MacBook-Pro vertx-pgbouncer-bug % docker-compose -f infra-pgbouncer.yml up -d
Starting vertx-pgbouncer-bug_postgres_1 ... done

ERROR: for pgbouncer  Container "01afad227300" is unhealthy.
ERROR: Encountered errors while bringing up the project.

[doppelrittberger]

Hi,
my friend fixed the docker-compose file. You may now try again...

[vietj]

I'll have a look again soon

[doppelrittberger]

Did you succeed with the docker file?

[vietj]

net yet the opportunity to look