Re: Trigger violates foreign key constraint - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Trigger violates foreign key constraint
Date
Msg-id 2899874.1712515943@sss.pgh.pa.us
Whole thread Raw
In response to Re: Trigger violates foreign key constraint  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: Trigger violates foreign key constraint
List pgsql-hackers
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Patch v3 is attached.

I agree with documenting this hazard, but I think it'd be better
to do so in the "Triggers" chapter.  There is no hazard unless
you are writing user-defined triggers, which is surely far fewer
people than use foreign keys.  So I suggest something like the
attached.

            regards, tom lane

diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index a5390ff644..99d8b75fc5 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -354,6 +354,20 @@
     to avoid infinite recursion in such scenarios.
    </para>
 
+   <para>
+    <productname>PostgreSQL</productname> implements foreign key
+    constraints via system-defined <literal>AFTER</literal> triggers.
+    If a foreign key constraint specifies referential actions (that is,
+    cascading updates or deletes), these triggers issue ordinary SQL
+    update or delete commands on the referencing table to perform the
+    actions.  In particular, any user-defined triggers on the referencing
+    table will be fired.  If such a trigger modifies or blocks the effect
+    of one of these commands, the end result could be to break referential
+    integrity.  It is the trigger programmer's responsibility to avoid
+    that.  (Similar problems can arise in any case where different
+    triggers are working at cross-purposes.)
+   </para>
+
    <para>
     <indexterm>
      <primary>trigger</primary>

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: MultiXact\SLRU buffers configuration
Next
From: Alexander Lakhin
Date:
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands