Prepared Statements vs. pgbouncer - Mailing list pgsql-jdbc

From Paul Lindner
Subject Prepared Statements vs. pgbouncer
Date
Msg-id 20070929002513.GG3140@inuus.com
Whole thread Raw
Responses Re: Prepared Statements vs. pgbouncer
Re: Prepared Statements vs. pgbouncer
List pgsql-jdbc
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

Attachment

pgsql-jdbc by date:

Previous
From: "Mike C."
Date:
Subject: Re: [BUGS] BUG #2856: Jdbc 4 connector running on JDK 1.6 should
Next
From: Oliver Jowett
Date:
Subject: Re: Prepared Statements vs. pgbouncer