Preventing an 'after' trigger from causing rollback on error
Is there any way I can stop a trigger which fires after a row is inserted
into a table from causing a rollback of the entire transaction if something
goes wrong?
I have rows being written to a table, and I have triggers which calculate
and update statistics automatically in stats tables based on the rows
inserted. However, if something goes wrong in that trigger, right now it
causes all the inserts to roll back, as well as the stats calculations. I
would rather keep all the inserts, but just fail the stats calculates. I
can always rematerialize the entire stats table with another procedure if
all the rows are present.
Thanks in advance for any assistance.
--
Eliot Gable
On Fri, 2012-04-13 at 17:58 -0400, Eliot Gable wrote:
Is there any way I can stop a trigger which fires after a row is
inserted into a table from causing a rollback of the entire
transaction if something goes wrong?
1. Try using subtransactions
( http://www.postgresql.org/docs/9.1/static/sql-savepoint.html ). I
suggest releasing or rolling back the savepoints that you no longer need
because triggers can be executed many times.
2. You could have a separate connection that does the processing you
need, and use LISTEN/NOTIFY to alert the other connection that new data
is available to process.
Regards,
Jeff Davis
On Fri, Apr 13, 2012 at 3:58 PM, Eliot Gable
<egable+pgsql-general@gmail.com> wrote:
Is there any way I can stop a trigger which fires after a row is inserted
into a table from causing a rollback of the entire transaction if something
goes wrong?
Take look here:
http://www.postgresql.org/docs/9.0/static/plpgsql-porting.html
Specifically the part about:
BEGIN
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
EXCEPTION
WHEN unique_violation THEN (2)
-- don't worry if it already exists
END;