pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brad White <b55white@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: DELETE trigger, direct or indirect?
Date: Thu, 16 Feb 2023 12:46:18 -0600
Message-ID: <c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com> (raw)
In-Reply-To: <CAFCRh--E-JC244cGOhVxmroQJaGNwbCp+a9a5soRpoe5-4aLYQ@mail.gmail.com>
References: <CAFCRh-_aJjgHvjbE4OvNHMAWygdJAC026_rgGYj1kfTmB-EvJA@mail.gmail.com>
	<c0b73262-1afc-842b-1653-1671a4f9e7a5@aklaver.com>
	<CAKFQuwZ-f+ukUkai64mj_SFFSW_WC0nr+1SmzEkvauf+qASfhg@mail.gmail.com>
	<be552356-ca68-7ce6-bafe-50a82450952c@aklaver.com>
	<CAFCRh-_RKx9Dj8F-DikmAbxBdb0=qu=RdYwxUibFwVj4SQ9X2Q@mail.gmail.com>
	<da92a2f9-1257-ed85-917b-da1f37f9caf0@aklaver.com>
	<CAFCRh--E-JC244cGOhVxmroQJaGNwbCp+a9a5soRpoe5-4aLYQ@mail.gmail.com>

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.

view thread (10+ messages)  latest in thread

Message-ID: <c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com>
Permalink:  ../c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com/
Also on:    postgresql.org/message-id/c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: b55white@gmail.com
  Subject: Re: DELETE trigger, direct or indirect?
  In-Reply-To: <c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com>
  References: <CAFCRh-_aJjgHvjbE4OvNHMAWygdJAC026_rgGYj1kfTmB-EvJA@mail.gmail.com> <c0b73262-1afc-842b-1653-1671a4f9e7a5@aklaver.com> <CAKFQuwZ-f+ukUkai64mj_SFFSW_WC0nr+1SmzEkvauf+qASfhg@mail.gmail.com> <be552356-ca68-7ce6-bafe-50a82450952c@aklaver.com> <CAFCRh-_RKx9Dj8F-DikmAbxBdb0=qu=RdYwxUibFwVj4SQ9X2Q@mail.gmail.com> <da92a2f9-1257-ed85-917b-da1f37f9caf0@aklaver.com> <CAFCRh--E-JC244cGOhVxmroQJaGNwbCp+a9a5soRpoe5-4aLYQ@mail.gmail.com> <c704a4b8-8d12-a51d-a3b5-79ccdbd1f2c2@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox