"Finnerty, Jim" <jfinnert@amazon.com> writes:
> PostgreSQL executes AFTER triggers (and constraints) in alphabetical order according to the name assigned to the
triggerat creation time; therefore, PostgreSQL permits IMMEDIATE foreign key constraints to be deferred until after
AFTERtriggers are fired if their name sorts lower than “RI_ConstraintTrigger”. This is a violation of the standard.
We consider that a feature, not a bug. If you want standard-compliant
behavior, don't name your triggers that way. On the other hand, if
you'd like to get something done before FK constraints fire, you have
the option to do so.
(I think this is documented somewhere. If not, there's an opportunity
for documentation improvement.)
The fact that the sort is plain ASCII means that "RI_ConstraintTrigger"
sorts before any lower-case trigger name, so the issue is not as large
as it might otherwise seem.
regards, tom lane