Skip site navigation (1) Skip section navigation (2)

Re: PREPARE and transactions

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Jeroen T(dot) Vermeulen" <jtv(at)xs4all(dot)nl>
Cc: <pgsql-hackers(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Alvaro Herrera" <alvherre(at)dcc(dot)uchile(dot)cl>
Subject: Re: PREPARE and transactions
Date: 2004-06-24 20:19:36
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AE83@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-hackers by date

Next:From: Thomas HallgrenDate: 2004-06-24 20:30:24
Subject: Re: warning missing
Previous:From: Cyril VELTERDate: 2004-06-24 19:53:25
Subject: [Re] Re: [Re] Re: PREPARE and transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group