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)


-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
FOO MANE PADME HUM

Fri, 30 Apr 2004 15:34:46 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Weird prepared stmt behavior
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Is this expected?  If so, why?  I'd expect the prepared stmt to be
> deallocated.

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.

			regards, tom lane

Fri, 30 Apr 2004 21:44:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird prepared stmt behavior 
On Fri, Apr 30, 2004 at 09:44:52PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > Is this expected?  If so, why?  I'd expect the prepared stmt to be
> > deallocated.
> 
> 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.

Right now there is no distinction between a PREPARE prepared statement
and a protocol-level one.  If we want to have the v3proto's statements
behave different from PREPARE's, it's just a matter of adding a new
field into the PreparedStatement.  I can do that and make them behave
different if people think this is how it should be.

I don't really have an opinion on whether protocol-level should behave
different.  What do people think?

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

Sat, 1 May 2004 13:20:47 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Right now there is no distinction between a PREPARE prepared statement
> and a protocol-level one.  If we want to have the v3proto's statements
> behave different from PREPARE's, it's just a matter of adding a new
> field into the PreparedStatement.

It just occurred to me that there are situations where it's arguably
*necessary* to abandon a PreparedStatement at rollback.  Consider

	BEGIN;
	CREATE TABLE foo AS SELECT ... blah blah ... ;
	PREPARE bar(int) AS SELECT * FROM foo WHERE key = $1;
	ROLLBACK;

After the rollback, table foo no longer exists, and it seems like it
would be wise if the dependent prepared statement went away too.
Keeping the prepared statement because it was generated by a protocol
operation rather than a SQL "PREPARE" command would still leave you with
a broken statement.

I'm not sure whether this is a reasonable argument for discarding *all*
prepared statements made within a failed transaction, though.  If we got
off our duffs and tracked dependencies of prepared plans, we could
perhaps detect whether this situation applies or not.

Also, you can easily create the identical failure without any rollback,
just by dropping table foo later.  So maybe we just ought to accept the
notion that prepared statements can be in a "broken" state due to later
changes in the system catalogs.  If we said that then there wouldn't be
any logical inconsistency in treating prepared-statement creation as a
non-transactional operation.

We could imagine that once we add tracking of plan dependencies,
detection of a change that invalidates a prepared statement's plan
would just cause the prepared statement to be marked as "needs
recompilation".  The next attempt to use it would have to re-plan
from source, and could get an error if there is no longer any valid
interpretation of the original source string.  (We'd have to save either
the original source text or the raw grammar output, but I think we
already do.)

			regards, tom lane

Sat, 01 May 2004 17:02:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird prepared stmt behavior 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
                                                                                                                             
                                                                                                                             
> We could imagine that once we add tracking of plan dependencies,
> detection of a change that invalidates a prepared statement's plan
> would just cause the prepared statement to be marked as "needs
> recompilation".  The next attempt to use it would have to re-plan
> from source, and could get an error if there is no longer any valid
> interpretation of the original source string.
                                                                                                                             
I am very uneasy about this. Statements should stay invalidated, else
the prepared statement may no longer even do what was originally
intended when it was first created.
 
> I'm not sure whether this is a reasonable argument for discarding *all*
> prepared statements made within a failed transaction, though.
 
That gets my vote, and seems to make the most sense. If the user does
not want it to get rolled back, let them commit it before going on
to the rest of the actions.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200405011816
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAlCIivJuQZxSWSsgRAun5AKCUWH3mb59c+iuaDMlf2U+pZG9UXACghalD
5E34MgozbiPkIYMn2tvdGFk=
=oenf
-----END PGP SIGNATURE-----



Sat,  1 May 2004 22:16:56 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: Weird prepared stmt behavior 
On Sat, May 01, 2004 at 10:16:56PM -0000, Greg Sabino Mullane wrote:

> > We could imagine that once we add tracking of plan dependencies,
> > detection of a change that invalidates a prepared statement's plan
> > would just cause the prepared statement to be marked as "needs
> > recompilation".  The next attempt to use it would have to re-plan
> > from source, and could get an error if there is no longer any valid
> > interpretation of the original source string.
>
> I am very uneasy about this. Statements should stay invalidated, else
> the prepared statement may no longer even do what was originally
> intended when it was first created.

OTOH, Oliver Jowett said on the JDBC list that the JDBC driver would
like to have a mechanism to non-transactionally create prepared
statements
(http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00000.php)

A possible compromise is what Tom said originally: we could just have
the PREPARE command statements be discarded at rollback, but the Prepare
message's statements should be kept.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."

