Thread: PREPARE and transactions

We were discussing prepared statement support for libpqxx just now (Bruce,
Peter Eisentraut & myself are manning the postgres booth at LinuxTag 2004
in Karlsruhe, Germany), when we ran into a problem that came up two months
ago.  That discussion follows:

Post by Alvaro Herrera:
> Hackers,
> 
> Is this expected?  If so, why?  I'd expect the prepared stmt to be
> deallocated.
> 
> alvherre=# begin;
> BEGIN
> alvherre=# prepare tres as select 3;
> PREPARE
> alvherre=# rollback;
> ROLLBACK
> alvherre=# execute tres;
> ?column? 
> ----------
> 3
> (1 fila)

Followup by Tom Lane:
> prepare.c probably should have provisions for rolling back its state to
> the start of a failed transaction ... but it doesn't.
> 
> Before jumping into doing that, though, I'd want to have some
> discussions about the implications for the V3 protocol's notion of
> prepared statements.  The protocol spec does not say anything that
> would suggest that prepared statements are lost on transaction rollback,
> and offhand it seems like they shouldn't be because the protocol is
> lower-level than 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]
You could say that the DEALLOCATE in the first transaction should have
been outside the transaction, i.e. after the ABORT.  But that would mean
that the client is expected to roll back, manually, individual changes
made in an aborted transaction.  If that's what we expect from the client,
what's the point in having transactions in the first place?

Lots of variations of the scenario spring to mind.  Imagine the second
transaction were not a transaction at all: the second PREPARE would fail,
and the EXECUTE may go execute the wrong statement.

A partial fix would be to allow identical redefinitions of a prepared
statement, optionally with reference counting to determine when it should
be deallocated.  But instances of the same transaction may want to include
a pseudo-constant in the fixed part of the query text that changes between
instances of the transaction.

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.
If it turns out that this wastes a lot of opportunities for reuse, the
prepared plans can always be cached across definitions.


Jeroen

> 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
previouserror] 
ABORT
BEGIN BEGIN   PREPARE foo AS ...  [fails: foo is already defined!] COMMIT/ROLLBACK EXECUTE foo        [will now always
runif 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)    BEGINPREPARE 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)    BEGINPREPARE 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

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

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

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

"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

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

Jeroen T. Vermeulen wrote:

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

The current JDBC driver uses PREPARE/EXECUTE to prepare arbitrary 
queries, requested either directly from the application or via a 
middleware layer. For queries where use of PREPARE/EXECUTE is requested, 
the driver sends a PREPARE/EXECUTE pair on query execution. If the 
PREPARE succeeds, subsequent query executions for the same query send 
only the EXECUTE.

This might all happen either inside or outside a transaction -- the 
mechanics of transaction demarcation are done by the driver, but the 
transaction model used is up to the application and is effectively 
invisible to the driver.

The set of PREPAREd queries is per-connection state, so whatever 
connection pooling etc. logic runs on top of the driver isn't an issue. 
The driver currently stores that state in the statement objects 
allocated by the application -- which are also tied to a particular 
connection -- but there's no reason why the driver couldn't, for 
example, maintain a cache of prepared statements per connection and 
match that against newly requested queries.

If PREPAREd statements did DEALLOCATE on transaction rollback, the 
driver would have to track the set of statements that were first 
PREPAREd in the current transaction so it can fix the state on the 
driver side if the transaction rolls back. This is a lot of extra 
complexity for no benefit I can see. And it'd get pretty nasty if nested 
transactions were involved..

It's all somewhat moot for the JDBC driver as it's moving to using 
protocol-level Parse/Bind messages instead of PREPARE/EXECUTE statements 
anyway. That said, I would be very unhappy if Parse suddenly became 
transactional to match the behaviour of PREPARE.

-O

Oliver wrote:
> If PREPAREd statements did DEALLOCATE on transaction rollback, the
> driver would have to track the set of statements that were first
> PREPAREd in the current transaction so it can fix the state on the
> driver side if the transaction rolls back. This is a lot of extra
> complexity for no benefit I can see. And it'd get pretty nasty if
nested
> transactions were involved..
>
> It's all somewhat moot for the JDBC driver as it's moving to using
> protocol-level Parse/Bind messages instead of PREPARE/EXECUTE
statements
> anyway. That said, I would be very unhappy if Parse suddenly became
> transactional to match the behaviour of PREPARE.

That is precisely my situation.  The more I think about it, granting
prepared statements transactional lifetime would force me to stop using
them, period.  There really is no reasonable way of using transactions
to protect against this that solves the general case.  Not having
parse/bind to fall back on would be a disaster...

Even if I could end up using parse/bind it would be nice to have a
little time to get ready for this.  I would humbly request that the
current behavior be deprecated for one or more released versions.

Merlin

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

> > 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.
>
> BEGIN;
>     ... do something ... ;
>     SUBBEGIN;
>         EXECUTE ...;
>         -- if it fails:
>         -- SUBABORT;
>         -- PREPARE ...;
>         -- SUBBEGIN;
>         -- EXECUTE ...;
>         -- can continue as if nothing happened
>     SUBCOMMIT;
> COMMIT;

This is not an option: this requires that every EXECUTE be wrapped with
a subtransaction.  This is a performance killer because it (at minimum)
triples my statement turnaround latency.

With the current behavior, you can guarantee certain behaviors by
wrapping PREPARE/DEALLOCATE... and I was eagerly anticipating your work
to do this.  I would much rather do that than be required to wrap every
single EXECUTE (recall that most of my I/O goes through prepared
statements).

Merlin

On Sat, Jun 26, 2004 at 09:12:33AM -0400, Merlin Moncure wrote:

> > BEGIN;
> >     ... do something ... ;
> >     SUBBEGIN;
> >         EXECUTE ...;
> >         -- if it fails:
> >         -- SUBABORT;
> >         -- PREPARE ...;
> >         -- SUBBEGIN;
> >         -- EXECUTE ...;
> >         -- can continue as if nothing happened
> >     SUBCOMMIT;
> > COMMIT;
> 
> This is not an option: this requires that every EXECUTE be wrapped with
> a subtransaction.  This is a performance killer because it (at minimum)
> triples my statement turnaround latency.

Ah, good point.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

Sorry for the delay, life tends to get complicated if you leave it
alone for a few days...

I see how making PREPARE obey rollbacks would be inconvenient for some
existing code, but frankly I'm getting a bit worried about the "why should
I care whether what I do is committed or not?" argument.  I guess one could
say that about lots of statements: "I don't really want this to be subject
to the transaction but it happens convenient for me to write it inside the
transaction, and then I have this problem that it's affected by rollbacks."
If it's that important, come up with a generic "session-not-transaction"
syntax to temporarily escape bracketing.

I'll wave my hands a bit here and use the term "middleware" for drivers,
front-end libraries, proxies, in-application abstraction layers, anything
that sits between the business logic (is that term still fashionable?)
firing queries and the backend handling them.

So far, as I believe Tom has pointed out, I've assumed regular code that
knows whether it's in a transaction, or where transactions begin and end, 
or what else the program is doing with the same session.  The arguments for
the nontransactional behaviour have been about code where this isn't the
case, but that does have exclusive control of prepared statements.

Frankly I think that's a strange point of view, since transactions are one
of the pillars of database management and elementary to statement semantics,
whereas prepared statements are a recently added optimization feature.

In this message I'll give examples of how the current behaviour may affect
other middleware that doesn't use prepared statements, but may have to deal
with applications that do.

So let's assume for a change that the middleware has at least some
knowledge or perhaps even control over transactionality, but doesn't know
about prepared statements (perhaps because it predates 7.4):

(*) The middleware may deal with transient errors, such as some deadlocks,
by rerunning the transaction.  Now if a PREPARE (and no matching DEALLOCATE)
preceded the point of failure, the retry would break under the
nontransactional semantics--just because the PREPARE escaped the rollback.

You can work around this by silently accepting redefinitions of prepared
statements, but a redefinition may also be a true honest bug.  Only
accepting identical redefinitions will not work, because a redefinition
during retry may have a slightly different body than the original definition
during the first attempt.  Another workaround as we've seen is to re-PREPARE
in a nested transaction, which besides offsetting any performance gain may
mean that you're blithely executing the wrong version of the statement!

(*) Transaction code may start out with a PREPARE statement, use the
prepared statement a few times, and then DEALLOCATE it at the very end.
This seems a clean way to work, and an attempt not to affect session state.
But now, paradoxically, there will be unintended residue of the transaction
only if the transaction _fails_.  This may break a future instance of the
transaction, which may be why the DEALLOCATE was there in the first place.

(*) Middleware may support some form of connection pooling, or other
reuse of connections.  The only difference between a reused connection
and a fresh one to the same database that I can think of right now are (i)
session variables and (ii) prepared statements.  So based on the 7.3 state
of things, the middleware might assume that a connection was reusable if (a)
no statements affecting session variables were issued; (b) any changes in
session variables are OK; or (c) no transaction affecting session variables
was committed.

Prepared statements can break each of these options--most notably, they
would break the even more diligent assumption that a session is clean and
unspoiled as long as any transactions (whether implicit or explicit) entered
inside it have been rolled back.

(*) Middleware may want to restore broken connections.  It would have to
restore any prepared statements from the old connection (quite possibly
unnecessarily) in addition to session variables.

Worse, the semantics for the two kinds of session state are different!
Middleware that tries to maintain session state but doesn't keep track of
rollbacks is really already broken when it comes to session variables.
The fix for that and a change to transactional PREPARE require the exact
same mechanism.


Now, two more issues for middleware that does prepare statements:

(*) What if preparing a statement fails?  Could be that you've just broken
the transaction at a point where the application didn't expect it, or in a
way it didn't expect.

(*) What if your prepared statement interferes with one prepared by the
application?

Sure, all of these could be worked around; there's a lot of "don't do that
then" in there--which IMHO cuts both ways.  And of course some of these
are simply real-life examples that I need to try and deal with elegantly if
behaviour is to stay nontransactional.  Just don't tell me that making
PREPARE respect rollbacks would break compatibility, or that it's possible
to write code that doesn't play well with transaction semantics, or that
it's inconvenient, because there are counterexamples for each.  And in case
of doubt, why not go with some form of transactional behaviour?

I should add here that session variables used to escape transaction
bracketing as well--but that was fixed some time ago.  Why are session
variables so different from prepared statements?  At the very least, it
would be nice for middleware to deal with one form of session state, not
"the kind you manipulate with regular statements" and "the kind that
ignores transaction bracketing except that it happens to be atomic also, and
that manipulations are still rejected inside transactions that are already
in failure mode."


Jeroen

Jeroen T. Vermeulen wrote:

> If it's that important, come up with a generic "session-not-transaction"
> syntax to temporarily escape bracketing.

Do you have a proposal for this? It seems to me that if your argument is 
that "if you want the old behaviour, you could add this extension" means 
that you need to provide the extension before changing the behaviour. 
Otherwise you're just exchanging one set of disgruntled users for another.

> Now, two more issues for middleware that does prepare statements:

> (*) What if preparing a statement fails?  Could be that you've just broken
> the transaction at a point where the application didn't expect it, or in a
> way it didn't expect.

The JDBC driver, at least, is careful to only issue a PREPARE at the 
same point the underlying query would have been run anyway, so there's 
no issue there.

> (*) What if your prepared statement interferes with one prepared by the
> application?

Name your autogenerated statement names better. Document the names that 
applications shouldn't use as part of your middleware documentation, or 
don't support applications using PREPARE when the middleware is too.

> Just don't tell me that making
> PREPARE respect rollbacks would break compatibility

Why not? It's true.

> I should add here that session variables used to escape transaction
> bracketing as well--but that was fixed some time ago.  Why are session
> variables so different from prepared statements?

Prepared queries do not change the semantics of queries. Some session 
variables do (e.g. DateStyle).

It's actually quite annoying -- speaking from the client side -- for the 
state of some of the session variables to be affected by transaction 
boundaries, namely those that are really to do with the protocol-level 
stream's state, e.g. client_encoding. You really don't want those 
changing back on you, and you want the ability to change them at 
arbitrary points in the connection -- even if you're in a failed 
transaction. Ideally I'd like to see a way where all of these 
connection-specific settings that are to do with the client's 
preferences, not the server's query execution semantics, can be changed 
immediately regardless of transaction state -- because they really have 
nothing to do with the global state of the database, they are already 
isolated from the settings on other connections by their very nature, 
and they do not affect the semantics of the queries being executed. The 
only thing that making them transactional gives you is atomicity, and 
since they affect the protocol stream at the point where they change, 
you've actually bought extra work by allowing them to be rolled back.

But I think the tide is against me on this one :)

> At the very least, it
> would be nice for middleware to deal with one form of session state, not
> "the kind you manipulate with regular statements" and "the kind that
> ignores transaction bracketing except that it happens to be atomic also, and
> that manipulations are still rejected inside transactions that are already
> in failure mode."

This is only true for middleware that is not dealing with PREPARE 
itself, but wants to be PREPARE-aware. The exact opposite is true for 
middleware that uses PREPARE itself, as illustrated by my JDBC example 
earlier.

Also: what about the V3 protocol's support for named statements?

V3-protocol named statements are very much nontransactional 
protocol-level connection state at the moment, and are effectively a 
more flexible form of PREPARE (they even map to the same namespace, I 
believe). If you want to make PREPARE transactional, do those messages 
also change? If you want to keep the messages nontransactional (as I 
think they should be), why don't the arguments for doing that also apply 
to PREPARE?

-O

On Fri, Jul 02, 2004 at 12:30:07PM +1200, Oliver Jowett wrote:
> >If it's that important, come up with a generic "session-not-transaction"
> >syntax to temporarily escape bracketing.
> 
> Do you have a proposal for this? It seems to me that if your argument is 
> that "if you want the old behaviour, you could add this extension" means 
> that you need to provide the extension before changing the behaviour. 
> Otherwise you're just exchanging one set of disgruntled users for another.
I was thinking of some "escape" keyword to say "I don't want this to be
part of the current transaction; execute it as if directly in the
surrounding session context."  That would at least make it obvious what's
going on.  Wouldn't take any extra semantic work, since one could just
restrict the statements that can go with this syntax--to just PREPARE and
DEALLOCATE for now.  I guess it should also escape auto-abort of
transactions, which has the advantage that you can still DEALLOCATE inside
a transaction.


