Re: PREPARE and transactions

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Jeroen T(dot) Vermeulen" <jtv(at)xs4all(dot)nl>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: PREPARE and transactions
Date: 2004-06-23 16:49:15
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AE74@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


> 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gaetano Mendola 2004-06-23 18:55:59 Re: warning missing
Previous Message Alexander Cohen 2004-06-23 16:46:39 Re: creating a cluster