Re: Trigger firing order - Mailing list pgsql-novice

Steve Baldwin <steve.baldwin@gmail.com> writes:
> I'm using triggers to implement a DML auditing mechanism. I've been doing
> some testing around cascading foreign key constraints and the results
> surprised me (and somewhat busted my mechanism haha). I was hoping to be
> able to log statement level data as well as actual row changes that link
> back to the logged statement.

> ...

> What surprised me was the after statement trigger for t2 and 1+ of the
> after row triggers for t2 fired after the after statement trigger for t1.

Yeah.  The reason is that all the "after" triggers fired by the outer
statement on t1 (the RI enforcement triggers, and your a_ar triggers on
t1, and your a_as trigger on t1) are queued up, then when the RI
enforcement triggers run and cause additional "after" trigger events
to be queued (the ones for t2), those go onto the end of the queue.

> I guess I had imagined all operations for 'nested' tables would complete
> before executing the after statement trigger of the outermost 'invoking'
> table. (I'm coming from having used Oracle for many years).

We could easily fire the newly-queued "after" triggers before returning
from each RI enforcement trigger, but that creates a new set of problems:
those triggers would see an incompletely-updated state of t2, if more RI
enforcement actions remain in the queue.  Essentially that would make
visible the fact that whatever cascade actions need to be done are done
in a separate command for each cascaded-from row, whereas the SQL spec
would have us make it look like all the cascade effects occur in a single
statement.  So we do it like this instead.  (I'm not totally wedded to
this as being a great idea, but rejiggering it would be a complex task
with probably not a lot of payoff in the end.)

> Can anyone point me to somewhere in the docs where this behaviour is
> explained?

I doubt it's terribly well documented :-(

> I understand postgres FK's are implement with triggers. How do I
> find them so I can see what they're doing and hopefully better understand
> the mechanism?

Just look into pg_trigger, eg

SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgisinternal
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = 'mytable'::regclass
ORDER BY 1;

(psql's \d command does essentially this to show you a table's triggers,
but it skips "tgisinternal" triggers which is why you don't see the RI
triggers.)

                        regards, tom lane