Re: Prepared Statements vs. pgbouncer

On Sat, Sep 29, 2007 at 10:36:33AM +0100, Gregory Stark wrote:
>
> "Paul Lindner" <lindner@inuus.com> writes:
>
> >   * pgbouncer notices that client A is idle and reassigns backend to
> >     client B
>
> What do you mean by "notices"?

Okay, I wasn't being clear.  In pgbouncer at the END of a commit the
backend will be put into the idle pool.  Please read the following:

https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer

> Prepared statements are only one form of state which can persist beyond a
> transaction end. I don't think you can reassign connections unless you get
> some sort of explicit notice that the client is done with any state it has set
> up. Either the driver supports noticing such a state because there are no
> active references to its handle or the client issues a statement like RESET
> ALL or something equivalent.

Okay.

How do we fix this?  Short term?  Long term?

For this specific case a long term fix might involve
transaction-scoped prepared statements.  Of course that would require
adding this feature on many levels.

Should middleware products track all prepared statements and re-send
those to each backend?  What happens when you have collisions between
names?

Should auto-generated prepared statements use a common hashing method
to insure that we don't recreate the same prepared statement over and
over?

--
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner@inuus.com