Обсуждение: Automatic deletion of orphaned rows
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
On Wed, Jan 22, 2025 at 2:00 AM 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.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Wednesday, January 22, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
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.
The description was correct even though using probably imprecise terminology. The basic goal is to delete childless parents.
David J.
On Wed, Jan 22, 2025 at 2:00 AM Runxi Yu <me@runxiyu.org> wrote:
I therefore propose a feature, to be able to specify in a table schema 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
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
On Wed, Jan 22, 2025 at 9:37 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, January 22, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
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.The description was correct even though using probably imprecise terminology. The basic goal is to delete childless parents.
That's kinda what I thought he wrote, but it's so far beyond "normal" that I dismissed the possibility. Parents are allowed to not have children, after all.
Is there ANY DBMS with a built-in reverse FK "parents must have children" feature?
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!