PostgreSQL中基于时效、状态及关联行存在性的行删除需求

表结构与测试数据 #

create table the_table (
  id integer, root_id integer, parent_id integer, status text, ts timestamp, comment text);
insert into the_table values
(1, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, standalone'),
(2, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, root of 3,4'),
(3, 2,    null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 2, parent of 4'),
(4, 2,    3,    'OPEN',     now()-'92d'::interval, '>90 days old, open, child of 2,3'),
(5, null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, root of 6,7'),
(6, 5,    null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 5, parent of 4'),
(7, 5,    6,    'COMPLETE', now()-'10d'::interval, '<=90 days old, complete, child of 5,6' )

删除要求 #

基础删除条件 #

需要删除同时满足以下条件的记录：

• 时间戳ts早于当前时间90天（即超过90天）
• 状态为COMPLETE

例外规则 #

以下情况的记录不可删除：

• 被状态为OPEN的行通过root_id或parent_id关联
• 被未超过90天的行通过root_id或parent_id关联

示例验证 #

• id=1：满足删除条件且无关联例外，应被删除
• id=2、3：虽满足基础条件，但被OPEN状态的id=4关联，不可删除
• id=5、6：虽满足基础条件，但被未超90天的id=7关联，不可删除

实现SQL #

使用递归CTE找出所有需要保留的记录，再删除不在保留列表中的目标行：

WITH recursive keep_records AS (
    -- 第一步：先找出必须保留的核心行：OPEN状态或未超90天的行
    SELECT id, root_id, parent_id
    FROM the_table
    WHERE status = 'OPEN' OR ts >= now() - '90d'::interval
    UNION ALL
    -- 第二步：递归找出所有被核心行关联的父/根节点，这些节点也需要保留
    SELECT t.id, t.root_id, t.parent_id
    FROM the_table t
    JOIN keep_records kr ON t.id = kr.root_id OR t.id = kr.parent_id
)
DELETE FROM the_table
WHERE status = 'COMPLETE'
  AND ts < now() - '90d'::interval
  AND id NOT IN (SELECT id FROM keep_records);

逻辑解释 #

1. 递归CTEkeep_records先筛选出两类必须保留的行：OPEN状态的行、以及时间未超过90天的行；
2. 接着递归追踪这些行的root_id和parent_id，把所有关联的上级节点（父节点、根节点）加入保留列表；
3. 最后删除满足基础删除条件，但不在保留列表里的记录。

内容的提问来源于stack exchange，提问作者Nagesh