Message-ID: <a3c8f66a2d89270eef77da9ba485949a49cdd8e9.camel@cybertec.at>
Subject: Re: Trigger violates foreign key constraint
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Date: Mon, 02 Oct 2023 16:11:59 +0200
In-Reply-To: <2014740.1696254593@sss.pgh.pa.us>
References: <b81fe38fcc25a81be6e2e5b3fc1ff624130762fa.camel@cybertec.at>
	 <2014740.1696254593@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/a3c8f66a2d89270eef77da9ba485949a49cdd8e9.camel%40cybertec.at>
Precedence: bulk

On Mon, 2023-10-02 at 09:49 -0400, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETU=
RN NULL; END;';
> > CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTI=
ON silly();
>=20
> > The trigger function cancels the cascaded delete on "child", and we are=
 left with
> > a row in "child" that references no row in "parent".
>=20
> Yes.=C2=A0 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.
>=20
> There are good reasons to want triggers to be able to see and
> react to FK-driven updates, so it's unlikely that we'd want to
> revisit that design decision, even if it hadn't already stood
> for decades.

Thanks for the clarification.  I keep learning.

I didn't find anything about that in the documentation or the
READMEs in the source, but perhaps I didn't look well enough.

Yours,
Laurenz Albe