Re: PREPARE and transactions - Mailing list pgsql-hackers

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