MIME-Version: 1.0
References: <D78EPGDRGTYO.1C8SB09QLCOHE@runxiyu.org>
In-Reply-To: <D78EPGDRGTYO.1C8SB09QLCOHE@runxiyu.org>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 22 Jan 2025 09:03:06 -0500
Message-ID: <CANzqJaAsAY_u-OoZHojfk6E2bjjndqNEu2qNqy7-Xi-CPbe=_g@mail.gmail.com>
Subject: Re: Automatic deletion of orphaned rows
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008cd209062c4bf4f8"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAsAY_u-OoZHojfk6E2bjjndqNEu2qNqy7-Xi-CPbe%3D_g%40mail.gmail.com>
Precedence: bulk

--0000000000008cd209062c4bf4f8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 22, 2025 at 2:00=E2=80=AFAM Runxi Yu <me@runxiyu.org> wrote:

> Hi,
>
> While writing a new program, I encountered the following:
>
> I have three tables: A, B, and X. Rows in X are referenced by A and/or B
> via foreign keys, one or more times. I would like to delete all orphaned
> rows in X, i.e. a row in X is deleted if and only if it is no longer
> referenced by any row in A or B. (When inserting these rows, I would
> insert X first, then the reference in A or B, in the same transaction.)
>
> To the best of my knowledge, there is no such functionality natively
> built into PostgreSQL. Alternatives include (1) using triggers or (2)
> using application logic. Both would involve locking the row in table X,
> and since I don't see a native "reference count" feature in PostgreSQL,
> the reference count would have to be maintained as a field in X. Both of
> these alternatives could get somewhat messy. In more complex schemas
> where circular references may be involved and a mark-and-sweep garbage
> collector is preferred, this would be even more difficult to implement.
>
> I therefore propose a feature, to be able to specify in a table schema
> that a row should be deleted if orphaned.


For one thing, rows *can't* be orphaned if there's a foreign key
reference.  That's the whole point of creating a foreign key.

As to automatically deleting children, ON DELETE CASCADE has been a feature
of foreign keys for at least 20 years.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000008cd209062c4bf4f8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jan 22, 2025 at 2:00=E2=80=AFAM R=
unxi Yu &lt;<a href=3D"mailto:me@runxiyu.org">me@runxiyu.org</a>&gt; wrote:=
</div><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">Hi,<br>
<br>
While writing a new program, I encountered the following:<br>
<br>
I have three tables: A, B, and X. Rows in X are referenced by A and/or B<br=
>
via foreign keys, one or more times. I would like to delete all orphaned<br=
>
rows in X, i.e. a row in X is deleted if and only if it is no longer<br>
referenced by any row in A or B. (When inserting these rows, I would<br>
insert X first, then the reference in A or B, in the same transaction.)<br>
<br>
To the best of my knowledge, there is no such functionality natively<br>
built into PostgreSQL. Alternatives include (1) using triggers or (2)<br>
using application logic. Both would involve locking the row in table X,<br>
and since I don&#39;t see a native &quot;reference count&quot; feature in P=
ostgreSQL,<br>
the reference count would have to be maintained as a field in X. Both of<br=
>
these alternatives could get somewhat messy. In more complex schemas<br>
where circular references may be involved and a mark-and-sweep garbage<br>
collector is preferred, this would be even more difficult to implement.<br>
<br>
I therefore propose a feature, to be able to specify in a table schema<br>
that a row should be deleted if orphaned.</blockquote><div><br></div><div>F=
or one thing, rows <b>can&#39;t</b>=C2=A0be orphaned if there&#39;s a forei=
gn key reference.=C2=A0 That&#39;s the whole point of creating a foreign ke=
y.</div><div><br></div><div>As to automatically deleting children, ON DELET=
E CASCADE has been a feature of foreign keys=C2=A0for at least 20 years.</d=
iv></div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><b=
r><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;=
Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.=
<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000008cd209062c4bf4f8--