On Thu, Apr 19, 2018 at 11:57 AM, Tom Lane <tgl@sss.pgh.pa.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.
