Re: Trigger firing order

Thanks again Tom. I really appreciate the time you take to respond to my
noob questions.

Steve

On Thu, May 17, 2018 at 8:04 AM, Tom Lane <[email protected]> wrote:

> Steve Baldwin <[email protected]> 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
>