Mon, 3 May 2004 11:49:53 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior
> On Sat, May 01, 2004 at 10:16:56PM -0000, Greg Sabino Mullane wrote:
>> I am very uneasy about this. Statements should stay invalidated, else
>> the prepared statement may no longer even do what was originally
>> intended when it was first created.

I think Greg's concern is overblown, and would result in an effectively
unusable facility if we did implement it that way.  As an example,
adding an index to a table *should* result in flushing of cached plans
for that table (maybe the query can make good use of the new index),
but people would be quite unhappy if already-prepared statements for the
table just stopped working.  More generally, the flush mechanism will
probably be written in a way that causes plan flushes to occur for
events that aren't even user-visible --- such as VACUUM FULL relocating
the catalog tuple that describes an object used by the plan.

If we re-parse from source then we will detect any changes that make the
query visibly incorrect.  I don't really see that the user can have any
beef if he continues to use a prepared statement whose source text would
have a valid but incompatible meaning due to changes elsewhere.

			regards, tom lane

Mon, 03 May 2004 12:37:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird prepared stmt behavior 

Tom Lane <tgl@sss.pgh.pa.us> writes:

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

Woah, that would totally defeat the purpose of prepared statements.

The idea is that an OLTP system can prepare all the statements it will ever
need at startup time. Then simply execute them with various parameters as
needed.

For instance, on even a large web site there are often only a few dozen pages
with a few hundred SQL queries total. It's entirely feasible to prepare them
all on startup then simply execute them as needed. This means the optimizer
only ever needs to look at a query once, not every execution which could be
hundreds of times per second.

Actually, using a web server architected like Apache this turns into "prepare
the first time it's seen in a given process or thread". Configure Apache
processes to last a few thousand page requests and the query is reparsed and
optimized often enough to take advantage of new statistics but infrequently
enough to be a negligible hit on performance.

Using a good driver like Perl's DBI this just means using something like
prepare_cached() instead of prepare().

-- 
greg


03 May 2004 15:18:37 -0400
Greg Stark <gsstark@mit.edu>
Re: Weird prepared stmt behavior
On Mon, May 03, 2004 at 03:18:37PM -0400, Greg Stark wrote:
> 
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> > 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.
> 
> Woah, that would totally defeat the purpose of prepared statements.
> 
> The idea is that an OLTP system can prepare all the statements it will ever
> need at startup time. Then simply execute them with various parameters as
> needed.

I don't see how this collides with the ideas presented so far.  The JDBC
driver wants the same: they want to prepare some statements and be able
to use them later in the session.  They don't want to be paying
attention to which prepares were committed and which ones weren't.

> Using a good driver like Perl's DBI this just means using something like
> prepare_cached() instead of prepare().

Then prepare_cached could send a v3 Prepare and assume the statement
will be available for the rest of the session.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)

Mon, 3 May 2004 15:51:00 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior

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

> I don't see how this collides with the ideas presented so far.  The JDBC
> driver wants the same: they want to prepare some statements and be able
> to use them later in the session.  They don't want to be paying
> attention to which prepares were committed and which ones weren't.

Oh I thought the idea was that the statement would only be available within a
transaction.

You're saying they span transactions but if the transaction rolls back then it
also rolls back the statement "creation".

Off the top of my head that doesn't seem like a problem. Though I wonder how
that meshes with other database's views on the point.

> Then prepare_cached could send a v3 Prepare and assume the statement
> will be available for the rest of the session.

Incidentally I tried to find documentation on the v3 binary prepare/execute
protocol and failed. I think I ended up looking at libpq calls which is too
high level to understand what the protocol is and isn't capable of. I have
some ideas of what the next step could be.

Where should I be looking? Source code would be fine if the wire protocol
isn't in the documentation.

-- 
greg


03 May 2004 16:15:10 -0400
Greg Stark <gsstark@mit.edu>
Re: Weird prepared stmt behavior
On Mon, May 03, 2004 at 04:15:10PM -0400, Greg Stark wrote:

> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> 
> > I don't see how this collides with the ideas presented so far.  The JDBC
> > driver wants the same: they want to prepare some statements and be able
> > to use them later in the session.  They don't want to be paying
> > attention to which prepares were committed and which ones weren't.
> 
> Oh I thought the idea was that the statement would only be available within a
> transaction.
> 
> You're saying they span transactions but if the transaction rolls back then it
> also rolls back the statement "creation".

