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

Re: Transactions within a function body

From: Richard Huxton <dev(at)archonet(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>, 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 09:53:17
Message-ID: 48E49A0D.6030506@archonet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Ron MayerDate: 2008-10-02 09:53:34
Subject: Interval output bug in HAVE_INT64_TIMESTAMP
Previous:From: Greg StarkDate: 2008-10-02 09:02:35
Subject: Re: Common Table Expressions (WITH RECURSIVE) patch

pgsql-general by date

Next:From: Reg Me PleaseDate: 2008-10-02 10:24:59
Subject: Re: Transactions within a function body
Previous:From: Albe LaurenzDate: 2008-10-02 09:01:37
Subject: Re: Transactions within a function body

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