Предполагая схему, подобную следующей:
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
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, вам не нужно считать, вам не нужны все столбцы и вам не нужно тестировать всю таблицу для каждой отдельной строки.
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);
linkage_overridden. Если это true, то внешнее ограничение не будет использоваться, и даже если проверка parent_id == id верна, цикла не будет. Как такая проверка будет работать здесь? Я пытался обновить условие g.parentid = ANY(sg.path) с помощью: g.parentid = ANY(sg.path) and g.linkage_overridden = false, но это не работает.
- person Rohit Jain; 05.01.2021
Чтобы ответить на мой собственный вопрос, я придумал триггер, который предотвращает это:
CREATE OR REPLACE FUNCTION detect_cycle() RETURNS TRIGGER AS
$func$
DECLARE
loops INTEGER;
BEGIN
EXECUTE 'WITH RECURSIVE search_graph(id, parentid, name, depth, path, cycle) AS (
SELECT g.id, g.parentid, g.name, 1,
ARRAY[g.id],
false
FROM node g
UNION ALL
SELECT g.id, g.parentid, g.name, sg.depth + 1,
path || g.id,
g.id = ANY(path)
FROM node g, search_graph sg
WHERE g.id = sg.parentid AND NOT cycle
)
SELECT count(*) FROM search_graph where cycle = TRUE' INTO loops;
IF loops > 0 THEN
RAISE EXCEPTION 'Loop detected!';
ELSE
RETURN NEW;
END IF;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER detect_cycle_after_update
AFTER UPDATE ON node
FOR EACH ROW EXECUTE PROCEDURE detect_cycle();
Итак, если вы попытаетесь создать цикл, как в вопросе:
UPDATE node SET parentid = 2 WHERE id = 1;
Вы получаете EXCEPTION:
ERROR: Loop detected!
Хотя текущий принятый ответ @Erwin Brandstetter в порядке, когда вы обрабатываете одно обновление/вставку за раз, он все же может дать сбой при рассмотрении одновременного выполнения.
Предположим, что содержимое таблицы определяется
INSERT INTO node VALUES
(1, 'A', NULL),
(2, 'B', 1),
(3, 'C', NULL),
(4, 'D', 3);
а затем в одной транзакции выполнить
-- transaction A
UPDATE node SET parentid = 2 where id = 3;
и в другом
-- transaction B
UPDATE node SET parentid = 4 where id = 1;
Обе команды UPDATE завершатся успешно, и впоследствии вы сможете зафиксировать обе транзакции.
-- transaction A
COMMIT;
-- transaction B
COMMIT;
Тогда у вас будет цикл 1->4->3->2->1 в таблице. Чтобы заставить его работать, вам придется либо использовать уровень изоляции SERIALIZABLE, либо использовать явную блокировку в триггере.
