Execution order of cascading triggers in postgresql

Question

I'm wondering about the execution/firing order of triggers for FKs with UPDATE CASCADE, concerning grandchild tables, in PostgreSQL (9.3)

Here's what I have:

Parent:      Invoices           (invoice_id)
Child:       Invoice_Lines      (invoice_id, line_nbr)
Grandchild:  Invoice_Line_Taxes (invoice_id, line_nbr, tax)

Invoice_Lines has FOREIGN KEY (invoice_id) REFERENCES Invoices ON UPDATE CASCADE;
Invoice_Line_Taxes has FOREIGN KEY (invoice_id, line_nbr) REFERENCES Invoice_Lines ON UPDATE CASCADE;

I also have a custom UPDATE trigger on Invoices (who's name start with S, which comes after RI_). This trigger sums up the invoice amounts. And my actual update statement changes invoice_id, which gets propagated down to the child and grandchild.

Trouble is, in/during my custom trigger, the invoice_id for Invoice_Lines has already changed, but not for the grandchild Invoice_Line_Taxes.

I've dumped the rows from within the custom trigger using RAISE:

invoice_lines: (5,1)
invoice_line_taxes: (-1,1,HST)

After the trigger:

SELECT * from invoice_line_taxes where invoice_id IN (5,-1);
 invoice_id | line_nbr | tax_nm
------------+----------+--------
          5 |        1 | HST

So I'm wondering, what's the trigger execution order with respect to cascading triggers?

I would have assumed something like this:

RI_on_invoices
RI_on_invoice_lines
S_custom_trigger

Any ideas? Anybody know where I can get official documentation on the execution ordering? I've tried looking for detailed documentation about this, but all I've found is that ordering is alphabetical. Perhaps if I find the specific details about the ordering I could build something around it. But right now, I'd be basing it on guess work.

Thank you.

Answer 1

After setting up some triggers on the tables involved that simply raised a notice that they were going called, I observed the following sequence of events (Though, thanks to the answer above, EXPLAIN ANALYZE achieves this more easily):

1. Update on Invoices
2. all triggers on Invoices are fired, in order
   • some of these triggers affect Update on Invoices_Lines (child table)
3. all triggers on Invoice_Lines are fired, in order (but after all the parent triggers are done)
   • some of these triggers affect Update on Invoice_Line_Taxes (grandchild table)
4. all triggers on Invoice_Line_Taxes are fired, in order (but after all its parent triggers are done)

So, the naming of triggers only affects its "local" execution order.

Anyhow, I ended up setting the trigger to run INITIALLY DEFERRED, and that gets the job done. Though yes, I agree, using triggers in this way is tenuous.

Answer 2

The trigger execution order is indeed alphabetical, which you want to understand here as ASCII string order, "0" < "A" < "_" < "a", while keeping in mind that "A3" < "a1" < "a10" < "a2".

When you want to force the execution in a specific order, it's simpler to prefix trigger names, e.g. (note the double quotes when identifiers start with a number):

create trigger _01_do_stuff  ...
create trigger "01_do_stuff" ...

Asides:

• If your app relies on triggers getting fired in a specific order, or cascade updating invoice ids, it may be a sign that something's wrong in your schema, in your design, or in your code flow.
• If memory serves, foreign keys are enforced internally as constraint triggers, so the order in which the triggers fire can come and bite you if you rely on them for application logic.
• As a rule of thumb, avoid making triggers have side effects on the tables that fire them, whether directly or indirectly. The only exception to this rule is before triggers affecting the row that fires them. Anything else will yield hard to fix bugs.

FWIW, my best guess is that your actual problem is you've two or three of these points and MVCC working against you. Namely, a trigger is probably making PG mark the original row as dead and insert a new live row; a subsequent trigger (e.g. the cascade update?) then makes PG mark that new live row as dead as well, resulting in yet another live row with unexpected data in it, and that in term may be firing further triggers for further side effects.

Put another way, your raise notice is not observing the final updated row, but an intermediary one that gets squashed by one or more subsequent updates. Observing the ctid column of the rows involved should reveal this, if that is indeed what's going on.

Lastly, note that explain analyze will show the order in which triggers get executed on the target table. (Last I tried it, it didn't show cascaded triggers downhill, but this may have changed in recent versions.) Using that command can be helpful when debugging trigger interactions, or when identifying performance problems.