Subject: Automatic deletion of orphaned rows

From: "Runxi Yu" <[email-masked]>
Date: Wed, 22 Jan 2025 15:00:28 +0800

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.

--
Best regards,

Runxi Yu (they/them)
Y11 Student
YK Pao School
https://runxiyu.org

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 22 Jan 2025 10:11:26 -0500

> that a row should be deleted if orphaned.

I think you mean "childless" rows, as "orphaned" has a different meaning
traditionally.

When and how would this deletion take place? And why not just run the
delete yourself?
It would help to show us exactly the behavior you want. Here's some sample
tables we
can use:

create table parent( id int primary key );

create table kid( refid int references parent(id) );

insert into parent values (1),(2),(3);

insert into kid values (1);

-- remove any rows non-referenced rows (aka childless)
delete from parent where not exists (select 1 from kid where refid=parent.id
);

select * from parent;
 id
----
  1

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support