Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Gaetano MendolaDate: 2004-06-23 18:55:59
Subject: Re: warning missing
Previous:From: Alexander CohenDate: 2004-06-23 16:46:39
Subject: Re: creating a cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group