postgresql - 预防和/或检测postgres中的周期

假设架构如下：

CREATE TABLE node (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR,
  parentid INT REFERENCES node(id)
);

此外，假设存在以下数据：

INSERT INTO node (name,parentid) VALUES
('A',NULL),
('B',1),
('C',1);

有没有办法防止循环被创建？例子：

UPDATE node SET parentid = 2 WHERE id = 1;

这将创建一个1->2->1->...的循环

最佳答案

您的触发器经过简化和优化，应该更快：

CREATE OR REPLACE FUNCTION detect_cycle()
  RETURNS TRIGGER AS
$func$
BEGIN

IF EXISTS (
   WITH RECURSIVE search_graph(parentid, path, cycle) AS ( -- relevant columns
       -- check ahead, makes 1 step less
      SELECT g.parentid, ARRAY[g.id, g.parentid], (g.id = g.parentid)
      FROM   node g
      WHERE  g.id = NEW.id  -- only test starting from new row

      UNION ALL
      SELECT g.parentid, sg.path || g.parentid, g.parentid = ANY(sg.path)
      FROM   search_graph sg
      JOIN   node g ON g.id = sg.parentid
      WHERE  NOT sg.cycle
      )
   SELECT 1
   FROM   search_graph
   WHERE  cycle
   LIMIT  1  -- stop evalutation at first find
   )
THEN
   RAISE EXCEPTION 'Loop detected!';
ELSE
  RETURN NEW;
END IF;

END
$func$ LANGUAGE plpgsql;

不需要动态SQL，不需要计数，不需要所有列，也不需要为每一行测试整个表。

CREATE TRIGGER detect_cycle_after_update
AFTER INSERT OR UPDATE ON node
FOR EACH ROW EXECUTE PROCEDURE detect_cycle();

这样的行为也必须被禁止：

INSERT INTO node (id, name,parentid) VALUES (8,'D',9), (9,'E',8);