Thread: Weird prepared stmt behavior
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
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
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)
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
-----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-----
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."
> 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
[ 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
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
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)
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
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)
-----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-----
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)
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
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/)
(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
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/
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.)