From: | "Henka" <henka(at)cityweb(dot)co(dot)za> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Transaction commit in a trigger function |
Date: | 2007-05-17 18:29:49 |
Message-ID: | 63503.196.23.181.69.1179426589.squirrel@support.metroweb.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert James | 2007-05-17 18:44:03 | Re: Postgres Printed Manuals |
Previous Message | Andrew Sullivan | 2007-05-17 18:23:19 | Re: Fault Tolerant Postgresql (two machines, two postmasters, one disk array) |