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天
• 与处于COMPLETE状态。
• ANDnot与不超过90天且COMPLETE的行直接或间接相关

示例如下：

• 应删除具有id=1的行。
• id=2和id=3是COMPLETE并且超过90天，但是由于id=4是OPEN并且具有root_id=2和parent_id=3，所以这三行（id in (2,3,4)）不应该被删除。
• id=5、id=6和id=7都是COMPLETE，但id=7不超过90天，并且有root_id=5和parent_id=6，因此这三行（id=5、id=6和id=7）不应删除。
• id=15和id=16都足够老，COMPLETE但id=17仍然是OPEN，并且小于90天。16与17没有直接关系，但它们共享15作为父行，因此不应删除这三行。

我尝试了这个answer，但有一个案例失败了：一个可移动的父节点有两个子节点，一个不可移动，一个可移动。（最后一个测试案例在这里fiddle）
删除时，我想保留整个组，直到所有成员都可删除。我尝试使用递归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 *;

型
我也试过用函数

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;

型