Thread: Weird prepared stmt behavior

Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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


Re: Weird prepared stmt behavior

From
Tom Lane
Date:
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


Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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)


Re: Weird prepared stmt behavior

From
Tom Lane
Date:
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


Re: Weird prepared stmt behavior

From
"Greg Sabino Mullane"
Date:
-----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-----




Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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."


Re: Weird prepared stmt behavior

From
Tom Lane
Date:
> 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


Re: Weird prepared stmt behavior

From
James Robinson
Date:
[ WRT/ automagically re-parsing prepared statement from source when 
dependency
plan changes.]

If done, this would have the wonderful side-effect of being able to use 
regular queries
in plpgsql procedures which must currently be done using the EXECUTE 
form, such
as those that just need to manipulate temporary tables.

Quite spiffy, reducing the amount of surprise encountered by postgres 
neophytes.

----
James Robinson
Socialserve.com



Re: Weird prepared stmt behavior

From
Greg Stark
Date:
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



Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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)


Re: Weird prepared stmt behavior

From
Greg Stark
Date:
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



Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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)


Re: Weird prepared stmt behavior

From
"Greg Sabino Mullane"
Date:
-----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-----




Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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)


Re: Weird prepared stmt behavior

From
Tom Lane
Date:
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


Re: Weird prepared stmt behavior

From
Alvaro Herrera
Date:
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/)


Re: Weird prepared stmt behavior

From
Oliver Jowett
Date:
(I'm not on -hackers, but saw this in the archives)

Alvaro Herrera wrote:
> On Fri, Apr 30, 2004 at 09:44:52PM -0400, Tom Lane wrote:
>> Alvaro Herrera <alvherre ( at ) dcc ( dot ) uchile ( dot ) 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?

At least from the JDBC driver's point of view, having prepared
statements roll back is more work for the driver. Currently it uses
PREPARE/EXECUTE statements, but eventually it'll use the protocol-level
messages.

When the JDBC driver is given a query to execute and decides to use
server-side preparation, it sends a PREPARE (or eventually a Parse
message). Thereafter, when that same query is executed it will send an
EXECUTE (or Bind/Execute) instead of the full query. It does this by
setting some state in the driver-side object representing the query to
say "this query is prepared with name 'foo'".

If PREPARE can roll back, the driver must maintain a set of all
statements that were sucessfully PREPAREd in the current transaction,
and fix up the corresponding query object state whenever a transaction
rolls back.

 From that point of view, it's much simpler to keep PREPARE (or at least
Parse) as it currently is. I suspect the same argument applies to any
interface layer that uses PREPARE or Parse automatically.

-O


Re: Weird prepared stmt behavior

From
Karel Zak
Date:
On Sun, May 02, 2004 at 10:08:50AM +1200, Oliver Jowett wrote:
> If PREPARE can roll back, the driver must maintain a set of all
> statements that were sucessfully PREPAREd in the current transaction,
> and fix up the corresponding query object state whenever a transaction
> rolls back.
>
> From that point of view, it's much simpler to keep PREPARE (or at least
> Parse) as it currently is. I suspect the same argument applies to any
> interface layer that uses PREPARE or Parse automatically.

 Exactly.

 Tom, will work these two scenarios:

1/ I have web application that  uses persistent connetions to PostgreSQL
   backend. After the  connection opening  the application  prepares all
   queries and the  rest of the application code  uses EXECUTE statement
   only. It  means the  EXECUTE  statemens are  used  in next  arbitrary
   transactions.

2/ The  other way  which my  application  uses is  "prepare query  first
   time  when some  code needs  it" --  and it's  independend on  actual
   transaction  of  course. I  use  this  way  now,  beacuse  it's  more
   effective for me than prepare all queries after the connection startup.

 If I good understand your idea the case 1/ will work, but case 2/ not.

 I have no care about

        BEGIN;
        CREATE TABLE xxx (id serial);
        PREPARE q AS SELECT * FROM xxx;
        ABORT;

        EXECUTE q;
        ERROR:  relation with OID 38242 does not exist

 because I can detect it by  error message and it's too academic problem
 for me.  I don't change DB schema in stable and production server and I
 think ALTER/DROP/CREATE is  nothing often in running  and good designed
 databases.

    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Re: Weird prepared stmt behavior

From
Peter Eisentraut
Date:
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.)