> >Just don't tell me that making
> >PREPARE respect rollbacks would break compatibility
> 
> Why not? It's true.
So many things are true.  But _not_ doing so also breaks compatibility,
so like I said, there are counterexamples.


> >I should add here that session variables used to escape transaction
> >bracketing as well--but that was fixed some time ago.  Why are session
> >variables so different from prepared statements?
> 
> Prepared queries do not change the semantics of queries. Some session 
> variables do (e.g. DateStyle).
Prepared queries _do_ change semantics of subsequent EXECUTE and PREPARE 
statements--cause them to fail where they succeeded before; cause them
to succeed where they failed before; or make an EXECUTE do something not
necessarily related to what it did before.  It just doesn't affect you
if you're totally aware of prepared statements throughout your path to
the backend.

I'm coming from the other side, where transactions are a primary semantic
concern but not everybody in the stack is aware of prepared statements,
or at least not in a coordinated way.  In any case, I think the
pattern-matching approach I proposed earlier solves the problem for all
of us, and gives us more besides.  Any comments on that?


> It's actually quite annoying -- speaking from the client side -- for the 
> state of some of the session variables to be affected by transaction 
> boundaries, namely those that are really to do with the protocol-level 
> stream's state, e.g. client_encoding. You really don't want those 
> changing back on you, and you want the ability to change them at 
> arbitrary points in the connection -- even if you're in a failed 
> transaction.

I'm not entirely sure.  What if setting a session variable triggers a
failure?  I'm thinking along the lines of "oops, this data isn't valid
utf-8 after all; never mind that change in encoding."  If you don't want
to have transactional behaviour, back we go to an escape syntax like I
suggested.  But I think transactionality ought to be the default like it
is for everything else; ACID and correctness are a lot more important than
a modest amount of convenience in middleware code.  And last but not least,
it's much easier to reason about a cleanly transactional system than about
one with holes and exceptions in it.  That last point is much more
important to me than programming convenience.  Remember that I too have had
to program around rollbacks to support session variables properly; I'll
gladly pay the price for a pure transactional model.


> Also: what about the V3 protocol's support for named statements?

Haven't looked at them (I work on top of libpq), but you make it sound
like the whole PREPARE mess is just a special case of a problem with named
statements.  Like I said before, the prepared-statements problem wouldn't
be here if only prepared statements were anonymous, or at least their names
weren't used in invocation.  It's not the prepared plan that breaks
transactionality; it's the name.

So again, I'd like to hear any comments about my pattern-matching
proposal.  Is there any problem here that it would not solve?  Does anyone
see a problem in implementing them?


Jeroen

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> So again, I'd like to hear any comments about my pattern-matching
> proposal.  Is there any problem here that it would not solve?
> Does anyone see a problem in implementing them?
Quoting. Extra parsing work.
Specifically, your proposal (if I read it correctly) would require me
to send in the full SQL statement every time, thus negating a major
benefit of prepared statements in that I do not have to quote the
values myself, but can let the server handle it. It would also be a lot
of work for the server to parse the statement and find a "match".
Maybe enough work to make the "solution" worse than the cure.
If, on the other hand, you are suggesting sending the statement and
parameters separately, that would be slightly better, but (like above)
it would be quite a bit of extra traffic to send the whole query and
variables for every single execute.
Finally, I am still not convinced there is a problem at all. While the
current behavior is a bit quirky, I think it does work out overall.
A prepared statement will throw an error on two conditions:
1) The name is already in use.
This is trivial to solve by the client, so it should never arise.
(with the caveat that middleware should clearly document its naming
scheme, and request and/or enforce that the client not creating
conflicting names). So we really should remove this from the debate.
2) The statement is invalid.
This one is very common, and it /should/ throw an error. With the fact
that statements cannot be shared across connections, and the handy
PQtransactionStatus function, the client should easily be able to
handle any situation that arises smoothly.
Having the statement exist even on rollback is a little harder to
accept, but I have yet to see a better alternative. Prepare statements
already "break the rules" anyway, by the fact that a commit() after a
prepare does not make them available to any other people viewing
the database.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200407021729
-----BEGIN PGP SIGNATURE-----
iD8DBQFA5dlnvJuQZxSWSsgRApVZAKDl4JznBEf/PBZ8NxBPZf7GdapFsACfc4ro
sIoFwz9mdSxpRvET2WgQHog=
=pIFV
-----END PGP SIGNATURE-----

On Fri, Jul 02, 2004 at 09:51:38PM -0000, Greg Sabino Mullane wrote: 
> Specifically, your proposal (if I read it correctly) would require me
> to send in the full SQL statement every time, thus negating a major

Well, like I said, it wouldn't actually _require_ this; it would just
allow transactional semantics for the current, explicit syntax without
the actual optimization being rolled back.

A logical consequence of the implementation, I think, would be that
you could re-PREPARE a statement when in doubt, without paying the
parsing and planning cost again.


> benefit of prepared statements in that I do not have to quote the
> values myself, but can let the server handle it. It would also be a lot
> of work for the server to parse the statement and find a "match".
> Maybe enough work to make the "solution" worse than the cure.

The algorithm as I've got it worked out so far doesn't look very costly,
unless you have lots and lots of highly similar prepared statements, or
queries so long that their sheer text size becomes the problem.  There's
no actual extra parsing involved, as far as I can see, just pattern
matching and the extraction of the variables.

As you say, however, quoting of parameters remains...


> Finally, I am still not convinced there is a problem at all. While the
> current behavior is a bit quirky, I think it does work out overall.
> A prepared statement will throw an error on two conditions:
>  
> 1) The name is already in use.

Presumably you're taking about a prepare statement here, not a prepared
statement.

> This is trivial to solve by the client, so it should never arise.
> (with the caveat that middleware should clearly document its naming
> scheme, and request and/or enforce that the client not creating
> conflicting names). So we really should remove this from the debate.

In other words, it's only trivial to solve by the client if there is no
possible conflict over where and how the statement got prepared.  You
say it's trivial, but you're also assuming that the work to ensure this
has already been done.  Most problems become trivial that way!


> 2) The statement is invalid.
> This one is very common, and it /should/ throw an error. With the fact
> that statements cannot be shared across connections, and the handy
> PQtransactionStatus function, the client should easily be able to
> handle any situation that arises smoothly.

Of course you shouldn't forget this one:

3) The PREPARE is executed inside a transaction that has already run
into an error.


> Having the statement exist even on rollback is a little harder to
> accept, but I have yet to see a better alternative. Prepare statements
> already "break the rules" anyway, by the fact that a commit() after a
> prepare does not make them available to any other people viewing
> the database.

But that's no different with session variables.  They're transactional,
and they affect session state only.  There is a place reserved for session
state as well as transaction state and database state.  No news there.  What
bugs me is that PREPARE introduces another kind of session state into the
mix, one that doesn't allow you to get out of a fix by aborting the
transaction and continuing the session.


Jeroen

Jeroen T. Vermeulen wrote:

>>>Just don't tell me that making
>>>PREPARE respect rollbacks would break compatibility
>>
>>Why not? It's true.
> 
>  
> So many things are true.  But _not_ doing so also breaks compatibility,
> so like I said, there are counterexamples.

This is nonsense. Not changing the current behaviour cannot break 
compatibility, almost by definition.

