Nested Sets + PostgreSQL TRIGGER

[@ustav]

Это сколько времени надо было потратить… Огромное спасибо

[@hardtop]

Благодарен за столь подробные примеры. Ждем кода для MySQL.

[@phoinixrw]

На самом деле, все запросы универсальны для обоих баз.
Модифицированы будут только работа с переменными и команды создания триггеров.

[@mecommayou]

Сколько времени ушло на поиск решения?

[@phoinixrw]

Поиск решения финтов ушами с триггерами — день;
Решение вообще по Nested Sets — давно это было… не помню… с неделю может быть.

phoinix.ucoz.ru/publ/7

[@mecommayou]

phoinix.ucoz.ru/publ/1-1-0-1 Сейчас там комментарии скрыты почему-то, но в самой статье в примерах SQL запросов были ошибки.

[@phoinixrw]

Комментарии скрыты, т.к. спама там сыпалось очень много, устал каждый день чистить.
Примеры SQL, да, возможно ошибки есть (особенно в теоретической статье), т.к. там лежат статьи еще первой редакции.
Давно чисткой не занимался. Как-то не до этого было.
Статьи новой редакции есть, но еще не выкладывал.
Зачищу, выложу.

[@mecommayou]

www.google.ru/search?hl=ru&q=Nested+Sets Твои статьи на первых местах когда начинаешь искать что-то по NS там для неподготовленного человека и так все непросто, а еще и грабли в кустах, массы будут тебе признательны.

[@boolive]

и как быстро это работает? не лучше ли чуток модернизированным «материализованным путём» пользоваться.

[@phoinixrw]

На выборках работает быстро.
+ еще достаточно много ништяков дополнительных, например количество узлов детей, родителей, всего в дереве получается безо всяких COUNT и дополнительные запросы нужны редко.

Основная грабля — это, конечно, изменение структуры дерева, собственно, данная статья как раз и облегчает эту работу. Но обновление идет по INTEGER полям, тесты не показали особых задержек.

А с учетом того, что на порядок чаще все-таки выборка, а не изменение, выигрыш существенный.

[@galaxy]

_{Disclaimer: читал малец по диагонали}
Сумбурно как-то. Вы бы привели примеры запросов, которые такая схема делает быстрыми и удобными, а то я что-то не понял. Как эта схема помогает, например, решать типичные для древовидной структуры задачи типа «найти всех потомков данного узла» или «найти всех родителей данного узла». Или вы там что-то быстро выбираете по ключам (left_key/right_key)? В таком случае, получается — заново изобретаем индексы?
Кстати о рекурсии — в недавно вышедшей 8.4 появились рекурсивные запросы — не интересовались?
Комментарии по коду (не имеющие, впрочем, отношения к существу вопроса):

CREATE OR REPLACE FUNCTION lock_ns_tree(integer)
    RETURNS boolean AS
$BODY$
DECLARE tree_id ALIAS FOR $1;
    _id INTEGER;
BEGIN
    SELECT id
        INTO _id
        FROM ns_tree
        WHERE tree = tree_id FOR UPDATE;
    RETURN TRUE;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

1. Алиас для параметра — как-то архаично и неудобно, лучше писать сразу lock_ns_tree(tree_id integer).
2. Используйте PERFORM (PERFORM id FROM ns_tree ...), дабы не плодить ненужных переменных.
3. Функция — типичный кандидат на STABLE, VOLATILE не стоит ставить из соображений производительности.

[@tigrenok]

ERROR: SELECT FOR UPDATE is not allowed in a non-volatile function CONTEXT:

[@clops]

Много лет назад элегантно решил эту задачу следующим способом:

Есть основная «обычная» таблица foo(id, parent_id, label); на которой висит куча триггеров, которые уже «ведут» второстепенную таблицу foo_traversed(id, lft, rgt, level); таким образом никаких проблем с лочкой или непониманием со стороны нет. Запросы стандартных задачь делам либо через функции либо через LEFT JOIN