[Pgbouncer-general] JDBC driver, prepared stataments and pgbouncer transaction pooling

Omar Kilani omar.kilani at gmail.com
Sat Feb 20 06:44:13 UTC 2010


Hi there,

I was looking around for solutions to the 'ERROR:  prepared statement
"S_1" does not exist' issue when using the Postgres JDBC driver and
pgbouncer in transaction pooling mode (which, IMHO, is a killer
feature of pgbouncer :), and didn't really find anything definitive,
so I thought I'd post my fix in case someone else Googles this
problem.

The pgbouncer FAQ
(http://pgbouncer.projects.postgresql.org/doc/faq.html) says this:

--

Transaction pooling

To make prepared statements work in this mode would need PgBouncer to
keep track of them internally, which it does not do. So only way to
keep using PgBouncer in this mode is to disable prepared statements
completely.

For JDBC this seems to be achieved by adding protocolVersion=2
parameter to connect string.

--

The protocolVersion thing is true, but pgbouncer doesn't support
protocol version 2, so it's not really very helpful. :)

The correct parameter to pass is 'prepareThreshold=0', which disables
named prepared statements in the JDBC driver (yay!), except for
transaction control statements like BEGIN/COMMIT/ROLLBACK (boo!).

To fix the latter problem, I've rolled a patch:

http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch

Which can be applied to the JDBC driver source found here:

http://jdbc.postgresql.org/download/postgresql-jdbc-8.4-701.src.tar.gz

This patch makes the driver send BEGIN/COMMIT/ROLLBACK/etc as unnamed
prepared statements, and pgbouncer in transaction pooling mode now
runs happily for me.

I hope this helps anyone else looking to solve this problem (and I
also hope the patch doesn't make the driver eat your database. :)

Thanks!

Regards,
Omar



More information about the Pgbouncer-general mailing list