防止和/或检测 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
  LANGUAGE plpgsql 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 FROM search_graph
      WHERE  cycle
      LIMIT  1  -- stop evaluation at first find
      )
   THEN
      RAISE EXCEPTION 'Loop detected!';
   ELSE
     RETURN NEW;
   END IF;
END
$func$;

您不需要动态 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 也必须被禁止:

An INSERT like this has to be prohibited, too:

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

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