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. Yours, Laurenz Albe
From b6abd7dfdf1e25515ead092489efde0d239f7053 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Tue, 3 Oct 2023 09:20:54 +0200 Subject: [PATCH] Document foreign key internals Warn the user that foreign keys are implemented as triggers, and that user-defined triggers can interact with them and break referential integrity. Discussion: https://postgr.es/m/b81fe38fcc25a81be6e2e5b3fc1ff624130762fa.camel%40cybertec.at --- doc/src/sgml/ddl.sgml | 12 ++++++++++++ 1 file changed, 12 insertions(+) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 075ff32991..8016b9fd81 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1224,6 +1224,18 @@ CREATE TABLE posts ( syntax in the reference documentation for <xref linkend="sql-createtable"/>. </para> + + <note> + <para> + Foreign key constraints are implemented as system triggers in PostgreSQL. + As such, they are subject to the trigger firing rules described in + <xref linkend="trigger-definition"/>. In particular, other triggers + defined on the referencing table can cancel or modify the effects of + cascading delete or update, thereby breaking referential integrity. + This is not considered a bug, and it is the responsibility of the user + to write triggers so that such problems are avoided. + </para> + </note> </sect2> <sect2 id="ddl-constraints-exclusion"> -- 2.41.0
