|
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...
| Turn off triggers for bulk load
|
Issue: 9-3 |
| [GENERAL] Turning off triggers ? |
25-Nov-2002
| |
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 -----
Sent: Friday, July 06, 2007 10:06
PM
Subject: [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?
|
