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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Durstewitz | 2008-10-02 10:26:20 | Trigger disable for table |
Previous Message | Richard Huxton | 2008-10-02 09:53:17 | Re: Transactions within a function body |
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2008-10-02 10:27:14 | Re: trivial patch for pg_freespacemap documentation |
Previous Message | hubert depesz lubaczewski | 2008-10-02 10:01:30 | trivial patch for pg_freespacemap documentation |