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@inuus.com