PREPARE and transactions

Now, here's a scenario that has us worried:

BEGIN
PREPARE foo AS ...
... [error]
DEALLOCATE foo [fails: already aborted by previous error]
ABORT
BEGIN
PREPARE foo AS ... [fails: foo is already defined!]
EXECUTE foo [fails: already aborted by previous error]
COMMIT [fails: already aborted by previous

error]

Part of the problem is that PREPARE has no provision to overwrite an
existing plan (CREATE OR REPLACE). I run into this all the time because
I make heavy use of prepared statements to emulate an ISAM file system.
I have to jump through hoops to keep track of what statements are
already prepared to keep from bouncing the current transaction.

However, at least for me, nested x basically solves this problem. I'll
just always wrap the prepare statement with a sub-transaction and
commit/rollback as necessary. This is odd because the rollback does
nothing other than guard the following statements from the prepare
failure to execute.
So, you do:

BEGIN
BEGIN
PREPARE foo AS ...
COMMIT/ROLLBACK
... [error]
DEALLOCATE foo [fails: already aborted by previous error]
ABORT
BEGIN
BEGIN
PREPARE foo AS ... [fails: foo is already defined!]
COMMIT/ROLLBACK
EXECUTE foo [will now always run if prepare is aborted]
COMMIT [commit executes]

To me, this is good style and it looks like nested x is going to make
7.5. I have no opinion on whether rollback should affect
prepare/deallocate.

Merlin

"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:

Even if the spec doesn't help, I think a statement prepared within a
transaction should definitely be deallocated at the end of the transaction.

Uh, you do realize that Postgres does *everything* within a transaction?
The above proposal would render prepared statements practically useless.

regards, tom lane

Part of the problem is that PREPARE has no provision to overwrite an
existing plan (CREATE OR REPLACE). I run into this all the time because
I make heavy use of prepared statements to emulate an ISAM file system.
I have to jump through hoops to keep track of what statements are
already prepared to keep from bouncing the current transaction.

Bruce - TODO?:

* PREPARE OR REPLACE...

This would be an incredibly useful command since there's no way of
_checking_ in advance that a name is already used as a prepared statement...

Chris

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Merlin Moncure wrote:

I have to jump through hoops to keep track of what statements are
already prepared to keep from bouncing the current transaction.

Christopher Kings-Lynne wrote:

* PREPARE OR REPLACE...

This would be an incredibly useful command since there's no
way of _checking_ in advance that a name is already used as a
prepared statement...

