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

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

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

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

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

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.

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

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

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

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

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

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:

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

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:

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