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' ),
(8, null, null, 'COMPLETE', now()-'10d'::interval, '<=90 days old, complete, standalone'),
(9, null, null, 'COMPLETE', now()-'10d'::interval, '<=90 days old, complete, root of 10'),
(10,9,    null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 9' ),
(11,11,   null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, parent/child of self'),
(12,null, 12,   'COMPLETE', now()-'92d'::interval, '>90 days old, complete, parent/child of self'),
(13,14,   null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, cross-parent/child of 14'),
(14,13,   null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, cross-parent/child of 13'),
(15,null, null, 'COMPLETE', now()-'92d'::interval, '>90 days old, complete, parent of 16,17'),
(16,null, 15,   'COMPLETE', now()-'92d'::interval, '>90 days old, complete, child of 15'),
(17,null, 15,   'OPEN',     now()-'10d'::interval, '<=90 days old, open, child of 15');

删除需求 #

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

• 数据已超过90天（ts < NOW() - '90 days'::interval）
• 状态为COMPLETE
• 未直接或间接关联到以下两类行：
  1. 未超过90天的行
  2. 状态非COMPLETE的行

示例说明 #

• id=1的行应被删除：满足前两个条件，且无关联需保留的节点
• id=2、3不应被删除：虽满足前两个条件，但关联了OPEN状态的id=4
• id=5、6、7不应被删除：id=7未超90天，三者属于同一关联链
• id=15、16不应被删除：关联了未超90天的OPEN状态id=17

原有方案的问题 #

递归CTE查询 #

WITH recursive RecursiveHierarchy AS (
  SELECT id, root_id, parent_id, status, ts, comment
  FROM the_table
  WHERE status = 'OPEN'
  UNION ALL
  SELECT t.id, t.root_id, t.parent_id, t.status, t.ts, t.comment
  FROM the_table t
  JOIN RecursiveHierarchy r ON (  ( 'OPEN'=t.status
                         OR now()-t.ts <= '90d'::interval   ) 
                    AND (   t.id IN(r.root_id,r.parent_id)
                         OR r.id IN(t.root_id,t.parent_id) ) ) 
)
DELETE FROM the_table
WHERE ts < NOW() - '90 days'::interval
AND status = 'COMPLETE'
AND id NOT IN (SELECT id FROM RecursiveHierarchy)
RETURNING *;

问题：初始锚点仅包含OPEN状态的节点，漏掉了未超90天的COMPLETE节点（如id=7、9），导致这些节点关联的父节点（如id=5、10）未被纳入保护列表，会出现误删。同时递归条件中额外限制了t的状态或时间，导致部分关联节点无法被正确加入保护列表。

PL/pgSQL函数 #

CREATE OR REPLACE FUNCTION delete_old_records()
RETURNS VOID AS $$
DECLARE
  record_to_delete the_table;
BEGIN
  FOR record_to_delete IN SELECT * FROM the_table
  WHERE ts < NOW() - '90 days'::interval AND status = 'COMPLETE'
  LOOP
    IF NOT EXISTS (
      SELECT 1 FROM the_table
      WHERE id = record_to_delete.id
        OR root_id = record_to_delete.id
        OR parent_id = record_to_delete.id
    ) THEN
      DELETE FROM the_table WHERE id = record_to_delete.id;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

问题：EXISTS条件中的id = record_to_delete.id永远为真，导致永远不会执行删除。此外逻辑仅检查直接关联，未覆盖间接关联的场景，无法处理层级链中的复杂关联。

正确实现方案 #

使用递归CTE先找出所有**需要保护（不能删除）**的节点，再删除符合条件且不在保护列表中的记录：

WITH RECURSIVE protected_nodes AS (
    -- 初始锚点：本身需要保留的节点（非COMPLETE状态 或 未超过90天）
    SELECT id, root_id, parent_id
    FROM the_table
    WHERE status != 'COMPLETE' OR now() - ts <= '90d'::interval
    UNION ALL
    -- 递归找出所有与protected_nodes有层级关联的节点（父/子节点）
    SELECT t.id, t.root_id, t.parent_id
    FROM the_table t
    JOIN protected_nodes p ON 
        -- 当前节点是保护节点的父节点（保护节点的root/parent指向当前节点）
        (p.root_id = t.id OR p.parent_id = t.id)
        -- 或者保护节点是当前节点的父节点（当前节点的root/parent指向保护节点）
        OR (t.root_id = p.id OR t.parent_id = p.id)
    WHERE t.id NOT IN (SELECT id FROM protected_nodes) -- 避免重复处理
)
DELETE FROM the_table
WHERE ts < now() - '90d'::interval
  AND status = 'COMPLETE'
  AND id NOT IN (SELECT id FROM protected_nodes)
RETURNING *;

逻辑说明 #

1. protected_nodes递归CTE：
   • 第一步锚定所有本身不能删除的节点：状态不是COMPLETE，或者未超过90天的记录。
   • 第二步递归遍历所有与锚点节点有层级关联的节点（无论父节点还是子节点），将这些节点也纳入保护列表——因为只要关联链中存在一个需要保留的节点，整个链上的节点都不能删除。
2. 删除操作：仅删除满足“超90天+COMPLETE状态”且不在保护列表中的节点，完全符合需求。

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