>>Also: what about the V3 protocol's support for named statements?
> 
> 
> Haven't looked at them (I work on top of libpq),

Please do take a look. The V3 protocol treats the set of named 
statements as part of the connection state, not as anything at the SQL 
statement level. There are also named portals to deal with if your issue 
is that things shouldn't be named.

> So again, I'd like to hear any comments about my pattern-matching
> proposal.  Is there any problem here that it would not solve?  Does anyone
> see a problem in implementing them?

The client has query-lifetime and query-reuse information that the 
server does not have and can't obtain via simple query matching. Also, 
clients need per-query control over use of PREPARE: prepared queries can 
run slower as they must use a more general query plan. I don't see how 
you overcome either of these if the server hides the mechanics of which 
query plans are preserved.

You could implement the pattern-matching logic as a passthrough layer in 
front of the server -- perhaps in something like pgpool? -- and 
translate to PREPARE based on patterns. Then your application can remain 
unaware of the translation to PREPARE for the most part, the only issue 
being name collision which in practice is simple to work around. But I 
don't see why you want this in the main server at all -- it's really a 
bandaid for applications that don't want to precisely control the 
prepared-statement behaviour themselves.

-O

On Sat, Jul 03, 2004 at 11:17:18AM +1200, Oliver Jowett wrote:

> >So many things are true.  But _not_ doing so also breaks compatibility,
> >so like I said, there are counterexamples.
> 
> This is nonsense. Not changing the current behaviour cannot break 
> compatibility, almost by definition.
Almost.  But prepared statements can break compatibility with code not
aware of their existence yet--and in some cases, this does not happen if
they behave transactionally.  It may not be a big deal, but I'm not
convinced that the effort of supporting rollbacks in middleware is such
a big waste of time either.

> Please do take a look. The V3 protocol treats the set of named 
> statements as part of the connection state, not as anything at the SQL 
> statement level. There are also named portals to deal with if your issue 
> is that things shouldn't be named.
But neither of these pose as SQL statements.  It's the SQL session that
I'm really worried about.


> The client has query-lifetime and query-reuse information that the 
> server does not have and can't obtain via simple query matching. Also, 
> clients need per-query control over use of PREPARE: prepared queries can 
> run slower as they must use a more general query plan. I don't see how 
> you overcome either of these if the server hides the mechanics of which 
> query plans are preserved.
The converse is also true: a dynamic scheme may do better than a static
one.  This often happens.  We may even want to detect reusability on the
fly; that could be based on the same mechanism.  And there's that idea of
sharing plans between backends that also comes into play.


> You could implement the pattern-matching logic as a passthrough layer in 
> front of the server -- perhaps in something like pgpool? -- and 
> translate to PREPARE based on patterns. Then your application can remain 
> unaware of the translation to PREPARE for the most part, the only issue 
> being name collision which in practice is simple to work around. But I 
> don't see why you want this in the main server at all -- it's really a 
> bandaid for applications that don't want to precisely control the 
> prepared-statement behaviour themselves.

Don't want to, or perhaps can't.  It may be hard for the application to
deallocate a statement, for instance, because the transaction failed
before it got to the DEALLOCATE and the middleware doesn't make it easy to
go back and fix that.


Jeroen

Jeroen T. Vermeulen wrote:
> On Sat, Jul 03, 2004 at 11:17:18AM +1200, Oliver Jowett wrote:
> 
> 
>>>So many things are true.  But _not_ doing so also breaks compatibility,
>>>so like I said, there are counterexamples.
>>
>>This is nonsense. Not changing the current behaviour cannot break 
>>compatibility, almost by definition.
> 
>  
> Almost.  But prepared statements can break compatibility with code not
> aware of their existence yet--and in some cases, this does not happen if
> they behave transactionally.  It may not be a big deal, but I'm not
> convinced that the effort of supporting rollbacks in middleware is such
> a big waste of time either.

I stand by my original statement: making no change does not break 
compatibility. Please provide an example of PREPARE/EXECUTE use that 
works under 7.3/7.4 but does not work with current 7.5.

>>Please do take a look. The V3 protocol treats the set of named 
>>statements as part of the connection state, not as anything at the SQL 
>>statement level. There are also named portals to deal with if your issue 
>>is that things shouldn't be named.
> 
>  
> But neither of these pose as SQL statements.  It's the SQL session that
> I'm really worried about.

Parse/Bind/Execute interact with PREPARE/EXECUTE -- they share a 
namespace. Quirky as the current behaviour is, it'd be even quirkier if 
PREPARE/EXECUTE had substantially different semantics to Parse/Bind/Execute.

Please do read the V3 protocol spec: 
http://developer.postgresql.org/docs/postgres/protocol.html

-O

On Sat, Jul 03, 2004 at 12:16:50PM +1200, Oliver Jowett wrote:
> I stand by my original statement: making no change does not break 
> compatibility. Please provide an example of PREPARE/EXECUTE use that 
> works under 7.3/7.4 but does not work with current 7.5.

Whether the transaction from "7.3/7.4" to "7.5" (as it stands) breaks
compatibility was never at issue here.  There would be no point: this break
is _between_ 7.3 and 7.4.  Middleware can no longer assume that rolling
back will get it out of the changes it was making at the SQL level, unless
it is in exclusive control of prepared statements.


> Parse/Bind/Execute interact with PREPARE/EXECUTE -- they share a 
> namespace. Quirky as the current behaviour is, it'd be even quirkier if 
> PREPARE/EXECUTE had substantially different semantics to Parse/Bind/Execute.
> 
> Please do read the V3 protocol spec: 
> http://developer.postgresql.org/docs/postgres/protocol.html

