Re: WITH HOLD and pooled connections

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.

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.

regards, tom lane