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

Re: Transactions within a function body

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: "Alvaro Herrera *EXTERN*" <alvherre(at)commandprompt(dot)com>, "Dennis Brakhane" <brakhane(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Transactions within a function body
Date: 2008-10-02 10:24:59
Message-ID: 200810021224.59496.regmeplease@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Well, if it is a limitation, and having it would lead to a "better product",
why not making it a feature for the next still-open release?

In my opinion that's more than a limitation, it's a missing feature.
In your code you often need to create savepoints to delay the decision for the 
commitment.
A Pl/PgSQL function is just a bunch of code you want to move into the DB.
So the need for savepoints seems to me to be still there.

Useless to say I would vote for a "GO".

On Thursday 02 October 2008 11:01:37 Albe Laurenz wrote:
> 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: Heikki LinnakangasDate: 2008-10-02 10:27:14
Subject: Re: trivial patch for pg_freespacemap documentation
Previous:From: hubert depesz lubaczewskiDate: 2008-10-02 10:01:30
Subject: trivial patch for pg_freespacemap documentation

pgsql-general by date

Next:From: Frank DurstewitzDate: 2008-10-02 10:26:20
Subject: Trigger disable for table
Previous:From: Richard HuxtonDate: 2008-10-02 09:53:17
Subject: Re: Transactions within a function body

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