Ah, now I see.  This is why some have proposed to change the SQL behaviour
(which is what I'm concerned with) but not the protocol.  That seemed
strange to me before, but it makes more sense now.

I guess the question then becomes: should we see the frontend-backend
protocol as a transport layer underneath, and conceptually separate from,
the SQL session?  Or should the protocol be allowed to shine through in
the way SQL itself is supported, and vice versa, so the two can share code
and concepts?

My point of view in this is the layered one, i.e. I'd like to be able to
"speak SQL" to the server, as I'm doing now through libpq, and ignore how
it gets there--at least as far as the SQL itself is concerned.  Call it a
2.0 vantage point.  From that angle there are no problems with giving the
protocol's bound statements and SQL's prepared statements both different
namespaces and different behaviour.

You seem to be taking the other view, where now that we have the extended
query protocol, it makes no sense to have one behaviour for the protocol
and another for SQL--and since the protocol implementation is not subject
to transactions (except that portals live in transactions?) there is no
point in going with transactional behaviour for a mechanism shared between
both.

Does that more or less describe the underlying controversy?


Jeroen

At 2004-06-24 13:13:42 -0400, tgl@sss.pgh.pa.us wrote:
>
> > 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.

As a client maintainer, I have no particular problem with the status quo
(apparently like Greg and Cyril), but I can appreciate the point made in
Jeroen's initial post in this thread, and I would not object to changing
PREPARE to be transactional while leaving Parse messages alone. Nor do I
have a problem with "PREPARE OR REPLACE".

But for what it's worth, I strongly dislike the later proposal of making
prepared statements anonymous, and pattern matching the statement text,
especially if they reintroduce the need to quote query parameters.

Ugh.

-- ams

Jeroen T. Vermeulen wrote:
> On Sat, Jul 03, 2004 at 12:16:50PM +1200, Oliver Jowett wrote:
>  
> 
>>I stand by my original statement: making no change does not break 
>>compatibility. Please provide an example of PREPARE/EXECUTE use that 
>>works under 7.3/7.4 but does not work with current 7.5.
> 
> 
> Whether the transaction from "7.3/7.4" to "7.5" (as it stands) breaks
> compatibility was never at issue here.  There would be no point: this break
> is _between_ 7.3 and 7.4.

I think you mean "between 7.2 and 7.3".

> Middleware can no longer assume that rolling
> back will get it out of the changes it was making at the SQL level, unless
> it is in exclusive control of prepared statements.

Correctly written middleware could never assume this anyway. PREPARE was 
introduced in 7.3. So was rollback of SET changes on transaction abort. 
So a pre-7.3 middleware layer can't assume that ROLLBACK rolls back all 
connection state changes, and a 7.3+ middleware layer has already had to 
deal with PREPARE not rolling back for two release cycles.

> My point of view in this is the layered one, i.e. I'd like to be able to
> "speak SQL" to the server, as I'm doing now through libpq, and ignore how
> it gets there--at least as far as the SQL itself is concerned.  Call it a
> 2.0 vantage point.  From that angle there are no problems with giving the
> protocol's bound statements and SQL's prepared statements both different
> namespaces and different behaviour.
> 
> You seem to be taking the other view, where now that we have the extended
> query protocol, it makes no sense to have one behaviour for the protocol
> and another for SQL--and since the protocol implementation is not subject
> to transactions (except that portals live in transactions?) there is no
> point in going with transactional behaviour for a mechanism shared between
> both.

Yes. I see PREPARE/EXECUTE as a SQL-statement-level, connection-local 
way of getting control over reuse of plans that doesn't otherwise affect 
the semantics of the query. With the V3 protocol you can also do it at 
the protocol level rather than the statement level, but it's still the 
same underlying operation; why should it behave differently?

I'm not too worried, to tell the truth -- the JDBC driver has already 
moved to using the protocol-level approach, and so long as that doesn't 
change I'm happy. It just seems to me that the changes you're advocating 
are going to break more clients than they help (e.g. it would have 
required nontrivial work on the JDBC driver as of a month ago to deal 
with the change), and for no convincing reason.

-O

"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> I guess the question then becomes: should we see the frontend-backend
> protocol as a transport layer underneath, and conceptually separate from,
> the SQL session?  Or should the protocol be allowed to shine through in
> the way SQL itself is supported, and vice versa, so the two can share code
> and concepts?

One point here is that in the present design, statements prepared via
SQL-level PREPARE are intentionally interchangeable with statements
prepared via protocol-level Prepare messages.  You can create a
statement either way and then use it at either level.  Perhaps this was
a bad idea.  I think that if we decide to make the semantics of PREPARE
and Prepare different, it would be wise to separate the statement
namespaces so that PREPARE'd and Prepare'd statements are totally
independent.

It's late at night and I'm too lazy to go look right now, but I think
that the same is true of SQL-level cursors and protocol-level portals.
        regards, tom lane

At 2004-07-03 08:20:17 +0530, ams@oryx.com wrote:
>
> I would not object to changing PREPARE to be transactional while
> leaving Parse messages alone.

That is to say, it wouldn't cause any problems for me. But since it does
seem to be a nuisance for Oliver and Merlin (among others), I agree with
Greg that I don't see much of a need to change anything.

-- ams

On Sat, Jul 03, 2004 at 08:20:17AM +0530, Abhijit Menon-Sen wrote:
> At 2004-06-24 13:13:42 -0400, tgl@sss.pgh.pa.us wrote:
> >
> > > 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.
> 
> As a client maintainer, I have no particular problem with the status quo
> (apparently like Greg and Cyril), but I can appreciate the point made in
> Jeroen's initial post in this thread, and I would not object to changing
> PREPARE to be transactional while leaving Parse messages alone. Nor do I
> have a problem with "PREPARE OR REPLACE".

Do you use libpq on your client, or the be-fe protocol directly?

AFAIK there is no way to use Parse with libpq calls ... I think this
limits it's applicability as a lot of people uses libpq
(unsurprisingly).

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

On Sat, Jul 03, 2004 at 08:20:17AM +0530, Abhijit Menon-Sen wrote:
> But for what it's worth, I strongly dislike the later proposal of making
> prepared statements anonymous, and pattern matching the statement text,
> especially if they reintroduce the need to quote query parameters.

Only in cases where you aren't sure your prepared statement name will be
visible.  And even in those cases, wouldn't PQexecParams() do the job?
That'd be like PQexecPrepared() except the query text becomes a kind of
replacement for the query name.


Jeroen

On Sat, Jul 03, 2004 at 02:59:58PM +1200, Oliver Jowett wrote:
> 
> I think you mean "between 7.2 and 7.3".
Ah, OK.  I thought PREPARE had been added in 7.4.  My apologies.


> Yes. I see PREPARE/EXECUTE as a SQL-statement-level, connection-local 
> way of getting control over reuse of plans that doesn't otherwise affect 
> the semantics of the query. With the V3 protocol you can also do it at 
> the protocol level rather than the statement level, but it's still the 
> same underlying operation; why should it behave differently?
The real basis of this pattern-matching idea I'm proposing is that the
naming issue (which is semantic) and the reuse of plans (which is an
optimization) could be handled separately.  The next question I think is
whether the two should really share a namespace, and whether the two types
of names should have the same behaviour.  The underlying mechanism would 
remain the same, but once these issues have been decoupled I think SQL
names and protocol-level names are easy to see as different things.


> I'm not too worried, to tell the truth -- the JDBC driver has already 
> moved to using the protocol-level approach, and so long as that doesn't 
> change I'm happy. It just seems to me that the changes you're advocating 
> are going to break more clients than they help (e.g. it would have 
> required nontrivial work on the JDBC driver as of a month ago to deal 
> with the change), and for no convincing reason.

Maybe.  OTOH I find it simply disturbing (as a matter of aesthetics, you
could say) that the application can be messing around with the protocol
underneath the middleware it's supposed to live on top of--the middleware
that should expect to be in control of the backend below the SQL level.


Jeroen

Jeroen T. Vermeulen wrote:

> Maybe.  OTOH I find it simply disturbing (as a matter of aesthetics, you
> could say) that the application can be messing around with the protocol
> underneath the middleware it's supposed to live on top of--the middleware
> that should expect to be in control of the backend below the SQL level.

Consider SET client_encoding then..

-O

On Sun, Jul 04, 2004 at 02:33:53PM +1200, Oliver Jowett wrote:
> Consider SET client_encoding then..

Does that really affect most middleware?  In my situation for instance,
what goes through the connection either way is "just bytes" to the
middleware.  Its interpretation is a client matter.  So to me this is a
client application thing (though of course lower-level than normal SQL)
and it's also fully transactional.


Jeroen

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Jeroen T. Vermeulen wrote:
> There's no actual extra parsing involved, as far as I can see, just
> pattern matching and the extraction of the variables.
That sounds like "parsing" to me. :)
[client handling the tracking of PREPARE names]
> In other words, it's only trivial to solve by the client if there
> is no possible conflict over where and how the statement got prepared.
> You say it's trivial, but you're also assuming that the work to ensure
> this has already been done.  Most problems become trivial that way!
It's already been done in DBD::Pg. Naming starts at dbdpg_1 and goes to
dbdpg_2, dbdpg_3, etc. The only requirement we ask of the application
using it is that you don't prepare statements yourself named "dbdpg_x".
In most cases, the application does not worry about the naming anyway,
but simply issues an anonymous prepare request through DBIs paradigm of
one statement handle bound to a single SQL statement. DBD::Pg also does
the deallocating itself, and keeps track of the transaction status as well.
Deallocation is merely a courtesy anyway, as we don't reuse the names.
If there are flaws in the above design, I'd like to know about them,
as all of this prepare/execute stuff is rather new and undertested.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200407041434
-----BEGIN PGP SIGNATURE-----
iD8DBQFA6E8uvJuQZxSWSsgRAivsAJwIBtULWbqxIxenRee16iIBtTDvywCfUDNT
pT7cVKHpmq0/Torj+yZkSWA=
=NzIl
-----END PGP SIGNATURE-----

