Re: Trigger violates foreign key constraint

Thanks for having a look at my patch!

On Mon, 2023-10-30 at 15:03 -0700, David G. Johnston wrote:
> On Mon, Oct 30, 2023 at 2:50 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
> > On Tue, Oct 3, 2023 at 12:52 AM Laurenz Albe <laurenz.albe@cybertec.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.

I specifically *want* to scare^H^H^H^H^Halert the user, and I thought I
provided sufficient context and a link to a more detailed description of
how triggers behave.
What is unclear or lacking in the proposed wording?

  In particular, other triggers
  defined on the referencing table can cancel or modify the effects of
  cascading delete or update, thereby breaking referential integrity.

> > 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.

I didn't plan to write a novel on the topic... and I don't think your wording is
clearer than mine.  I went over my text again with the intent to add clarity, but
apart from a few minor modifications ("other triggers" -> "user-defined triggers")
I couldn't make it clearer.  I'd have to write an example to make it clearer,
and that would certainly be out of scope.

> > Add sgml as needed, note the original patch missed adding "<productname>" to PostgreSQL.

Ah, thanks for noticing!  Fixed.

>
> Additionally, the existing place this is covered is here:
>
> [https://www.postgresql.org/docs/current/trigger-definition.html]
>
> 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."

My patch already contains a link to this very section.

I tried to understand your sentence and had to read it several times.  I don't
think that it adds clarity to my patch.



Attached is a slightly modified version of the patch.

Yours,
Laurenz Albe