Thread: Transaction commit in a trigger function

Hi there,

I'm using PG 8.2.3.

Is it possible to (somehow) commit a specific statement in a trigger
function if the function itself is rolled back because of an error (eg, for a
unique index error)?

For example:

create table tab1 (col1 int unique);
create table tab2 (col1 int);

CREATE OR REPLACE FUNCTION
f_func1 () RETURNS trigger AS $$
BEGIN
        --  ... some processing ...

        INSERT INTO tab2 (col1) VALUES (new.col1);
        -- COMMIT the above statement, irrespective of whether this
        -- trigger/function is rolled back or not.

        RETURN new;
end;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tiu_t1
    BEFORE UPDATE OR INSERT ON tab1
    FOR EACH ROW
    EXECUTE PROCEDURE f_func1();


* * *

Now, if you:

insert into tab1 (col1) values (1);

and tab1 already has a row with col1=1, tab2 must be updated even when
the statement fails with:

ERROR:  duplicate key violates unique constraint...

I know this can be achieved outside the DB (ie, with checks, etc), but I'd
like to keep this aspect inside the DB.  Also, I could perform selects inside
the trigger to pre-empt a unique constraint error, but this will slow the
inserts down.

I can't wrap BEGIN/COMMIT around the INSERT in the trigger.  Is there
another way of achieving this?

Any suggestions are appreciated.

Regards

Henka wrote:

> Is it possible to (somehow) commit a specific statement in a trigger
> function if the function itself is rolled back because of an error (eg, for a
> unique index error)?

No.  You can use savepoints (or, in PL/pgSQL functions, EXCEPTION
blocks) to inhibit the uniqueness error from aborting the transaction.
There is an example in the error catching section of the PL/pgSQL docs
about the insert-if-fail-update construct.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

> I can't wrap BEGIN/COMMIT around the INSERT in the trigger.  Is there
> another way of achieving this?

    - Savepoints (won't work with your trigger approach)
    - dblink would allow you to open another connection concurrently

>     - dblink would allow you to open another connection concurrently

This suggestion worked perfectly, thank you very much.