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

Re: Transactions within a function body

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Alvaro Herrera *EXTERN*" <alvherre(at)commandprompt(dot)com>,"Dennis Brakhane" <brakhane(at)googlemail(dot)com>
Cc: "Reg Me Please" <regmeplease(at)gmail(dot)com>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Transactions within a function body
Date: 2008-10-02 09:01:37
Message-ID: D960CB61B694CF459DCFB4B0128514C202901E88@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Alvaro Herrera wrote:
> > > Is there a way to have (sub)transactions within a function body?
> > > I'd like to execute some code (a transaction!) inside a function and later
> > > decide whether that transaction is to be committed or not.
> > 
> > You could issue a "SAVEPOINT name". If at the end you don't want your
> > changes to apply, you can issue a "ROLLBACK to name"
> 
> Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
> PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
> just do a RAISE EXCEPTION, and the exception block is run).

After a discussion on comp.databases.postgresql I realized that this
is actually a limitation.

Consider the following:

BEGIN
   UPDATE ...
   UPDATE ...
   UPDATE ...
EXCEPTION
   WHEN integrity_constraint_violation THEN
      ...
END;

If the first UPDATE succeeds but the second one bombs, there is no way
to undo the first update short of having the whole transaction cancelled.

So while exceptions are implemented using savepoints, they give you only
part of the functionality, namely to make a group of statements
all-or-nothing within one transaction.

If you need all three of these UPDATEs to either all succeed or fail,
but the whole transaction should continue, you cannot do that in PL/pgSQL.

Is there a chance to get savepoint support in PL/pgSQL at some point?
Does it make sense to raise this on -hackers?

Yours,
Laurenz Albe

In response to

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2008-10-02 09:02:35
Subject: Re: Common Table Expressions (WITH RECURSIVE) patch
Previous:From: Zdenek KotalaDate: 2008-10-02 08:32:42
Subject: Re: FSM rewrite committed, loose ends

pgsql-general by date

Next:From: Richard HuxtonDate: 2008-10-02 09:53:17
Subject: Re: Transactions within a function body
Previous:From: bullDate: 2008-10-02 08:10:21
Subject:

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