From: | "Dmitry Koterov" <dmitry(at)koterov(dot)ru> |
---|---|
To: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Update a single row without firing its triggers? |
Date: | 2007-07-06 20:06:11 |
Message-ID: | d7df81620707061306k3e25004ak5ec4d1f2e394e9f0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Staubo | 2007-07-06 20:20:09 | Re: Polymorphic delete help needed |
Previous Message | Stephen Frost | 2007-07-06 19:29:01 | Re: ISO TESTS for a Pg lexer+parser |