Re: WITH HOLD and pooled connections

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073