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

Re: Transactions within a function body

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, "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:31:47
Message-ID: 200810021231.48198.regmeplease@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Hi.

My humble opinion follows.

One point here is that the decision for the ROLLBACK could possibly be 
different from errors.
It could simply be based upon a generic expression, not just the conditions 
seen in "Appendix A" of the manual.
An exception is something different from a transaction, despite the former is 
implemented with the latter.


On Thursday 02 October 2008 11:53:17 Richard Huxton wrote:
> Albe Laurenz wrote:
> > 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.
>
> No, I think you've got that backwards Albe. You can even nest exceptions.
>
> > 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.
>
> Try the following script. By commenting out the second INSERT you can
> change whether you get one or no rows inserted into t1. The
> BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".
>
>
> BEGIN;
>
> CREATE TABLE t1 (a integer);
>
> CREATE OR REPLACE FUNCTION test_exception()
> RETURNS boolean AS $$
> DECLARE
>     n integer;
> BEGIN
>     INSERT INTO t1 (a) VALUES (1);
>     -- INSERT INTO t1 (a) VALUES ('b');
>     BEGIN
>         INSERT INTO t1 (a) VALUES (2);
>         INSERT INTO t1 (a) VALUES ('c');
>     EXCEPTION
>         WHEN OTHERS THEN
>             SELECT INTO n count(*) FROM t1;
>             RAISE NOTICE 'n2 = %', n;
>             RETURN false;
>     END;
>     RETURN true;
> EXCEPTION
>     WHEN OTHERS THEN
>         SELECT INTO n count(*) FROM t1;
>         RAISE NOTICE 'n1 = %', n;
>         RETURN false;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT test_exception();
>
> SELECT count(*) FROM t1;
>
> ROLLBACK;



In response to

pgsql-hackers by date

Next:From: Albe LaurenzDate: 2008-10-02 11:49:06
Subject: Re: Transactions within a function body
Previous:From: Ron MayerDate: 2008-10-02 10:31:40
Subject: Patch for ISO-8601-Interval Input and output.

pgsql-general by date

Next:From: Markus WannerDate: 2008-10-02 10:49:04
Subject: Postgres major version support policy on Debian
Previous:From: Frank DurstewitzDate: 2008-10-02 10:26:20
Subject: Trigger disable for table

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