Prepared Statements vs. pgbouncer

Hi,

I've been trying to implement pgbouncer in my environment and have
come up against a few issues with the JDBC driver.

The symptoms surface as an error redefining a the statement S_1.

"S_1" already exists

I read the documentation and set prepareThreshhold=0, and the problem
decreased but did not go away. Digging deeper I believe I've found
out why this occurs. The problem only occurs when JDBC statements use
a non-zero fetchSize. This results in the driver creating a named
statement.

Specifically the problem occurs when clients use a recycled backend.
Here's the scenario:

* Client A creates statement S_1, fetches results.
When finished the statement is put in a cleanup queue.
* time passes
* pgbouncer notices that client A is idle and reassigns backend to
client B
* client B tries to create statement S_1, but fails. (S_1 is already
defined)
...

So the combination of lazy cleanup and the assumption of a singular
client->server backend is causing this problem.

I can see two solutions:

1) Forcing cleanup of prepared statements when exiting a transaction
block. (Seems like this would be hard...)

2) Allowing the client to specify a unique prefix for the statement
handles. (This seems easy, but could result in a leak of named
statements in the scenario above)

I'd be fine with #2, since we can configure pgbouncer to recycle
backend connections to protect us against prepared statement leaks.

I'd be happy to work to make this possible.

Thanks!

--
Paul Lindner ||||| | | | | | | | | |
lindner(at)inuus(dot)com