Re: Transactions within a function body

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