Re: Transactions within a function body

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;

--
Richard Huxton
Archonet Ltd