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, Pennsylvania 19073

Fri, 8 Aug 2003 13:36:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 16:00:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 
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.
Fri, 08 Aug 2003 16:09:28 -0400
Rod Taylor <rbt@rbt.ca>
Re: WITH HOLD and pooled connections
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, Pennsylvania 19073

Fri, 8 Aug 2003 16:22:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 16:24:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 

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, Pennsylvania 19073

Fri, 8 Aug 2003 16:25:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 16:28:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 
> 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.
Fri, 08 Aug 2003 16:28:30 -0400
Rod Taylor <rbt@rbt.ca>
Re: WITH HOLD and pooled connections
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, Pennsylvania 19073

Fri, 8 Aug 2003 16:31:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 16:33:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 
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

Fri, 08 Aug 2003 16:36:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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, Pennsylvania 19073

Fri, 8 Aug 2003 16:41:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections
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


Fri, 08 Aug 2003 16:47:14 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 16:53:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 
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, Pennsylvania 19073

Fri, 8 Aug 2003 17:01:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections
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

Fri, 08 Aug 2003 17:33:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: WITH HOLD and pooled connections 

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
>  
>


Fri, 08 Aug 2003 21:58:06 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: WITH HOLD and pooled connections
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


Fri, 8 Aug 2003 22:12:09 -0400
Neil Conway <neilc@samurai.com>
Re: WITH HOLD and pooled connections

Added to TODO:

	o Allow pooled connections to query prepared queries
	o 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, Pennsylvania 19073

Sun, 17 Aug 2003 00:43:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: WITH HOLD and pooled connections