预防和/或检测postgres中的周期 [英] Prevent and/or detect cycles in postgres

问题描述

假定如下所示的模式:

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

此外，我们假设存在以下数据:

Further, let's assume the following data is present:

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

是否有一种方法可以防止创建循环?示例:

Is there a way to prevent cycles from being created? Example:

UPDATE node SET parentid = 2 WHERE id = 1;

这将创建一个1->2->1->...

推荐答案

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

Your trigger simplified and optimized, should be considerably faster:

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，不需要计数，不需要所有列，也不需要为每一行测试整个表.

You don't need dynamic SQL, you don't need to count, you don't need all the columns and you don't need to test the whole table for every single row.

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

也必须禁止这样的INSERT:

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

这篇关于预防和/或检测postgres中的周期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！