From: | SZŰCS Gábor <surrano(at)mailbox(dot)hu> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Order of triggers - totally lost |
Date: | 2003-08-13 10:11:46 |
Message-ID: | 001001c36183$4f636570$0403a8c0@fejleszt4 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Dear Gurus,
Please help to solve this chaos: I have a simple query that triggers several
plpgsql triggers on several tables.
Table A has a BIUD (Before Insert or Update or Delete) trigger (henceforth
A_BIUD and analogues).
Table B has triggers BIUD, AI and AU.
Table B1 has triggers BIUD and AD.
Table B2 has triggers BIUD and AIUD.
Consider the following, my preferred trigger order for a specific UPDATE on
table A:
A_BIUD, update:
insert into B (foobar); -- inserting a single tuple
B_BIUD, insert -- irrelevant
B_AI, insert:
insert into B1 (foo2); -- one or more tuples
B1.BIUD, insert:
if B is done then raise exception;-- "done" is false by default.
insert into B2 (foo2); -- the same one or more tuples
B2.BIUD, insert -- irrelevant
update B set done=true WHERE foobar; -- updating the same single tuple
B_BIUD, update -- irrelevant
B_AU, update:
update B2 set done=true WHERE foo2; -- the same one or more tuples
B2.BIUD, update -- irrelevant
In short:
A_BIUD, update
B_BIUD, insert
B_AI, insert
B1_BIUD, insert
B2_BIUD, insert
B_BIUD, update
B_AU, update
However: the triggers run in the following order, until the abovementioned
exception aborts the transaction:
A_BIUD, update
B_BIUD, insert
B_BIUD, update
A_BIUD, update ends here
B_AI, insert
B1_BIUD, insert: exception.
The docs say in trigger-datachanges.html:
"Changes made by query Q are visible by queries that are started after query
Q, no matter whether they are started inside Q (during the execution of Q)
or after Q is done.
This is true for triggers as well ..."
Trying to understand that, I have a feeling that the update in A_BIUD should
already see the results of the preceding insert, including the results of
triggers activated by that insert.
What may be wrong? Any ideas to re-organize parts of the triggers? May
putting the update to an A_AU trigger help? I tried it, still have problems
(not sure it's still the trigger order), but the trigger order is still
strange for me:
A_BIUD
B_BIUD, insert
B_AI
B1_BIUD, insert
A_AU
B2_AIUD, insert (!)
B2_BIUD, insert (???)
B_AU
G.
------------------------------- cut here -------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Haller | 2003-08-13 11:40:53 | Re: INSERT INTO ... SELECT |
Previous Message | Abdul Wahab Dahalan | 2003-08-13 03:20:58 | How to speeed up the query performance |