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: "Richard Huxton *EXTERN*" <dev(at)archonet(dot)com>
Cc: "Alvaro Herrera *EXTERN*" <alvherre(at)commandprompt(dot)com>,"Dennis Brakhane" <brakhane(at)googlemail(dot)com>,"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 11:49:06
Message-ID: D960CB61B694CF459DCFB4B0128514C202901F6A@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Richard Huxton 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.
> 
[...]
> 
> The BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".

You are right, and I'm happy to find myself wrong:

CREATE TABLE t1 (a integer PRIMARY KEY);

CREATE FUNCTION test_exception() RETURNS boolean LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (2);
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (3);
   RETURN TRUE;
EXCEPTION
   WHEN integrity_constraint_violation THEN
      RAISE NOTICE 'Rollback to savepoint';
      RETURN FALSE;
END;$$;

BEGIN;

SELECT test_exception();
NOTICE:  Rollback to savepoint
 test_exception 
----------------
 f
(1 row)

COMMIT;

SELECT count(*) FROM t1;
 count 
-------
     0
(1 row)

Great, thank you!

Yours,
Laurenz Albe

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2008-10-02 11:51:08
Subject: Re: [COMMITTERS] pgsql: Allow pg_regress to be run outside the build tree.
Previous:From: Reg Me PleaseDate: 2008-10-02 10:31:47
Subject: Re: Transactions within a function body

pgsql-general by date

Next:From: Roberto MarianoDate: 2008-10-02 11:51:37
Subject: Postmaster exit code 128 on Windows 2003 Server
Previous:From: A. KretschmerDate: 2008-10-02 10:59:45
Subject: Re: Trigger disable for table

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