Управление деревьями Adjacency List в PostgreSQL

Введение

Управление древовидными структурами данных (Adjacency List) на уровне PostgreSQL. Реализация на MySQL значительно сложнее или невозможна в некоторых случаях.

Правила

При работе с Adjacency List необходимо соблюдать два основных правила:

• Узел может подчиняться только существующему узлу.
• Узел не может подчиняться своему потомку (избежать циклических зависимостей).

Решения

Внешний ключ

ALTER TABLE "public"."my_tree"
    ADD CONSTRAINT "my_tree_fk" FOREIGN KEY ("pid")
        REFERENCES "public"."my_tree"("id")
        MATCH FULL
        ON DELETE CASCADE
        NOT DEFERRABLE;

Триггер на проверку циклических зависимостей

CREATE OR REPLACE FUNCTION "public"."my_tree_update_trigger" ()
    RETURNS TRIGGER AS
$body$
DECLARE
    tmp_id INTEGER;
BEGIN
    IF NEW.pid IS NOT NULL AND (NEW.pid <> OLD.pid OR OLD.pid IS NULL) THEN
        WITH RECURSIVE parents AS (
            SELECT t.id, t.pid, ARRAY[t.id] AS exist, FALSE AS cycle
            FROM my_tree AS t
            WHERE id = NEW.pid
            UNION ALL
                SELECT t.id, t.pid, exist || t.id, t.id = ANY(exist)
                FROM parents AS p, my_tree AS t
                WHERE t.id = p.pid AND NOT p.cycle
        )
        SELECT id INTO tmp_id FROM parents
        WHERE id = NEW.id AND NOT cycle LIMIT 1;

        IF tmp_id IS NOT NULL THEN
            RAISE NOTICE 'Нельзя ставить потомком родителя!';
            NEW.pid := OLD.pid;
        END IF;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

Денормализация

Для оптимизации часто запрашиваемых параметров добавляются денормализованные поля:

• level — уровень узла в дереве.
• counter — количество прямых потомков.

Триггер на вставку

CREATE OR REPLACE FUNCTION "public"."my_tree_insert_trigger" ()
    RETURNS TRIGGER AS
$body$
DECLARE
    parent my_tree;
BEGIN
    NEW.counter := 0;
    NEW.level := 0;

    IF NEW.pid IS NOT NULL OR NEW.pid > 0 THEN
        UPDATE my_tree
            SET counter = CASE WHEN counter IS NULL
                               THEN 1
                               ELSE counter + 1
                          END
            WHERE id = NEW.pid
            RETURNING * INTO parent;

        IF parent.id IS NULL THEN
            RAISE NOTICE 'ОШИБКА! Родителя с таким ID нет (%)';
            NEW.pid := 0;
        ELSE
            NEW.level := CASE WHEN parent.level IS NULL OR parent.level = 0
                              THEN 1
                              ELSE parent.level + 1
                         END;
        END IF;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

Триггер на удаление

Для управления удалением используется пользовательская переменная user_vars.tree_delete с тремя возможными значениями:

• cascade — каскадное удаление потомков.
• root — перенос потомков в корень.
• levelup — перенос потомков на уровень выше.

Конфигурация PostgreSQL

custom_variable_classes = 'user_vars'
user_vars.tree_delete = cascade

Использование

С явной транзакцией:

BEGIN;
SELECT set_config('user_vars.tree_delete', 'levelup', TRUE);
DELETE FROM my_tree WHERE id = [item.id];
COMMIT;

Без явной транзакции:

DELETE FROM my_tree
    USING (
        SELECT set_config('user_vars.tree_delete', 'root', TRUE)
        ) AS conf
    WHERE id = [item.id];