Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSgpC-0005ow-4Q for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 16:09:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSgpA-0007UZ-IK for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 16:09:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSgpA-0007UQ-7F for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 16:09:32 +0000 Received: from mout-u-107.mailbox.org ([80.241.59.207]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSgp5-0008KP-RY for pgsql-general@postgresql.org; Thu, 16 Feb 2023 16:09:31 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [10.196.197.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4PHfyp3c64z9sS4; Thu, 16 Feb 2023 17:09:22 +0100 (CET) Date: Thu, 16 Feb 2023 17:09:21 +0100 (CET) From: Erik Wienhold To: Dominique Devienne , pgsql-general@postgresql.org Message-ID: <1955533673.226407.1676563761989@office.mailbox.org> In-Reply-To: References: Subject: Re: DELETE trigger, direct or indirect? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 16/02/2023 14:23 CET Dominique Devienne wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, with > ON DELETE CASCADE, to preserve referential integrity. But we apparently > also need to preserve the severed reference (by natural key, i.e. its name), > to later on reconnect the two entities after-the-fact, should the parent > row re-appear later on (in the same transaction or not it still unclear). > > To achieve this weird requirement, I'd like to know if it is possible in an > ON DELETE trigger to know whether the deletion is coming from a direct-DELETE > in the "child table", or whether the deletion is coming from the "parent > table" CASCADEd to the child table. Not to my knowledge. ON DELETE CASCADE behaves like a manual DELETE on the child table that happens before the DELETE on the parent table. The process you describe shows that it's not known until the end of the transaction which parent rows can be deleted. You can instead track the parent rows as candidates for deletion in a temp table. Insert the primary key of parent rows if you deem them deletable and delete the primary key if you detect the opposite. At the end the temp table only contains IDs of parent rows that can be deleted for sure. -- Erik