PostgreSQL ltree: обеспечение целостности данных

Здравствуйте. Это версия моей статьи на русском языке. Она у меня первая, поэтому прошу не судить строго.

Одним из способов хранения древовидных структур в реляционных СУБД является “материализованный путь”. В интернете можно найти множество описаний этого и других способов, следует выбирать исходя из вашей задачи. 

В PostgreSQL существует специальное расширение ltree, предоставляющее дополнительные инструменты для работы с метками и путями. О нем и о решении вопросов, связанных с консистентностью (целостностью) данных, и поговорим. 

Итак, задача: сделать таблицу item с полем path типа ltree для удобной работы с деревом, а также обеспечить целостность данных на уровне базы. 

Терминология:

• метка — строка, состоящая из строчных и заглавных букв, цифр и знаков подчеркивания. Я в качестве метки буду использовать цифровой id записи.

• путь — список меток, разделенных точкой. Первой меткой должен быть корневой элемент дерева, а последней — метка самой записи.

Создание таблицы

В качестве примера буду использовать тестовую таблицу item, содержащую поля  id(serial) и path(ltree). Для начала включим расширение и создадим таблицу:

CREATE EXTENSION IF NOT EXISTS ltree;  create table public.item (     id  serial  constraint item_pk primary key,     path ltree default ((lastval())::text)::ltree not null constraint item_pk2 unique );

Поле path не может быть пустым, т.к. если даже элемент корневой, в этом поле должен содержаться id.

В моей задаче планируется большая вложенность и много item, которые будут часто добавляться и перемещаться. Я выбрал тип serial в качестве первичного ключа, т.к. использование буквенно-цифровых отрицательно скажется на объеме базы при росте кол-ва записей и вложенности. Если у вас планируется небольшое количество записей или небольшая вложенность, возможно, использование алфавитно-буквенных идентификаторов вам подойдет больше, т.к. у этого способа есть как минимум один большой плюс — возможность генерировать id на клиенте. В path нельзя положить UUID, т.к. данные в ltree не могут содержать дефис.

Вставка и обновление записей

Таблица создана, попробуем добавить запись. Первая запись будет корневым item, поэтому path будет состоять только из id записи. Для получения id записи в запросе insert воспользуемся функцией lastval(). Выполним запрос трижды:

insert into item(path) values (lastval()::text::ltree);

В таблице появились три записи с корневыми узлами деревьев (id = path).

select * from item;  | id | path | | 1 | 1 | | 2 | 2 | | 3 | 3 |

Теперь попробуем вставить строку с ключом, состоящим из несуществующих меток и проверим, что запрос выполнился удачно:

insert into item (path) values ('12.43.555.22.542');  select * from item;  | id | path | | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 12.43.555.22.542 | 

Теперь данные в таблице не консистентны, т.к. появилась запись с путем path, состоящим из несуществующих меток. С таким же успехом мы можем записать в поле path и “зацикленное” значение пути, вроде “12.42.12” и что угодно еще. Так произошло потому, что без дополнительных действий PostgreSQL позволяет класть в поле path любое значение, состоящее из разрешенных символов. Решим эти проблемы при помощи триггерных функций.

Создадим функцию:

CREATE OR REPLACE FUNCTION item_before_update_insert_check_path() RETURNS TRIGGER AS $item_before_update_insert_check_path$ DECLARE     parentPath  ltree;     curLabel ltree;     parentId text; BEGIN     parentPath := subpath(NEW.path, 0, -1);     curLabel := subpath(NEW.path, -1);      -- последняя метка в пути должна равняться id     if (curLabel::text != NEW.id::text) THEN         RAISE EXCEPTION 'The last path label % must be equal id %', curLabel::text, NEW.id::text;     end if;      -- должна существовать родительская запись с подходящим путем, если новая запись не корневая     if (parentPath != '') THEN         parentId := (select id from item where path = parentPath);         if (parentId IS NULL) THEN             RAISE EXCEPTION 'Parent item with path % not found', parentPath;         end if;     end if;      RETURN NEW; END; $item_before_update_insert_check_path$ LANGUAGE plpgsql; 

И сделаем так, чтобы она выполнялась при вставке записи или при изменении path:

CREATE TRIGGER tr_item_before_update_insert_check_path     BEFORE         INSERT OR         UPDATE OF path ON item     FOR EACH ROW EXECUTE FUNCTION item_before_update_insert_check_path();

Теперь не получится при вставке или изменении path установить значение, нарушающее консистентность данных в таблице. Для удобства я сделал запись с id=4 корневой, как и остальные. Идем дальше. Сделаем item c id=1 родителем id=2, а затем потом положим 1 в 3:

update item set path = '1.2' where id = 2; update item set path = '3.1' where id = 1; select * from item;  | id | path | | 3 | 3 | | 2 | 1.2 | | 4 | 4 | | 1 | 3.1 |

И снова нарушена целостность. У записи с id 2 неверный path: вместо ‘1.2’ должен быть ‘3.1.2’. Это особенность материализованного списка: при изменении пути элемента надо изменять пути всех дочерних элементов. Обеспечим это. Сначала сделаем снова все записи корневыми:

update item set path = id::text::ltree;

Затем добавим новый триггер:

CREATE OR REPLACE FUNCTION item_after_update_set_children_path() RETURNS TRIGGER AS $item_after_update_set_children_path$ BEGIN      IF (NEW.path != OLD.path) THEN         UPDATE item         SET path = NEW.path || subpath(path, nlevel(OLD.path))         WHERE path <@ OLD.path;     END IF;      RETURN NULL; END; $item_after_update_set_children_path$ LANGUAGE plpgsql;   CREATE TRIGGER tr_update_children_item_path     AFTER UPDATE OF path ON item     FOR EACH ROW EXECUTE FUNCTION item_after_update_set_children_path();

а затем снова выполним действия с путями записей 2 и 1:

update item set path = '1.2' where id = 2; update item set path = '3.1' where id = 1; select * from item;  | id | path | | 3 | 3 | | 4 | 4 | | 1 | 3.1 | | 2 | 3.1.2 |

Теперь path дочерних узлов обновляются с path родительского узла. Получилось своего рода каскадное обновление.

Удаление записей

Для того, чтобы не допустить удаление записей, имеющих дочерние записи, добавим еще одну функцию и триггер:

CREATE OR REPLACE FUNCTION item_before_delete_check_children() RETURNS TRIGGER AS $item_check_children_before_delete$ DECLARE     childrenCount bigint; BEGIN     SELECT COUNT(*) into childrenCount     FROM item WHERE path <@ OLD.path AND id != OLD.id;      IF (childrenCount > 0) THEN         RAISE EXCEPTION 'Deleting items containing child items by the path field is prohibited.';     end if; RETURN OLD; END; $item_check_children_before_delete$ LANGUAGE plpgsql;   CREATE TRIGGER tr_item_check_children_before_delete     BEFORE DELETE ON item     FOR EACH ROW EXECUTE FUNCTION item_before_delete_check_children();

Готово.

Итого

Для обеспечения консистентности данных в таблице, содержащей поле ltree, нужно:

1. Сделать поле path not null;

2. Добавить уникальный индекс для path;

3. Добавить триггеры:

   • BEFORE INSERT и BEFORE UPDATE, проверяющие, что последняя метка в поле path является id записи и что в таблице существует запись с path, соответствующим path записи без последней метки.

   • AFTER UPDATE, обновляющий path всех дочерних записей при изменении path (перемещении).

   • BEFORE DELETE, запрещающий удалять записи, содержащие дочерние записи.

Если у вас есть, что добавить, пожалуйста, оставьте комментарий.