Re: Trigger violates foreign key constraint

On Mon, Oct 30, 2023 at 2:50 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, Oct 3, 2023 at 12:52 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
>
>> On Mon, 2023-10-02 at 09:49 -0400, Tom Lane wrote:
>> > This is by design: triggers operate at a lower level than
>> > foreign keys, so an ill-conceived trigger can break an FK constraint.
>> > That's documented somewhere, though maybe not visibly enough.
>>
>> Not having found any documentation, I propose the attached caution.
>>
>>
> I dislike scaring the user like this without providing any context on what
> conditions or actions are problematic.
>
> The ON DELETE and ON UPDATE clauses of foreign keys are implemented as
> system triggers on the referenced table that invoke additional delete or
> update commands on the referencing table. The final outcome of these
> additional commands are not checked - it is the responsibility of the DBA
> to ensure that the user triggers on the referencing table actually remove
> the rows they are requested to remove, or update to NULL any referencing
> foreign key columns. In particular, before row triggers that return NULL
> will prevent the delete/update from occurring and thus result in a violated
> foreign key constraint.
>
> Add sgml as needed, note the original patch missed adding "<productname>"
> to PostgreSQL.
>
>
Additionally, the existing place this is covered is here:

"""
Trigger functions invoked by per-statement triggers should always return
NULL. Trigger functions invoked by per-row triggers can return a table row
(a value of type HeapTuple) to the calling executor, if they choose. A
row-level trigger fired before an operation has the following choices:

It can return NULL to skip the operation for the current row. This
instructs the executor to not perform the row-level operation that invoked
the trigger (the insertion, modification, or deletion of a particular table
row).

For row-level INSERT and UPDATE triggers only, the returned row becomes the
row that will be inserted or will replace the row being updated. This
allows the trigger function to modify the row being inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of these
behaviors 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).
"""

We should probably add a note pointing back to the DDL chapter and that
more concisely says.

"Note: If this table also contains any foreign key constraints with on
update or on delete clauses, then a failure to return the same row that was
passed in for update and delete triggers is going to result in broken
referential integrity for the affected row."

I do like "broken referential integrity" from the original patch over
"violated foreign key constraint" - so that needs to be substituted in for
the final part of my earlier proposal if we go with its more detailed
wording. My issue with "violated" is that it sounds like the system is
going to catch it at the end - broken doesn't have the same implication.

David J.