public inbox for [email protected]  

help / color / mirror / Atom feed

Automatic deletion of orphaned rows
2+ messages / 2 participants
[nested] [flat]

* Automatic deletion of orphaned rows
@ 2025-01-22 07:00  Runxi Yu <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Runxi Yu @ 2025-01-22 07:00 UTC (permalink / raw)
  To: [email protected]

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Automatic deletion of orphaned rows
@ 2025-01-22 15:11  Greg Sabino Mullane <[email protected]>
  parent: Runxi Yu <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Sabino Mullane @ 2025-01-22 15:11 UTC (permalink / raw)
  To: Runxi Yu <[email protected]>; +Cc: [email protected]

On Wed, Jan 22, 2025 at 2:00 AM Runxi Yu <[email protected]> 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

Cheers,
Greg

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-01-22 15:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-22 07:00 Automatic deletion of orphaned rows Runxi Yu <[email protected]>
2025-01-22 15:11 ` Greg Sabino Mullane <[email protected]>

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