Re: Weird prepared stmt behavior

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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