Re: WITH HOLD and pooled connections

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Seems we have a problem with pooled connections and WITH HOLD cursors.
> > We have code to reset transaction state and variables via RESET ALL, but
> > how do we remove WITH HOLD cursors when we pass a connection to a new
> > client?
> >> 
> >> Prepared statements would be just as much of a problem.  I think the
> >> correct answer is simply "don't use those features in a pooled
> >> environment".
> 
> > Yuck.  I can't think of any other option.  The pooled connections are
> > all the same user, so there isn't any permission issue here.
> 
> Well, one answer for cursors would be to offer a "CLOSE ALL" sort of
> command.  I'm not sure it's worth the trouble though.  I can't really
> visualize a reason to use held cursors in a pooled environment.  A held
> cursor is pointless unless you intend to use it for more than one
> transaction, and in a pooled environment that would be difficult, no?
> When you commit one transaction and start another, you might not have
> the same connection anymore.

I am thinking more of someone using WITH HOLD cursors and getting
disconnected before being able to close it.

> Rod's right that sharing prepared statements among all users of a pooled
> connection might be interesting.  However, I wonder whether anyone would
> actually use a "list prepared statements" feature to implement it.
> Seems like checking that way would just be a wasted roundtrip for most
> transactions.  I'd be inclined to set up the app so that all the
> required statements are prepared the moment it opens a new connection,
> and the pool users just assume the statements are available.

What would be interesting is a CREATE OR REPLACE functionality for
prepared cursors, where you could ask for it to be prepared, but if it
already existed, it would do nothing, or something like that.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073