Thread: WITH HOLD and pooled connections
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? -- 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
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".
regards, tom lane
On Fri, 2003-08-08 at 16:00, 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". Actually, prepared statements in a pooled environment would be very useful if you could ask what the currently prepared names for that connection are.
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. -- 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
> Hm. Good thought. Maybe the same is true of held cursors. > > It would be a simple addition at either the protocol or SQL level to > list the names of existing prepared statements and portals. Probably > too late for 7.4 though. Yup.. spot these two up on the TODO list.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> TODO item?
Yeah. I'm leaning towards the idea that a protocol addition is what to
do.
>> Hm. Good thought. Maybe the same is true of held cursors.
>>
>> It would be a simple addition at either the protocol or SQL level to
>> list the names of existing prepared statements and portals. Probably
>> too late for 7.4 though.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Yeah. I'm leaning towards the idea that a protocol addition is what to
>> do.
> I am confused why a protocol change would be desired.
I was thinking that you might want it at a lower level than having to
issue a SQL command to get at the data. A lot of what we changed in
the 7.4 protocol revision involved eliminating the need to use SQL
commands to get at information that a client library needs.
But see my later note questioning just how useful these features would
really be.
regards, tom lane
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.
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
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > TODO item? > > Yeah. I'm leaning towards the idea that a protocol addition is what to > do. I put this under "Clients": * Allow clients to query WITH HOLD cursors and prepared statements I am confused why a protocol change would be desired. I though we would just have a function that listed that info to the client. -- 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
TODO item? --------------------------------------------------------------------------- Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > On Fri, 2003-08-08 at 16:00, 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". > > > Actually, prepared statements in a pooled environment would be very > > useful if you could ask what the currently prepared names for that > > connection are. > > Hm. Good thought. Maybe the same is true of held cursors. > > It would be a simple addition at either the protocol or SQL level to > list the names of existing prepared statements and portals. Probably > too late for 7.4 though. > > regards, tom lane > -- 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
Rod Taylor <rbt@rbt.ca> writes:
> On Fri, 2003-08-08 at 16:00, 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".
> Actually, prepared statements in a pooled environment would be very
> useful if you could ask what the currently prepared names for that
> connection are.
Hm. Good thought. Maybe the same is true of held cursors.
It would be a simple addition at either the protocol or SQL level to
list the names of existing prepared statements and portals. Probably
too late for 7.4 though.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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.
I don't think you could call that CREATE OR REPLACE, because (if it's
supposed to be efficient) it *wouldn't* replace. In any case I think
apps would prefer to avoid the overhead of even issuing such a command.
If you approach it that way, you're adding some number of additional
SQL commands to each pooled transaction, which seems to defeat the
purpose of pre-preparing commands.
regards, tom lane
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". > > regards, tom lane > Ouch. Double ouch in fact. I'm using prepared statements extensively in my current (pooled conn) app. All pure selects. Can this be narrowed down a bit? Is it a problem on all query types? On all versions? What if the statement object (in JDBC) is closed? If it's really a problem I'll have to do some nasty last-minute recoding (again!) :-( (or abandon using pooling, which I would also hate). andrew
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
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > 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. > > I don't think you could call that CREATE OR REPLACE, because (if it's > supposed to be efficient) it *wouldn't* replace. In any case I think > apps would prefer to avoid the overhead of even issuing such a command. > If you approach it that way, you're adding some number of additional > SQL commands to each pooled transaction, which seems to defeat the > purpose of pre-preparing commands. Of course, it wouldn't be called "CREATE OR REPLACE" but something like that, or a flag to PREPARE. I assume the prepare overhead is much higher than a single query parse. How would you get the info without issuing a command to get it. You could get it via some protocol request, but that seems pretty hard to manage from libpq up to your interface, and is the extra effort worth avoiding sending a query? -- 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
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> 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".
> Ouch. Double ouch in fact. I'm using prepared statements extensively in
> my current (pooled conn) app. All pure selects.
> Can this be narrowed down a bit? Is it a problem on all query types?
The point is just that there's no infrastructure to manage prepared
statements, eg for a thread to discover whether someone has already
prepped a particular statement on the current connection. Evidently
you have set things up so that you don't need to do that. Panic not.
regards, tom lane
Whew. To the best of my knowledge, JDBC at least doesn't provide any API by which one could discover such a thing anyway, (although I guess a given driver could implement some sort of statement cache with a name lookup mechanism). I guess if it were part of the standards JDBC API we'd have heard calls for its support by now. When you think about it its a nice idea. (You are right - all my prepped statements are used and disposed of within a single use of a connection in a single thread.) OK ... back to logging stuff ... andrew Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>Tom Lane wrote: >> >> >>>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". >>> >>> > > > >>Ouch. Double ouch in fact. I'm using prepared statements extensively in >>my current (pooled conn) app. All pure selects. >>Can this be narrowed down a bit? Is it a problem on all query types? >> >> > >The point is just that there's no infrastructure to manage prepared >statements, eg for a thread to discover whether someone has already >prepped a particular statement on the current connection. Evidently >you have set things up so that you don't need to do that. Panic not. > > regards, tom lane > >
On Fri, Aug 08, 2003 at 04:00:22PM -0400, Tom Lane wrote: > 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". Well, unless you're prepared to manually manage these resources yourself (i.e. DEALLOCATE a prepared query when you're finished with it, CLOSE a holdable cursor). -Neil
Added to TODO: o Allow pooled connections to query prepared querieso Allow pooled connections to close all open WITH HOLD cursors --------------------------------------------------------------------------- Neil Conway wrote: > On Fri, Aug 08, 2003 at 04:00:22PM -0400, Tom Lane wrote: > > 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". > > Well, unless you're prepared to manually manage these resources > yourself (i.e. DEALLOCATE a prepared query when you're finished > with it, CLOSE a holdable cursor). > > -Neil > > -- 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