Re: PREPARE and 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]

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 previous error]
ABORT
BEGIN
BEGIN
PREPARE foo AS ... [fails: foo is already defined!]
COMMIT/ROLLBACK
EXECUTE foo [will now always run if 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