Re: Problem with trigger makes Detail record be invalid

On Thu, Apr 19, 2018 at 11:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> (I'm not sure that this issue is adequately documented, though.
> I'd have expected to find something about it in triggers.sgml and/or
> create_trigger.sgml, but in a quick look neither of them mentions foreign
> keys.)
>

​I'm leading toward inadequate myself...though create_trigger.sgml does
have:

"""
​SQL specifies that BEFORE DELETE triggers on cascaded deletes fire after
the cascaded DELETE completes. The PostgreSQL behavior is for BEFORE DELETE
to always fire before the delete action, even a cascading one. This is
considered more consistent. There is also nonstandard behavior if BEFORE
triggers modify rows or prevent updates during an update that is caused by
a referential action. This can lead to constraint violations or stored data
that does not honor the referential constraint.
"""

And triggers.sgml has:

"""
A row-level BEFORE trigger that does not intend to cause [a delete] must be
careful to return as its result the same row that was passed in (that is,
the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE
triggers).
"""

There is a lot of surrounding text to sift through though - and the former
is a "compatibility" comment. Warning blurb after the triggers.sgml quoted
section about preventing the action from occurring potentially breaking FK
constraints would be a reasonable response to this report.

I'd rather have a developer spend time coding up having an FK constraint
define an AFTER STATEMENT trigger using a transition table and ensure that
all FK constraints remain enforced for all changed records. Correctly or
incorrectly written triggers do not have any liberty to violate FK
constraints and the fact that they can is reasonably considered by the user
base to be a bug.

David J.