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


Thu, 24 Jun 2004 16:19:36 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: PREPARE and transactions
On Thu, Jun 24, 2004 at 04:19:36PM -0400, Merlin Moncure wrote:
 
> 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).
 
Sounds like a challenging (is that word still fashionable?) job.  Now if
only you weren't dealing with legacy applications, this would be a natural
for stored procedures I guess.  Well okay, maybe you could define stored
procedures on demand but then who would clear them up afterwards...

What if prepared statement semantics were modeled after those of session
variables?  You can change a session variable from within a transaction
and keep the change afterwards, but it does go by transaction rules.

What that leaves us is something that works _almost_ the way things work
now, so your code would work unchanged in the normal case.  The difference
would be that PREPARE would roll back like any other statement.

Taking this one step further, if compatibility with current semantics is
important, I could imagine adding a hack-I admit it's not pretty--that
keeps the statement allocated despite a rollback, but sets a "soft" bit.  
The backend could silently accept identical redefinitions of prepared
statements that have the "soft" bit set.

I think that would be the most compatible way, and probably the easiest 
as well, to turn PREPARE into a regular statement:

1. Add a "soft" bit to prepared-statement plans
2. Add rollback bookkeeping for prepared statements, which sets the bit
3. Accept identical re-preparations of "soft" statements, clearing the bit

Deallocation, lookup, execution etc. would not need to change.  There would
still be the risk of "leaking" prepared statements, but that is a problem
of the current semantics anyway.


Jeroen


Fri, 25 Jun 2004 15:25:58 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: PREPARE and transactions
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> 1. Add a "soft" bit to prepared-statement plans
> 2. Add rollback bookkeeping for prepared statements, which sets the bit
> 3. Accept identical re-preparations of "soft" statements, clearing the bit

That sounds awfully ugly :-(

It occurs to me that a lot of the problem would go away if we allowed
DEALLOCATE of a nonexistent statement to not be an error (seems like
a NOTICE would be be plenty).  Then you could just unconditionally
DEALLOCATE anything you were about to PREPARE, if you weren't entirely
sure whether it already existed.

			regards, tom lane

Fri, 25 Jun 2004 10:02:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PREPARE and transactions 
On Fri, Jun 25, 2004 at 10:02:29AM -0400, Tom Lane wrote:
 
> It occurs to me that a lot of the problem would go away if we allowed
> DEALLOCATE of a nonexistent statement to not be an error (seems like
> a NOTICE would be be plenty).  Then you could just unconditionally
> DEALLOCATE anything you were about to PREPARE, if you weren't entirely
> sure whether it already existed.

That would be an improvement anyway, I think--especially if the backend
could keep deallocated plans around a little longer in case they got
re-prepared soon after.  That way the client can ensure not only that the
statement doesn't exist, but also that it _does_ exist, without incurring
prohibitive cost.  And without going through an "if" construct too.

OTOH the problem then remains that we've got semantically significant work
escaping from transactions, but in all other ways being presented as a
regular bracketed operation.  To me it's a bit like a C function returning
a pointer to one of its own stack variables!


Jeroen


Fri, 25 Jun 2004 16:20:53 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: PREPARE and transactions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
  
> It occurs to me that a lot of the problem would go away if we allowed
> DEALLOCATE of a nonexistent statement to not be an error (seems like
> a NOTICE would be be plenty).  Then you could just unconditionally
> DEALLOCATE anything you were about to PREPARE, if you weren't entirely
> sure whether it already existed.
  
This might quell some of the complaints, but I am still looking for a
good example of a case where there is a real problem with the current
system. If you're calling a prepared statement, then you must claim
the responsibility for having created it. And tracking which ones you
have already created is simple in your application, regardless of whether
you are the main application or just middleware. If you create it, you
track it. If an error occurs, you can safely re-use that name. The
error should never occur due to a invalid statement name.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200406252215
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFA3ODAvJuQZxSWSsgRAun9AKCAy13RU4mJ14J9bihiPVm15kvitACghTKv
GgSrqeg9MRROXEwP+AuLSqM=
=Tq9h
-----END PGP SIGNATURE-----



Sat, 26 Jun 2004 02:32:43 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: PREPARE and transactions 