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 1pSjGz-0007CF-4U for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 18:46:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSjGx-0004Z3-RP for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 18:46:23 +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 1pSjGx-0004Ys-C8 for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 18:46:23 +0000 Received: from mail-il1-x130.google.com ([2607:f8b0:4864:20::130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSjGu-0001NQ-Jb for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 18:46:22 +0000 Received: by mail-il1-x130.google.com with SMTP id y8so1115218ilv.1 for ; Thu, 16 Feb 2023 10:46:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ufvwU5zMhxN9EGYW0OVVc2WgP65RzHEtP8rmoAYXbiQ=; b=jBWmzenrsFoc5xMc7NZ/rr0rCWMlmcfe0KNXT1Ogc6ldTNdXodsDXvgaGLlrH1uLxO AxQoN4VBwD+pSpPsxhGc/ooM15Y2cA3Wuz2afVmNX0C2+U8PFMPxb+o4SqBtGtjgqJyT TNdojwaVgNET7HAZqwtXe4oxDHNZAPXe35scmhJDo/zXHrSmqTueNVVZwnEaQeqxZduk rdM/n0DI6Dvv9vKdDimzauvmZjoN7gToshtwdEd4cp6Q28HueWiGGzsnM/VnI5spzDq/ BEDf5DFnBn/Bca25D7DfRSXZ8ffpNEhYDCtlH2L/ymd30KwMqfzgs0QmHi4JXJR7DtVL TZaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ufvwU5zMhxN9EGYW0OVVc2WgP65RzHEtP8rmoAYXbiQ=; b=LfcDBgGye9BC3JAw2MuQTJv1CuMyLYa/ZjAGy2I7BaH/GcWgQM69OfDYBy+BYFJhBd NgGsFkXkJLo41yovFHJ5lAYDUunqJMKpCS5QzY0/jcpAhhD5G3+XeH29h8eq2luFFfnx bnfQvE5Trx2rvV3dFnGaXUpgo1ZX2JMu7vC3m/USD9/F7v+vHLrXYNh7aWgCKRRvsqQS uAIISM8sy/jxKQ9VXJNUe1D1OmvjtbLzvOyegUfEPDakKKprr4hFKzntRJECFxLnFscf EJYpN+kF+WXnl8dSDBOkoKk7JB9w3RxhsVJ3w0Yem29yqp6sBm34q+nIKZl0fDWJoswh QwxA== X-Gm-Message-State: AO0yUKWxXwxTBlklqAvHfP22mJ/J9HaC54QYdpnu4PnAX/37S/H67AQ2 7Mjf3VhxGyHJwuw8kcLdL0kAhZ2boq4= X-Google-Smtp-Source: AK7set8ULPnXcPTLYX7tB+eE780FScER/PHCssEQCpnb34NC0oj7/H+94fmEFJ30vMFy9YiYUZPcPw== X-Received: by 2002:a05:6e02:1909:b0:315:7157:805d with SMTP id w9-20020a056e02190900b003157157805dmr6825657ilu.31.1676573179336; Thu, 16 Feb 2023 10:46:19 -0800 (PST) Received: from [10.10.50.239] (72-46-51-178.lnk.ne.static.allophone.net. [72.46.51.178]) by smtp.gmail.com with ESMTPSA id z9-20020a029f09000000b003b778515852sm751232jal.168.2023.02.16.10.46.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 16 Feb 2023 10:46:18 -0800 (PST) Content-Type: multipart/alternative; boundary="------------ZnKiNKXR7GjTAGG0S2b97TkW" Message-ID: Date: Thu, 16 Feb 2023 12:46:18 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.0.2 Subject: Re: DELETE trigger, direct or indirect? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Brad White In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ZnKiNKXR7GjTAGG0S2b97TkW Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2/16/2023 12:28 PM, Dominique Devienne wrote: > On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver > wrote: > > You have two tables with list in their name, so are rows deleted from > both. Just to be clear enity_list should actually be entity_list? > > Also how are entity and enity_list related? > > > I have to confess that your questions surprise me a bit. > I thought the model was pretty clear from the SQL. > We have two entities, Foo (my entity table), and another Bar (my > entity_list table), > with Bar reference 0 or more Foos, recording which ones it references > in an association table. > > Are the values for the name field in entity and enity(entity)_list > the > same for a given entity? > > > The name of Foo and Bar are completely independent. > > > transfering the old entity name to it thanks to an UPDATE on > > entity_name, thus preserving the old name. > > How? > Again how would you determine where the action started? > > > Deleting a Foo cascades to the _member assoc-table. > If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. > I no longer care where the action started. > > > Then an INSERT trigger on entity could locate any (indexed) > "stashed" > > entity names in that extra non-FK column in entity_list_member, > > How would it locate it if  the name that defined the > FK(entity(name)) was NULL? > > > In the extra non-FK column I mentioned explicitly, in the _member > assoc-table. Another option would be to not delete the records, but add a Deleted column and mark them as deleted. Your CASCADE then becomes an UPDATE trigger. Restoring the relationship would then be a simple matter of unmarking them as deleted. I haven't tried this, but it's possible that you could hijack the DELETE trigger so the app didn't have to change how it deletes records. If you were really insistant on the app not changing to respect the deleted flag, you could add views and read from those. If you potentially have a lot of deleted records, and you have a time frame after which it would be unlikely they would be restored, then you could add a DeletedDate field. After a given amount of time do garbage cleanup on anything over that threshold. We don't have that many deletes, so we just leave them. They don't show up in the app, since they are "deleted" but we have an admin mode that can ignore the deleted flag if the user chooses and they can then undelete any records. So nothing ever gets literally deleted, but they do get archived after 18 months. And again, we have a mode where you can include archived records. In your situation, when they undelete the parent record, it could automatically undelete the children. Brad. --------------ZnKiNKXR7GjTAGG0S2b97TkW Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 2/16/2023 12:28 PM, Dominique Devienne wrote:
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
You have two tables with list in their name, so are rows deleted from
both. Just to be clear enity_list should actually be entity_list?

Also how are entity and enity_list related?

I have to confess that your questions surprise me a bit.
I thought the model was pretty clear from the SQL.
We have two entities, Foo (my entity table), and another Bar (my entity_list table),
with Bar reference 0 or more Foos, recording which ones it references in an association table.

Are the values for the name field in entity and enity(entity)_list the
same for a given entity?

The name of Foo and Bar are completely independent.
 
> transfering the old entity name to it thanks to an UPDATE on
> entity_name, thus preserving the old name.

How?
Again how would you determine where the action started?

Deleting a Foo cascades to the _member assoc-table.
If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member.
I no longer care where the action started.
 
> Then an INSERT trigger on entity could locate any (indexed) "stashed"
> entity names in that extra non-FK column in entity_list_member,

How would it locate it if  the name that defined the FK(entity(name)) was NULL?

In the extra non-FK column I mentioned explicitly, in the _member assoc-table.

Another option would be to not delete the records, but add a Deleted column and mark them as deleted.
Your CASCADE then becomes an UPDATE trigger.
Restoring the relationship would then be a simple matter of unmarking them as deleted.

I haven't tried this, but it's possible that you could hijack the DELETE trigger so the app didn't have to change how it deletes records.
If you were really insistant on the app not changing to respect the deleted flag, you could add views and read from those.

If you potentially have a lot of deleted records, and you have a time frame after which it would be unlikely they would be restored, then you could add a DeletedDate field. After a given amount of time do garbage cleanup on anything over that threshold.

We don't have that many deletes, so we just leave them. They don't show up in the app, since they are "deleted" but we have an admin mode that can ignore the deleted flag if the user chooses and they can then undelete any records.
So nothing ever gets literally deleted, but they do get archived after 18 months. And again, we have a mode where you can include archived records.

In your situation, when they undelete the parent record, it could automatically undelete the children.

Brad.
--------------ZnKiNKXR7GjTAGG0S2b97TkW--