On Sun, Jul 04, 2004 at 06:39:46PM -0000, Greg Sabino Mullane wrote: 
> > There's no actual extra parsing involved, as far as I can see, just
> > pattern matching and the extraction of the variables.
>  
> That sounds like "parsing" to me. :) 
Depends on your definition, I guess.  I would say very limited lexical
analysis, yes, but nothing involving actual structure beyond individual
lexical tokens.


> It's already been done in DBD::Pg. Naming starts at dbdpg_1 and goes to
> dbdpg_2, dbdpg_3, etc. The only requirement we ask of the application
> using it is that you don't prepare statements yourself named "dbdpg_x".
> In most cases, the application does not worry about the naming anyway,
> but simply issues an anonymous prepare request through DBIs paradigm of
> one statement handle bound to a single SQL statement. DBD::Pg also does
> the deallocating itself, and keeps track of the transaction status as well.
> Deallocation is merely a courtesy anyway, as we don't reuse the names.
>  
> If there are flaws in the above design, I'd like to know about them,
> as all of this prepare/execute stuff is rather new and undertested.

Can't think of any, as long as you don't try to manage the connection.


Jeroen

Jeroen T. Vermeulen wrote:
> On Sun, Jul 04, 2004 at 02:33:53PM +1200, Oliver Jowett wrote:
>  
> 
>>Consider SET client_encoding then..
> 
> 
> Does that really affect most middleware?  In my situation for instance,
> what goes through the connection either way is "just bytes" to the
> middleware.  Its interpretation is a client matter.  So to me this is a
> client application thing (though of course lower-level than normal SQL)
> and it's also fully transactional.

It certainly affects the JDBC driver -- the native String representation 
in Java is UTF-16, so the driver transcodes between that and 
client_encoding for parameterized queries and query results involving 
strings.

With the V2 protocol, if the application issues a SET client_encoding, 
suddenly that transcoding breaks without warning.

With the V3 protocol, at least the driver can notice the change (via a 
ParameterStatus message) and complain loudly. It could track the change, 
in theory, but since the choice of client_encoding is an implementation 
detail for the JDBC driver and everything is going to end up as UTF-16 
anyway, there's no real point in allowing it.

So at least from that point of view, client_encoding is very much a 
protocol-level thing. Much as I see PREPARE :)

-O

On Mon, Jul 05, 2004 at 10:21:26AM +1200, Oliver Jowett wrote:
> It certainly affects the JDBC driver -- the native String representation 
> in Java is UTF-16, so the driver transcodes between that and 
> client_encoding for parameterized queries and query results involving 
> strings.
Oops, yeah, I forgot.  So perhaps we should be distinguishing several
layers in a session's state, along the lines of:
SQL session - temp tables, session variables, database contentsInterchange - encoding & representationProtocol - COPY,
bind/execute&c.Connection - socket stuff
 


> So at least from that point of view, client_encoding is very much a 
> protocol-level thing. Much as I see PREPARE :)

The Interchange layer is the ugly stepchild here; it's controlled at the
SQL level but should be handled either by the application or in middleware,
together with the Protocol layer.  The model really seems to assume that it
belongs in the application, which in your case of course is not an option.
If they were placed at the driver level (together with Protocol) then I'd
see how they might as well be nontransactional.  Are there even significant
uses of session variables other than controlling the Interchange layer?

Transactions come into play at the Protocol level, and the way things are
implemented, go all the way up to SQL level.  Only the Connection level is
entirely nontransactional, and the SQL layer to my intuition ought to be
exclusively transactional.  The only current exception to that that springs
to mind is the way PREPARE is implemented.


Jeroen

Jeroen T. Vermeulen wrote:

> So perhaps we should be distinguishing several
> layers in a session's state, along the lines of:
> 
>     SQL session - temp tables, session variables, database contents
>     Interchange - encoding & representation
>     Protocol - COPY, bind/execute &c.
>     Connection - socket stuff

That's not a bad model -- now we get to argue about what goes where ;)

> Transactions come into play at the Protocol level, and the way things are
> implemented, go all the way up to SQL level.  Only the Connection level is
> entirely nontransactional, and the SQL layer to my intuition ought to be
> exclusively transactional.  The only current exception to that that springs
> to mind is the way PREPARE is implemented.

Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is 
particularly fun -- don't most of the arguments for making PREPARE 
transactional also apply to DEALLOCATE? Is it actually feasible to roll 
back a DEALLOCATE?

-O

On Mon, Jul 05, 2004 at 11:44:08PM +1200, Oliver Jowett wrote:

> >    SQL session - temp tables, session variables, database contents
> >    Interchange - encoding & representation
> >    Protocol - COPY, bind/execute &c.
> >    Connection - socket stuff

> >Transactions come into play at the Protocol level, and the way things are
> >implemented, go all the way up to SQL level.  Only the Connection level is
> >entirely nontransactional, and the SQL layer to my intuition ought to be
> >exclusively transactional.  The only current exception to that that springs
> >to mind is the way PREPARE is implemented.
> 
> Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is 

Well, I'd say DEALLOCATE is implicitly lumped in with PREPARE.  But as for
FETCH, are you referring to cursors that live outside transactions (but get
manipulated inside transactions)?  Are those implemented yet and if so, how
does FETCH work there?  There's just been a discussion here about how
nested transactions should not be allowed to FETCH from cursors defined in
a wider scope for precisely this reason: to ensure neat transactional
behaviour.


> particularly fun -- don't most of the arguments for making PREPARE 
> transactional also apply to DEALLOCATE? Is it actually feasible to roll 
> back a DEALLOCATE?

I can see how it gets a bit more complicated when you DEALLOCATE, then
PREPARE a new statement with the same name in the same transaction.  But
nothing that a transaction-local mapping (plus "being deleted" bit) can't
fix, AFAICS.


Jeroen

Jeroen T. Vermeulen wrote:

> Well, I'd say DEALLOCATE is implicitly lumped in with PREPARE.  But as for
> FETCH, are you referring to cursors that live outside transactions (but get
> manipulated inside transactions)?  Are those implemented yet and if so, how
> does FETCH work there? 

I'm thinking WITH HOLD cursors -- they've been around since 7.4. 
7.4/7.5's behaviour leaves the cursor state unchanged by the rollback:

