Message-ID: <001001c36183$4f636570$0403a8c0@fejleszt4>
From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
To: <pgsql-sql@postgresql.org>
Subject: Order of triggers - totally lost
Date: Wed, 13 Aug 2003 12:11:46 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit

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 -------------------------------