Right.  But note that Tom wants to distinguish between statements
created via PREPARE (which would rollback) from those created via a
Prepare message (which wouldn't).


> Incidentally I tried to find documentation on the v3 binary prepare/execute
> protocol and failed. I think I ended up looking at libpq calls which is too
> high level to understand what the protocol is and isn't capable of. I have
> some ideas of what the next step could be.

> Where should I be looking? Source code would be fine if the wire protocol
> isn't in the documentation.

http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52666

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)

Mon, 3 May 2004 16:29:13 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> If we re-parse from source then we will detect any changes that make the
> query visibly incorrect.  I don't really see that the user can have any
> beef if he continues to use a prepared statement whose source text would
> have a valid but incompatible meaning due to changes elsewhere.
 
I see your point about the reparsing. It's what happens anyway for drivers
such as DBD::Pg which had to emulate a PREPARE before there actually was
one. In effect, the statement is re-parsed every time. So consider me over
my initial uneasiness.
 
What about rolling prepares back if they are in a transaction, though?
They still have the ability to affect a transaction, despite being
partially outside of it:
 
pgf=> begin;
BEGIN
pgf=> prepare toqualify as select 1 from pg_classs;
ERROR:  relation "pg_classs" does not exist
pgf=> prepare toqualify as select 1 from pg_class;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
pgf=> rollback;
ROLLBACK
pgf=> begin;
BEGIN
pgf=> prepare toqualify as select 1 from pg_class;
PREPARE
pgf=> prepare yourself as select 1 from pg_procc;
ERROR:  relation "pg_procc" does not exist
pgf=> prepare yourself as select 1 from pg_proc;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
pgf=> rollback;
ROLLBACK
pgf=> deallocate toqualify;
DEALLOCATE
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200405032114
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAlvCyvJuQZxSWSsgRAq00AJ4tvAseEraeOqz/zG83DvIBX8EPiACeObxW
oUPFV5t+dbgsVnFIjh8FgMs=
=HVkx
-----END PGP SIGNATURE-----



Tue,  4 May 2004 01:22:53 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: Weird prepared stmt behavior 
On Tue, May 04, 2004 at 01:22:53AM -0000, Greg Sabino Mullane wrote:

> What about rolling prepares back if they are in a transaction, though?
> They still have the ability to affect a transaction, despite being
> partially outside of it:

> [example ripped]

IMHO this is an oversight, not a design feature.  That's why I pointed
it out and planned to correct it.

I think with the Prepare message we should _not_ abort the transaction
if it fails -- if it's outside transaction control, it shouldn't affect
the current transaction (So we would open a subtransaction, process the
message, and rollback if it fails.)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La fuerza no estï¿½ en los medios fï¿½sicos
sino que reside en una voluntad indomable" (Gandhi)

Mon, 3 May 2004 22:02:50 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Right.  But note that Tom wants to distinguish between statements
> created via PREPARE (which would rollback) from those created via a
> Prepare message (which wouldn't).

Actually, no, I'd prefer not to make such a distinction; I'd be happy
with SQL-level PREPARE being nontransactional.  I'd be willing to put up
with that distinction if someone shows it's needed, but so far there's
not been a really good argument advanced for it, has there?

			regards, tom lane

Tue, 04 May 2004 00:03:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird prepared stmt behavior 
On Tue, May 04, 2004 at 12:03:16AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > Right.  But note that Tom wants to distinguish between statements
> > created via PREPARE (which would rollback) from those created via a
> > Prepare message (which wouldn't).
> 
> Actually, no, I'd prefer not to make such a distinction; I'd be happy
> with SQL-level PREPARE being nontransactional.  I'd be willing to put up
> with that distinction if someone shows it's needed, but so far there's
> not been a really good argument advanced for it, has there?

Will do.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I would rather have GNU than GNOT."  (ccchips, lwn.net/Articles/37595/)

Tue, 4 May 2004 09:12:19 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Weird prepared stmt behavior
Tom Lane wrote:
> Actually, no, I'd prefer not to make such a distinction; I'd be happy
> with SQL-level PREPARE being nontransactional.  I'd be willing to put
> up with that distinction if someone shows it's needed, but so far
> there's not been a really good argument advanced for it, has there?

Has anyone reviewed the standard with regards to embedded SQL PREPARE?  
It would be pretty weird if that behaved differently from the direct 
SQL PREPARE.  (The brief summary is that is does not roll back, but 
there may be subtleties if have not found.)



Fri, 7 May 2004 20:53:24 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: Weird prepared stmt behavior