DECLARE foo CURSOR WITH HOLD FOR SELECT * FROM sometable

BEGIN   FETCH FORWARD 10 FROM foo   -- returns rows 1..10
ROLLBACK

BEGIN   FETCH FORWARD 10 FROM foo   -- returns rows 11..20
ROLLBACK

> There's just been a discussion here about how
> nested transactions should not be allowed to FETCH from cursors defined in
> a wider scope for precisely this reason: to ensure neat transactional
> behaviour.

This breaks things like JDBC that want to use cursors to batch access to 
a large resultset. Saying that you can't access resultsets created 
before opening a new subtransaction (or equivalently, before a 
SAVEPOINT) -- but only if the driver has decided to use a cursor behind 
the scenes! -- is a pretty draconian requirement and certainly isn't in 
the JDBC spec anywhere. Iterating through a resultset emitting updates 
is a pretty common model, and you may well want a savepoint just before 
starting on the updates.

I don't like rollback of FETCH for much the same reasons as I don't like 
rollback of PREPARE -- lots more work on the client side. See my mail on 
the other thread. Avoiding changing the behaviour of FETCH in the above 
case is also an argument against it.

-O

On Tue, Jul 06, 2004 at 12:17:50AM +1200, Oliver Jowett wrote:
> 7.4/7.5's behaviour leaves the cursor state unchanged by the rollback:
> 
> DECLARE foo CURSOR WITH HOLD FOR SELECT * FROM sometable
> 
> BEGIN
>    FETCH FORWARD 10 FROM foo   -- returns rows 1..10
> ROLLBACK
> 
> BEGIN
>    FETCH FORWARD 10 FROM foo   -- returns rows 11..20
> ROLLBACK
That makes me wonder why people want to maintain transactionality w.r.t.
nested transactions but not with "outer" ones.  Odd!

I can see the technical problem, of course, although I think it should
respect rollbacks if at all possible without sacrificing significant
performance *in the commit case.*  Verify for failure, but optimize for
success.  Even if the cursor cannot go backwards I'd rather see those rows
buffered and "spliced back" into the cursor's stream on rollback.  Perhaps
the reasoning is that you process the batches returned by the cursor
inside a transaction, and have separate error handling for failed batches.

But then the FETCH should still occur before the transaction as far as I'm
concerned.  You fetch a batch (if it fails, you terminate) and *try* to
process it.


> >There's just been a discussion here about how
> >nested transactions should not be allowed to FETCH from cursors defined in
> >a wider scope for precisely this reason: to ensure neat transactional
> >behaviour.
> 
> This breaks things like JDBC that want to use cursors to batch access to 

This is a restriction on nested transactions, which aren't even in a real
release yet.  I thought you said you can't break compatibility without
changing the code?  ;)


> I don't like rollback of FETCH for much the same reasons as I don't like 
> rollback of PREPARE -- lots more work on the client side. See my mail on 
> the other thread. Avoiding changing the behaviour of FETCH in the above 
> case is also an argument against it.

In the case of FETCH, where does that extra work come from?


Jeroen

Jeroen T. Vermeulen wrote:

> That makes me wonder why people want to maintain transactionality w.r.t.
> nested transactions but not with "outer" ones.  Odd!

Yep.

> But then the FETCH should still occur before the transaction as far as I'm
> concerned.  You fetch a batch (if it fails, you terminate) and *try* to
> process it.

This is a non-starter for JDBC: it has no control over when an 
application decides to access a ResultSet in a way that results in a 
FETCH of new data.

Buffering *all* the ResultSet data client-side isn't an option -- 
cursors are used specifically to handle resultsets that don't fit into 
heap on the client side. And implementing a disk cache or similar a) 
doesn't work if you don't have disk access, b) is bandwidth-intensive 
and c) is really silly -- that's work that belongs on the server side, 
or why bother with implementing cursors at all?!

Invalidating all open resultsets on creation of a savepoint would make 
savepoints useless in many cases, and isn't hinted at in the JDBC spec 
for savepoints so is likely to break many otherwise portable apps.

Having ResultSets spontaneously change position on transaction 
boundaries would cause even more portability problems -- and it goes 
completely against how that API is designed (it's meant to *insulate* 
the application from details like cursors that may be used behind the 
scenes).

>>I don't like rollback of FETCH for much the same reasons as I don't like 
>>rollback of PREPARE -- lots more work on the client side. See my mail on 
>>the other thread. Avoiding changing the behaviour of FETCH in the above 
>>case is also an argument against it.
> 
> 
> In the case of FETCH, where does that extra work come from?

See my other email. The driver will either have to use SCROLL cursors 
and FETCH ABSOLUTE everywhere (which involves an extra Materialize step 
in the plan for nontrivial queries) or track each open cursor's position 
at the start of every active subtransaction so it can restore that 
information on rollback. The driver needs to track where the server 
thinks the cursor is positioned so it can do an appropriate FETCH or 
Execute when the application requests data in a resultset that's not 
currently available on the client side.

Reporting the new cursor positions at the protocol level when rollback 
happens might help but it's still fairly ugly and would need a protocol 
version change.

Also consider that the V3 protocol Execute message is essentially a 
FETCH (you can only do FETCH FORWARD count, but it's otherwise 
equivalent). This is another case of overlap between the SQL level and 
the protocol level and has much of the same problems as we have with 
PREPARE vs. Bind/Execute. The protocol-level portal state shouldn't 
suddenly change on a transaction boundary. I can understand closing 
nonholdable portals when the creating transaction closes (the data 
source just disappeared) but having the portal change *position* would 
be very weird.

-O

On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote:
> This is a non-starter for JDBC: it has no control over when an 
> application decides to access a ResultSet in a way that results in a 
> FETCH of new data.
From what you're telling me, I'm not sure I like JDBC!  Why did they come
up with such a low-level design?  Sounds like little more than a thin ODBC
wrapper plus JVM marshalling...


> Buffering *all* the ResultSet data client-side isn't an option -- 
> cursors are used specifically to handle resultsets that don't fit into 
> heap on the client side. And implementing a disk cache or similar a) 
> doesn't work if you don't have disk access, b) is bandwidth-intensive 
> and c) is really silly -- that's work that belongs on the server side, 
> or why bother with implementing cursors at all?!

But does this type of ResultSet scroll cursors?  Because in that case, it
should be easy to reset the cursor's position at rollback!  Not fast
perhaps, but easy.  Screw fast when you're rolling back, because you'll
have other things to worry about.

Okay, I know, you might not _want_ to reset on rollback.  But it does give
the middleware a lot more freedom to play with connections etc. like we
discussed before.  So personally, if it meant that I had to support
rollbacks, I would think it was a small price to pay for full ACID
guarantees.


> Having ResultSets spontaneously change position on transaction 
> boundaries would cause even more portability problems -- and it goes 
> completely against how that API is designed (it's meant to *insulate* 
> the application from details like cursors that may be used behind the 
> scenes).
Are you saying this is not something you'd be able to hide in the driver?


