Thread: Update a single row without firing its triggers?
Hello.
Suppose I have a table tbl with columns (a, b, c, counter).
And I have 5 ON UPDATE triggers assigned to this table. They process (a, b, c) columns, but never depend on counter.
I need to update counter field, but I know that it is totally independent, so - for performance reason I want to temporarily disable all triggers during the tbl.counter updation.
How could I do it?
(Please do not offer ALTER TABLE tbl DISABLE TRIGGER ALL. It is NOT a production case: ALTER TABLE locks all the table during, so it cannot be used in heavy-loaded systems.)
(Please do not also offer top move the counter to another table, because it is used in complex indices, e.g. INDEX ON (counter, a, c) to speedup fetching.)
Possible solution: add an additional column named "disable_trg" BOOLEAN: (a, b, c, disable_trg). Then, I use the following UPDATE:
UPDATE tbl SET counter = counter + 1, disable_trg = true WHERE a = 10;
In each trigger I firstly run an instruction:
IF NEW.disable_trg THEN RETURN NEW; END IF;
And the latest trigger resets disable_trg field to NULL, so it is not written to the table. So, in some queries I may explicitly specify do I need to disable triggers or not.
But this solution (the only possible?) looks like a brute-force method. Possibly Postgrs has another one, better?
Suppose I have a table tbl with columns (a, b, c, counter).
And I have 5 ON UPDATE triggers assigned to this table. They process (a, b, c) columns, but never depend on counter.
I need to update counter field, but I know that it is totally independent, so - for performance reason I want to temporarily disable all triggers during the tbl.counter updation.
How could I do it?
(Please do not offer ALTER TABLE tbl DISABLE TRIGGER ALL. It is NOT a production case: ALTER TABLE locks all the table during, so it cannot be used in heavy-loaded systems.)
(Please do not also offer top move the counter to another table, because it is used in complex indices, e.g. INDEX ON (counter, a, c) to speedup fetching.)
Possible solution: add an additional column named "disable_trg" BOOLEAN: (a, b, c, disable_trg). Then, I use the following UPDATE:
UPDATE tbl SET counter = counter + 1, disable_trg = true WHERE a = 10;
In each trigger I firstly run an instruction:
IF NEW.disable_trg THEN RETURN NEW; END IF;
And the latest trigger resets disable_trg field to NULL, so it is not written to the table. So, in some queries I may explicitly specify do I need to disable triggers or not.
But this solution (the only possible?) looks like a brute-force method. Possibly Postgrs has another one, better?
Hello.
These days I was trying to temporarily disable triggers, too, and had much problems with ALTER TABLE..DISABLE TRIGGER ALL. So, I was googling for another solution and have found this: http://www.varlena.com/GeneralBits/101.php. Works OK in my case...
|
Another issue with bulk loading is triggers firing with each row inserted. If you are sure your data is trustworthy and already meets your referential integrity requirements, you can turn off triggers for the bulk load and turn them back on immediately afterward. You should not use this option when your data is not completely clean.
The reltriggers field in the pg_class table contains the number of triggers active for each table. It can be set to 0 the disable the triggers, but will need to be reset to the proper number of triggers to have them re-enabled.
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'tablename'; UPDATE pg_class SET reltriggers = ( SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'table name';
Contributors: Stephan Szabo sszabo at megazone23.bigpanda.com, Jean-Luc Lachance jllachan at nsd.ca, Glen Eustace geustace at godzone.net.nz, Adam Witney awitney at sghms.ac.uk
Regards,
Zlatko
----- Original Message -----From: Dmitry KoterovTo: Postgres GeneralSent: Friday, July 06, 2007 10:06 PMSubject: [GENERAL] Update a single row without firing its triggers?Hello.
Suppose I have a table tbl with columns (a, b, c, counter).
And I have 5 ON UPDATE triggers assigned to this table. They process (a, b, c) columns, but never depend on counter.
I need to update counter field, but I know that it is totally independent, so - for performance reason I want to temporarily disable all triggers during the tbl.counter updation.
How could I do it?
(Please do not offer ALTER TABLE tbl DISABLE TRIGGER ALL. It is NOT a production case: ALTER TABLE locks all the table during, so it cannot be used in heavy-loaded systems.)
(Please do not also offer top move the counter to another table, because it is used in complex indices, e.g. INDEX ON (counter, a, c) to speedup fetching.)
Possible solution: add an additional column named "disable_trg" BOOLEAN: (a, b, c, disable_trg). Then, I use the following UPDATE:
UPDATE tbl SET counter = counter + 1, disable_trg = true WHERE a = 10;
In each trigger I firstly run an instruction:
IF NEW.disable_trg THEN RETURN NEW; END IF;
And the latest trigger resets disable_trg field to NULL, so it is not written to the table. So, in some queries I may explicitly specify do I need to disable triggers or not.
But this solution (the only possible?) looks like a brute-force method. Possibly Postgrs has another one, better?
Why not just write the trigger function as: if old.a is distinct from new.a or old.b is distinct from new.b ... end if -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice