Prepared Statements vs. pgbouncer

[Paul Lindner <lindner(at)inuus(dot)com>]

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

Attachment: pgpoJt8pN42f4.pgp
Description: PGP signature