> Reporting the new cursor positions at the protocol level when rollback 
> happens might help but it's still fairly ugly and would need a protocol 
> version change.
It would be nice IMHO to have a "tell" function for cursors, giving the
enumerated current position of the cursor.  I can fake that by counting
rows, in fact I've already done that, but it's not pretty and it easily
gets confused with the lower isolation levels (which fortunately Postgres
doesn't have).


> Also consider that the V3 protocol Execute message is essentially a 
> FETCH (you can only do FETCH FORWARD count, but it's otherwise 
> equivalent). This is another case of overlap between the SQL level and 
> the protocol level and has much of the same problems as we have with 
> PREPARE vs. Bind/Execute. The protocol-level portal state shouldn't 
> suddenly change on a transaction boundary. I can understand closing 
> nonholdable portals when the creating transaction closes (the data 
> source just disappeared) but having the portal change *position* would 
> be very weird.

You're beginning to convince me that maybe ACID for transactions in
postgres is unsalvageable and we should be thinking about some alternative,
such as ways of finding out whether ACID still applies to the current
transaction, and/or whether the current statement will change that...


Jeroen

On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote:

> Buffering *all* the ResultSet data client-side isn't an option -- 
> cursors are used specifically to handle resultsets that don't fit into 
> heap on the client side. And implementing a disk cache or similar a) 
> doesn't work if you don't have disk access, b) is bandwidth-intensive 
> and c) is really silly -- that's work that belongs on the server side, 
> or why bother with implementing cursors at all?!

Well, the proposal of implementing it like holdable cursors means using
a Materialize node which, if I understand correctly, means taking the
whole result set and storing it on memory (or disk).  So the same
question arises: why bother implementing that at all?  Of course the
answer is that the server definitely _has_ to provide the functionality.

Now, the cursor problem is beyond me ATM -- it needs deep understanding
of the executor code that I do not have and won't be able to develop in
two weeks ... if there's no reasonable solution in sight maybe the best
we can do is revert the whole nested xacts patch (or at least disable
the funcionality) so we have more time to solve this particular problem.

Sadly, AFAICS this is the only major problem with the functionality, so
it would be a pity to throw away all work only for this.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

Alvaro Herrera wrote:

> Well, the proposal of implementing it like holdable cursors means using
> a Materialize node which, if I understand correctly, means taking the
> whole result set and storing it on memory (or disk).  So the same
> question arises: why bother implementing that at all?  Of course the
> answer is that the server definitely _has_ to provide the functionality.

It seems more reasonable to implement this on the server side -- it 
already has the data to hand (not on the other side of a network 
connection) and is much more likely to have memory/disk available.

> Now, the cursor problem is beyond me ATM -- it needs deep understanding
> of the executor code that I do not have and won't be able to develop in
> two weeks ... if there's no reasonable solution in sight maybe the best
> we can do is revert the whole nested xacts patch (or at least disable
> the funcionality) so we have more time to solve this particular problem.
> 
> Sadly, AFAICS this is the only major problem with the functionality, so
> it would be a pity to throw away all work only for this.

Is there an approach that means we can do *something* sane with cursors 
and keep nested transactions? Something like "close all non-hold cursors 
on transaction start"? I think the JDBC driver can pass this restriction 
on to the application if we document it -- "creating a savepoint or 
starting a new subtransaction invalidates all open resultsets" -- as a 
necessary limitation of the current backend's implementation. I do think 
that this will make savepoints useless in many cases, but it's better 
than not having subtransactions at all.

Then maybe better cursor support can be done for 7.6?

-O

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is
> particularly fun -- don't most of the arguments for making PREPARE
> transactional also apply to DEALLOCATE? Is it actually feasible to roll
> back a DEALLOCATE?
That's why PREPARE and DEALLOCATE are so perfect the way they are: outside
of transactions. Although I think Tom mentioned that deallocating a
statement that did not exist should raise a notice and not change the
transaction status, which I would totally agree with.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200407062132
-----BEGIN PGP SIGNATURE-----
iD8DBQFA61K/vJuQZxSWSsgRAi6oAKDruPbDxfk2uDydOAPoFLjJxyeaHACfaT3V
LncDJ2/eFy8RMNLbmcG2Iwo=
=Tin2
-----END PGP SIGNATURE-----

Christopher Kings-Lynne wrote:
> > 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...

For me the OR REPLACE is used for cases like CREATE FUNCTION where you
want to presever the oid of the function.  Does OR REPLACE make sense
for functions?  Should you just drop and ignore the error first?

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

What was the conclusion for PREPARE done inside transactions?

---------------------------------------------------------------------------

Tom Lane wrote:
> "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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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

Were are we on deciding how PREPARE in aborted transactions should behave?

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>  
>  
> > Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is
> > particularly fun -- don't most of the arguments for making PREPARE
> > transactional also apply to DEALLOCATE? Is it actually feasible to roll
> > back a DEALLOCATE?
>  
> That's why PREPARE and DEALLOCATE are so perfect the way they are: outside
> of transactions. Although I think Tom mentioned that deallocating a
> statement that did not exist should raise a notice and not change the
> transaction status, which I would totally agree with.
>  
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200407062132
> -----BEGIN PGP SIGNATURE-----
>  
> iD8DBQFA61K/vJuQZxSWSsgRAi6oAKDruPbDxfk2uDydOAPoFLjJxyeaHACfaT3V
> LncDJ2/eFy8RMNLbmcG2Iwo=
> =Tin2
> -----END PGP SIGNATURE-----
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
[ Decrypting message... End of raw data. ]

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

On Sun, Jul 11, 2004 at 07:23:13PM -0400, Bruce Momjian wrote:
> 
> Were are we on deciding how PREPARE in aborted transactions should behave?

Haven't gotten much further than agreeing that current behaviour is
quirky.  It does not follow that we agree it's bad.  I would say most
of us agree that it may have been a little rash to unify prepared
statements on the fe/be protocol level and those on the SQL level into
the same namespace.

One piece of progress we did make is a layercake model to facilitate
discussion of the fe/be protocol and other interface issues:
SQL session - tables & queries and stuffInterchance - dat a representation, encodings &c.Protocol -
bind/prepare/execute,portals &c.Connection - sockets and such
 

Some observations we can make based on this:
- Transactionality plays at the upper three levels.  Should the upper level  be exclusively transactional?  In other
words,should we have an ACID SQL  implementation?  This issue can in principle be separated from any choices  at the
lowerlevels, but currently prepared statements and cursors cross  the divide.
 
- Session variables see significant use at the Interchange level, yet are  manipulated at the SQL level.  This means
they'retransactional although  some would like to see them work nontransactionally.
 
- To make things easier we've lumped everything between the client socket  and client-side business logic under the
term"middleware."
 
- Some middleware such as language drivers hide only the lowest levels but  leave SQL alone (JDBC), or hide only the
Protocollevel, expose the  Connection level, and leave everything else to further client software  (libpq), and some
hidesthe lower 2 levels, gets involved in the upper  level, but doesn't touch the Interchange level (libpqxx).  This
may influence how easy it is for the middleware to support transactionality  for various features, and where we would
liketo have it and where we  wouldn't.  Having nontransactional behaviour is convenient for middleware  that uses
preparedstatements and is not transaction-aware.
 
- There is also a layercake at the client side.  Middleware's naming  choices for e.g. prepared statements must not
clashwith those of other  layers, and this currently requires specific documentation.  I would  describe that as
anothernamespace problem.
 

Basically the whole problem probably wouldn't be with us if prepared
statements on the SQL level were different from the identically-named
concept in the fe/be protocol.

Jeroen