Re: Odd(?) RI-trigger behavior

On Thu, 18 Apr 2002, Tom Lane wrote:

> This particular test involves a table with a foreign-key reference to
> itself, ie, it's both PK and FK.  What apparently is happening is that
> the two RI triggers are now being fired in a different order than
> before.  While either of them would have detected an error, we now get
> the other error first.
>
> Does this bother anyone?  It seems to me that the old code essentially
> had no guarantee at all about the order in which the triggers would
> fire, and so it was pure luck that the regression test never showed
> the other message.

That's probably a bad thing even if I doubt that it'd ever come up the
other way barring changes to other regression tests in practice.  Forcing
an order probably helps with this case anyway.

> With the modified code, because we load the triggers by scanning
> an index on (tgrelid, tgname), it is actually true that triggers are
> fired in name order.  We've had requests in the past to provide a
> well-defined firing order for triggers --- should we document this
> behavior and support it, or should we pretend it ain't there?

Didn't someone (Peter?) say that the mandated firing order was based on
creation order/time in SQL99?