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

[vietj]

I'm trying the reproducer again and I'm getting

Caused by: org.postgresql.util.PSQLException: FATAL: database "test" does not exist
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:646)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:180)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
	at org.postgresql.Driver.makeConnection(Driver.java:468)
	at org.postgresql.Driver.connect(Driver.java:267)
	at io.agroal.pool.ConnectionFactory.createConnection(ConnectionFactory.java:210)
	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:513)
	at io.agroal.pool.ConnectionPool$CreateConnectionTask.call(ConnectionPool.java:494)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
	at io.agroal.pool.util.PriorityScheduledExecutor.beforeExecute(PriorityScheduledExecutor.java:75)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1141)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)

also could you provide a reproducer without quarkus ?

[vietj]

ping

[shivamsriva31093]

I have created a simple reproducer. Please let me know if it works for testing.

https://github.com/shivamsriva31093/pgbouncer-vertx-reproducer/tree/master

you can change database properties in application.properties (switch between ports 5432 (Postgres) and 6432 (pgbouncer)). Pgbouncer is started using docker container in transaction pooling mode. A Liquibase migration creates a table where prepared statement can be run to see the behaviour using an HTTP request.

[vietj]

@shivamsriva31093 I'll have a look, thanks

[doppelrittberger]

Sorry for the late reply. The project moved temporarily to Jdbi for now. Thanks to @shivamsriva31093 for the fixed reproducer

[vietj]

I am trying the reproducer and I get this issue:

org.postgresql.util.PSQLException: FATAL: unsupported startup parameter: extra_float_digits
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:623)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:163)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:215)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225)
	at org.postgresql.Driver.makeConnection(Driver.java:466)
	at org.postgresql.Driver.connect(Driver.java:265)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:230)
	at com.example.pgbouncer.MainVerticle.runLiquibase(MainVerticle.java:45)
	at com.example.pgbouncer.MainVerticle.start(MainVerticle.java:98)
	at io.vertx.core.impl.DeploymentManager.lambda$doDeploy$5(DeploymentManager.java:196)
	at io.vertx.core.impl.AbstractContext.dispatch(AbstractContext.java:100)
	at io.vertx.core.impl.AbstractContext.dispatch(AbstractContext.java:63)
	at io.vertx.core.impl.EventLoopContext.lambda$runOnContext$0(EventLoopContext.java:44)
	at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:164)
	at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:469)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:503)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:986)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:1493)

[shivamsriva31093]

@vietj yes thats pgbouncer settings.Please run the pgbouncer instance with following command:

docker run -p 6432:6432 -e"POSTGRESQL_HOST=$(ifconfig -u | grep 'inet ' | grep -v 127.0.0.1 | cut -d\ -f2 | head -1)" -e "POSTGRESQL_USERNAME=postgres" -e "POSTGRESQL_PASSWORD=test" -e "POSTGRESQL_DATABASE=postgres" -e "PGBOUNCER_POOL_MODE=transaction" -e "PGBOUNCER_PORT=6432" -e "PGBOUNCER_IGNORE_STARTUP_PARAMETERS=extra_float_digits" --name=pgbouncer bitnami/pgbouncer

missed updating the readme

[colinchilds]

Any update on this? I'm trying to use pgbouncer and am blocked by this issue.

[lasteris]

hi. I am also stucked on this. recently, I ve described my case in discord: "Hi all. Please tell me what can be done. I have a Quarkus project (updated to 2.16.5.Final) Reactive-resteasy. But it is written in an imperative style. I'm trying to convert it to reactive. In particular, I'm trying to use Vertx/PgPool to connect to the database (very convenient, by the way). But my application is behind a Yandex Odyssey server application pool. The server pool is running in transaction mode. We can't change it to Session mode. As a result, when using PreparedStatement, we encounter the error "the unnamed parameter does not exist". I know, that its server pool related problem in transaction mode, but JDBC has a preparedThreshold parameter. We set it to 0 to avoid the error above. But I did not find such a parameter in the vertx documentation. I even tried to shove binary_parameters=yes there (undocumented postgres connection string parameter) to send parameters as binary. Useless. Are there any other options?" May be we can help somehow to sort this problem out ? I think vertx pgclient surely will be the best option for future projects...

[shivamsriva31093]

I managed to work around by wrapping the execution in transactions to ensure prepared statement binding is done within the same context. In the pgbouncer side, add this function to the pgbouncer accessible databases:

create function deallocate_all() returns void language plpgsql as $$ declare r record; BEGIN for r in select 'deallocate "' || name || '";' as dealloc from pg_prepared_statements loop raise notice 'Command: %', r.dealloc; execute r.dealloc; end loop; END; $$;

This will clear prepared statements to ensure transaction pooling works as expected. Pair this by adding ?prepareThreshold=0 parameter to database configuration either in jdbc/postgres url or passing while creating the pgpool in vertx.

I referenced this url to make the pgbouncer compatible server work with our vertx application. I am hoping for vertx pg pool to natively support the configuration.

link1 link2

[vietj]

thanks for the reproducers, we actually might have a fix for this soon