Re: Prepared Statements vs. pgbouncer

On Sat, Sep 29, 2007 at 10:36:33AM +0100, Gregory Stark wrote:
>
> "Paul Lindner" <lindner(at)inuus(dot)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(at)inuus(dot)com