A check would be nice (and I've asked about it before) but it's
really not a lot of jumping through hoops since each connection has
it's own "namespace" of prepared statements. Since they last until
an explicit deallocate, the simple use of unique names makes it
fairly easy on the application side.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200406232330

-----BEGIN PGP SIGNATURE-----

iD8DBQFA2krxvJuQZxSWSsgRAhLSAJ9othitQerDlB9+J65rVl3EbRT9+QCeJfzH
vFdWCDLvxU/zkFMLEDjpydU=
=OzCx
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:

* PREPARE OR REPLACE...

This would be an incredibly useful command since there's no
way of _checking_ in advance that a name is already used as a
prepared statement...

A check would be nice (and I've asked about it before) but it's
really not a lot of jumping through hoops since each connection has
it's own "namespace" of prepared statements. Since they last until
an explicit deallocate, the simple use of unique names makes it
fairly easy on the application side.

Depends. I've got some report templating code that just replaces some
parameters and executes sql embedded in the template.

Replacing the parameters is neater if I use prepare/execute, but if the
sql gets executed again of course I get an error. The only way to know
if there is a PREPARE is to regexp the sql text - yuck.

Now, you might argue I should make my report code handle prepare
directly, then I'd know if I'd defined it or not. As it happens, that's
not the way things stand though.

--
Richard Huxton
Archonet Ltd

On Wed, Jun 23, 2004 at 03:26:49PM -0400, Tom Lane wrote:

Even if the spec doesn't help, I think a statement prepared within a
transaction should definitely be deallocated at the end of the transaction.

Uh, you do realize that Postgres does *everything* within a transaction?

Well, except prepared statements apparently; I'm not sure why they are an
exception.

When I say "within a transaction" as opposed to outside a transaction, I
mean of course an explicit transaction. If you want a prepared statement
to last throughout the session, I'd say it stands to reason that you
create it outside a transaction--in unfettered session context, so to
speak. I can't see how that would be either less intuitive or harder to
program in the client.

Maybe it would help to think of some precedents. Are there any actions
where it makes sense to disobey rollbacks? Counters spring to mind, but
I guess that's a technical necessity rather than an interface choice.
Session variables may be another one, but IIRC they become strictly
bracketed (when set inside a transaction, naturally) around 7.3. What
else?

The above proposal would render prepared statements practically useless.

Could you elaborate? Wouldn't it be possible to cache the plans across
transactions like I suggested, reusing the old plan if the statement is
re-prepared with the same definition? Or are you saying it's possible,
but wouldn't be helpful?

Jeroen

Jeroen T. Vermeulen wrote:

Well, except prepared statements apparently; I'm not sure why they are

an

exception.

When I say "within a transaction" as opposed to outside a transaction,

I

mean of course an explicit transaction. If you want a prepared

statement

to last throughout the session, I'd say it stands to reason that you
create it outside a transaction--in unfettered session context, so to
speak. I can't see how that would be either less intuitive or harder

to

program in the client.

I disagree. Lots of people use prepared statements for all kinds of
different reasons. A large percentage of them do not need or make use
of explicit transactions. Having to continually rebuild the statement
would be a hassle. The caching mechanism also seems like extra work for
little result (to be fair, I like the idea of multiple backends being
able to make use of the same plan). Generic routines can just always
wrap the prepare statement in a subtransaction, which now allows safety
until such time that a create or replace version becomes available,

Merlin

p.s. Is this correct behavior? A DROP TABLE gives a missing oid error
which is fine, but I don't like this much:

cpc=# create table test (a int, b int, c int);
CREATE TABLE

cpc=# prepare p (int) as select * from test;
PREPARE
cpc=# execute p(0);
a | b | c
---+---+---
(0 rows)

cpc=# alter table test drop column a;
ALTER TABLE
cpc=# execute p(0);
a | b | c
---+---+---
(0 rows)

On Thu, Jun 24, 2004 at 08:51:32AM -0400, Merlin Moncure wrote:

When I say "within a transaction" as opposed to outside a transaction,

I

mean of course an explicit transaction. If you want a prepared

statement

to last throughout the session, I'd say it stands to reason that you
create it outside a transaction--in unfettered session context, so to
speak. I can't see how that would be either less intuitive or harder

to

program in the client.

I disagree. Lots of people use prepared statements for all kinds of
different reasons. A large percentage of them do not need or make use
of explicit transactions. Having to continually rebuild the statement
would be a hassle. The caching mechanism also seems like extra work for

I think we're talking at cross purposes here... If the client doesn't use
explicit transactions, as you say is common, then you're obviously not
defining prepared statements inside explicit transactions either. And so
you're certainly not going to be bothered by what happens at the end of a
transaction! In that case, what I'm saying doesn't affect you at all, in
any way.

But let's look at the case where you do use explicit transactions, which
is what we're talking about. I think there should be a difference between

(1) BEGIN
PREPARE foo AS ...
...
COMMIT/ABORT

(2) PREPARE foo AS ...
BEGIN
...
COMMIT/ABORT

There currently is no difference. Even if you abort the transaction, you
will still have that prepared statement (which may require manual cleaning
up), unless you aborted because of an error which occurred inside the
transaction and before or during the PREPARE, in which case trying to
clean up the statement would be an error. You can try to clean up the
prepared statement inside the transaction, but it would not work if
there were an error or abort between the PREPARE and the DEALLOCATE.

That sounds messy to me.

What I propose is simply that these two behave as follows:

(1) PREPARE foo AS ...
BEGIN
...
COMMIT/ABORT

In this case, foo is defined for the duration of the session *just like
current behaviour.* The presence of the transaction isn't relevant here
at all; it's only there for comparison. Commit or abort of the
transaction doesn't affect foo, because foo has been defined outside
the transaction in "unfettered session context," for want of a better
term.

Presumably you're going to use foo in several transactions, and/or in
several statements that are not in any explicit transaction. Unless you
deallocate explicitly, foo will be there as long as you stay connected,
just like you're used to.

(2) BEGIN
PREPARE foo AS ...
...
COMMIT/ABORT

Here, the PREPARE is inside the transaction so at the very least, you'd
expect its effect to be undone if the transaction aborts. I would go
further and say "if you wanted foo to persist, you would have prepared
it before going into the transaction" but that's a design choice.
Deallocating at commit/abort would have the advantage that you always
know whether foo exists regardless of the transaction's outcome: if
defined inside the transaction, it lives and dies with the transaction.
If defined merely in the session (i.e. not in any transaction), it lives
and dies with the session.

So you use this second form when you don't intend to reuse this statement
after the transaction. If you do, OTOH, you use the first form. It
also means that you don't "leak" prepared statement plans if you forget
to deallocate them--remember that the prepared statement may be generated
on-the-fly based on client-side program variables.

little result (to be fair, I like the idea of multiple backends being
able to make use of the same plan). Generic routines can just always
wrap the prepare statement in a subtransaction, which now allows safety
until such time that a create or replace version becomes available,

The nested-transaction version allows you to add code to deal with the
uncertainty that I'm proposing to remove. In the current situation, it's
annoyingly hard to figure out whether the prepared statement exists so you
redefine it "just in case," going through a needless subtransaction abort
or commit. That's the nested-transaction solution you appear to favour;
but AFAICS _this_ is the approach where you have to "continually rebuild
the statement." With my version, you don't need to go through all that
because you're allowed to _know_ whether the statement exists or not.

I don't even think the nested-transaction approach helps with anything:
if you want to re-prepare foo for continued use in the rest of the session
just in case it wasn't around anymore (and ignore the likely error for
the redefinition), you might as well do so before you go into your
transaction in the first place. No nested transactions needed.

Jeroen

"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:

I think we're talking at cross purposes here... If the client doesn't use
explicit transactions, as you say is common, then you're obviously not
defining prepared statements inside explicit transactions either.

This whole discussion seems to be considering only the case of PREPAREs
issued as SQL statements, by a programmer who is fully cognizant of
where he's beginning and ending transactions.

The issue I was trying to raise at the beginning of the thread was: what
about prepared statements created by client libraries (think JDBC for
instance) using the V3 protocol Parse message? Rolling back a
successful prepare because of a later transaction failure seems like
exactly not what such a library would want.

regards, tom lane

[ all snipped ]

A problem with redefining the lifetime of a PREPARE'd statement
according to if it was defined within an explicitly managed transaction
or not would be with middlewares such as J2EE / EJB containers. The
container / JDBC driver performs most operations within explicitly
managed transactions *by the middleware container*, and, if the
middleware container is configured to cache prepared statements between
transactions, then it will expect them to live well beyond their
initial explicitly-managed transaction.

----
James Robinson
Socialserve.com

Just my 2 cents here. I agree with tom that the curent behevior for the v3
protocol is the right one. I use "On demand" preparation. The first time a
statement is needed for a specific connection, it is prepared and the client
keep track of that (reusing the prepared statement for subsequent calls). If
the transaction where the statement is prepared is aborted for whatever reason,
the prepared statement MUST remain valid for this to work, otherwise I need to
track if the transaction where the statement have been prepared commited or not
and invalidate it if it's not the case. This is a waste of time : tracking
transaction state / preparing a statement more than once. The only case where
rolling back a prepared statement can make sense is with DDL modifying
underlying objects (tables, index...).

If this behavior is changed things will breaks for some people.

cyril

----- Message d'origine -----
De : mailto:tgl@sss.pgh.pa.us
Emission : 24/06/2004 16:26:33

On Thu, Jun 24, 2004 at 05:11:48PM +0200, Cyril VELTER wrote:

Just my 2 cents here. I agree with tom that the curent behevior for the v3
protocol is the right one. I use "On demand" preparation. The first time a
statement is needed for a specific connection, it is prepared and the client
keep track of that (reusing the prepared statement for subsequent calls). If
the transaction where the statement is prepared is aborted for whatever reason,
the prepared statement MUST remain valid for this to work, otherwise I need to
track if the transaction where the statement have been prepared commited or not
and invalidate it if it's not the case.

This is why I proposed originally to keep the non-transactional behavior
for Parse messages, but transactional for SQL PREPARE. The latter can
be said to be inside the transaction and should behave like so. I think
this lowers the surprise factor.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

This is why I proposed originally to keep the non-transactional behavior
for Parse messages, but transactional for SQL PREPARE. The latter can
be said to be inside the transaction and should behave like so. I think
this lowers the surprise factor.

It seems like we are closing in on an agreement that that is what should
happen.

regards, tom lane

De : mailto:alvherre@dcc.uchile.cl
Emission : 24/06/2004 18:59:15

On Thu, Jun 24, 2004 at 05:11:48PM +0200, Cyril VELTER wrote:

Just my 2 cents here. I agree with tom that the curent behevior for the v3
protocol is the right one. I use "On demand" preparation. The first time a
statement is needed for a specific connection, it is prepared and the

client

keep track of that (reusing the prepared statement for subsequent calls).

If

the transaction where the statement is prepared is aborted for whatever

reason,

the prepared statement MUST remain valid for this to work, otherwise I need

to

track if the transaction where the statement have been prepared commited or

not

and invalidate it if it's not the case.

This is why I proposed originally to keep the non-transactional behavior
for Parse messages, but transactional for SQL PREPARE. The latter can
be said to be inside the transaction and should behave like so. I think
this lowers the surprise factor.

Yes, as long as there is a libpq call which allow to prepare a statement
without using the SQL PREPARE, which AFAIK does not exist today. something like
PQprepare(conn,name,statement,nParams,paramTypes[]) would do. I just checked my
code and while I use PQexecPrepared, I use the SQL PREPARE to prepare the
statement as there is no other way with libpq.

cyril

I disagree. Lots of people use prepared statements for all kinds of
different reasons. A large percentage of them do not need or make

use

of explicit transactions. Having to continually rebuild the

statement

would be a hassle. The caching mechanism also seems like extra work

for

I think we're talking at cross purposes here... If the client doesn't

use

explicit transactions, as you say is common, then you're obviously not
defining prepared statements inside explicit transactions either. And

so

you're certainly not going to be bothered by what happens at the end

of a

transaction! In that case, what I'm saying doesn't affect you at all,

in

any way.

Ok, I am starting to get your point and perhaps agree with you. Let me
give a little more detail about where I am coming from, and frame it
inside your logic. Also, my situation is probably highly unusual and
maybe unimportant in the grander scheme of things.

I am using PostgreSQL as a backend for legacy COBOL applications and
have written a driver which maps the COBOL I/O statements to SQL
statements. To save a little bit on parsing time and for various other
reasons these SQL statements are handled as prepared queries. Each
COBOL file has a corresponding SQL table in the database and each table
can have up to 7 prepared statements that the application creates when
it needs them. Unless I am misunderstanding things, if you change the
prepared statement's lifetime, I am forced to prepare a bunch of
statements all at once instead of when they are needed. I am prepared
to do this, however (pun intended).

My driver has to be transactionally agnostic: the application that uses
my driver might or might not be in a transaction at any particular point
in time. I can, however, keep track of a flag which tracks if I am in a
transaction. If my driver guesses wrong I get an SQL error which could
potentially bounce the transaction which I may or may not be in. With
nested x, I can guard this with a subtransaction (only necessary when
I'm in a transaction) but I get in trouble if the app opens a trouble
manually through direct SQL.

I do not under any circumstances want to keep re-preparing the statement
so having the prepared statement having a transaction - determined
lifetime under any circumstances is kind of a difficult for me to deal
with. I could keep track of a flag which tells me if I am inside a
transaction (and thus turn off assumptions about future use of the
statement), but there are subtle complexities with this approach (that
get worse with nested x) that I'd like to avoid if at all possible.

Merlin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It seems like we are closing in on an agreement that that is what
should happen.

I was originally unhappy with the current situation, but now I think
it is the best. Any changes will also cause a huge further headache
for driver/application writers, as we already have a released version
(and probably at least one more) with the current behavior. I'd be
all for making a DoesStatementExist(text) function, but changing
the behavior now may be closing the barn doors too late in the game,
and I've yet to see a totally convincing argument for a change,
considering that prepared statements are very explicitly declared
and cannot be seen outside of their own connection.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200406242200

-----BEGIN PGP SIGNATURE-----

iD8DBQFA24ehvJuQZxSWSsgRAkP+AJ9UZD52+BHhnskdwdgHJGvxZ44KbQCggAxl
+5K2gZS37iH60UpiLgumwIU=
=kJgm
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:

I was originally unhappy with the current situation, but now I think
it is the best. Any changes will also cause a huge further headache
for driver/application writers, as we already have a released version
(and probably at least one more) with the current behavior.

Well, we only have *one* released version with the current behavior,
so I think now is the time to change if we're gonna do it ...

regards, tom lane

On Fri, Jun 25, 2004 at 02:00:12AM -0000, Greg Sabino Mullane wrote:

I was originally unhappy with the current situation, but now I think
it is the best. Any changes will also cause a huge further headache
for driver/application writers, as we already have a released version
(and probably at least one more) with the current behavior. I'd be

Granted, that's probably going to force the issue. I do wonder though:
one of the arguments in favour of the current semantics is that the
problems can be worked around using nested transactions. Then what were
people doing before nested transactions, in Tom's scenario where the
programmer doesn't know where transactions begin?

There was also the middleware argument--some intermediate software layer
may be in control of bracketing. But in such cases, can you even rely
on two independent transactions executing in the same session? You'd
need to assume that to make the current semantics work in that situation.
What if the middleware does connection pooling, or restores a broken
connection between two transactions? The latter might happen because of
a timed-out firewall, for instance, when there is a long pause between
two unrelated transactions.

Besides, just the fact that current semantics are completely "out-of-band"
relative to bracketing, I guess it really ought to be any middleware's
responsibility to manage prepared statements. If the application isn't in
control of transactionality, it seems a little iffy to have it fire off
statements that don't affect database state but can make or break future
transactions.

As for the case where statements are prepared on demand when they are
first executed, wouldn't that be better done in the backend? It would
save the application this trouble of keeping track of which statements
have been prepared.

Perhaps the real problem is in the SQL syntax... Imagine a syntax that
doesn't assign a name to a prepared statement, just defines an anonymous
pattern to plan for. The backend would match against the pattern on the
fly, so introducing prepared statements in a program would involve no
changes apart from the PREPAREs. Implementations could ignore them if
they cached plans dynamically anyway; they could implement dynamic and
more effective replacement policies for prepared statements, and share
plans between connections.

Jeroen

Jeroen wrote:

Granted, that's probably going to force the issue. I do wonder

though:

one of the arguments in favour of the current semantics is that the
problems can be worked around using nested transactions. Then what

were

people doing before nested transactions, in Tom's scenario where the
programmer doesn't know where transactions begin?

The trick is that with the current semantics, you don't have to watch
transaction activity, just the prepare statements. You know if and when
(from the client/driver's point of view) a prepared statement exists
because you created it and don't have to be concerned about the
lifetime.

If you guys change the lifetime, it becomes difficult or impossible to
set a flag on the client which guarantees prepared statement existence.
This means I have to wrap the statement execution with a subtransaction
or run the risk of bouncing a current transaction. Currently in the
applications I write 70% of all I/O goes through prepared
statements...the reason to do this was to reduce statement turnaround
latency, which is the main driving performance factor in COBOL
applications.

I would be fine with changing the lifetime if an EXECUTE failure did not
abort the current transaction. Then I could simply watch the return
code of the statement execution and prepare the statement on
demand...from my point of view, this would actually be the most elegant
scenario.

Merlin