СУБД в этом отношении работают по тем же принципам "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и уронить всю базу целиком, съев всю доступную память. Поэтому защита от бесконечной рекурсии обязанность самого разработчика.
В PostgreSQL возможность использовать рекурсивные запросы через
WITH RECURSIVE появилась еще в незапамятные
времена версии 8.4, но до сих пор можно регулярно встретить
потенциально-уязвимые беззащитные запросы. Как избавить себя от
проблем подобного рода?Не писать рекурсивные запросы
А писать нерекурсивные. С уважением, Ваш К.О.На самом деле, PostgreSQL предоставляет достаточно большое количество функционала, которым можно воспользоваться, чтобы не применять рекурсию.
Использовать принципиально другой подход к задаче
Иногда можно просто взглянуть на задачу с другой стороны. Пример такой ситуации я приводил в статье SQL HowTo: 1000 и один способ агрегации перемножение набора чисел без применения пользовательских агрегатных функций:
WITH RECURSIVE src AS ( SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr), T(i, val) AS ( SELECT 1::bigint , 1UNION ALL SELECT i + 1 , val * arr[i] FROM T , src WHERE i <= array_length(arr, 1))SELECT valFROM TORDER BY -- отбор финального результата i DESCLIMIT 1;
Такой запрос можно заменить на вариант от знатоков математики:
WITH src AS ( SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT exp(sum(ln(prime)))::integer valFROM src;
Использовать generate_series вместо циклов
Допустим, перед нами стоит задача сгенерировать все возможные префиксы для строки
'abcdefgh':
WITH RECURSIVE T AS ( SELECT 'abcdefgh' strUNION ALL SELECT substr(str, 1, length(str) - 1) FROM T WHERE length(str) > 1)TABLE T;
Точно-точно тут нужна рекурсия?.. Если воспользоваться
LATERAL и generate_series, то даже CTE не
понадобятся:
SELECT substr(str, 1, ln) strFROM (VALUES('abcdefgh')) T(str), LATERAL( SELECT generate_series(length(str), 1, -1) ln ) X;
Изменить структуру БД
Например, у вас есть таблица сообщений форума со связями кто-кому ответил или тред в социальной сети:
CREATE TABLE message( message_id uuid PRIMARY KEY, reply_to uuid REFERENCES message, body text);CREATE INDEX ON message(reply_to);
Ну и типовой запрос загрузки всех сообщений по одной теме выглядит примерно так:
WITH RECURSIVE T AS ( SELECT * FROM message WHERE message_id = $1UNION ALL SELECT m.* FROM T JOIN message m ON m.reply_to = T.message_id)TABLE T;
Но раз у нас всегда нужна вся тема от корневого сообщения, то почему бы нам не добавлять его идентификатор в каждую запись автоматом?
-- добавим поле с общим идентификатором темы и индекс на негоALTER TABLE message ADD COLUMN theme_id uuid;CREATE INDEX ON message(theme_id);-- инициализируем идентификатор темы в триггере при вставкеCREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$BEGIN NEW.theme_id = CASE WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события ELSE ( -- или из сообщения, на которое отвечаем SELECT theme_id FROM message WHERE message_id = NEW.reply_to ) END; RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER ins BEFORE INSERT ON message FOR EACH ROW EXECUTE PROCEDURE ins();
Теперь весь наш рекурсивный запрос может быть сведен всего лишь к вот такому:
SELECT *FROM messageWHERE theme_id = $1;
Использовать прикладные ограничители
Если поменять структуру базы мы не в силах по каким-то причинам, давайте посмотрим, на что можно опереться, чтобы даже наличие ошибки в данных не приводило к бесконечному выполнению рекурсии.
Счетчик глубины рекурсии
Просто увеличиваем счетчик на единицу на каждом шаге рекурсии вплоть до момента достижения предела, который мы считаем заведомо неадекватным:
WITH RECURSIVE T AS ( SELECT 0 i ...UNION ALL SELECT i + 1 ... WHERE T.i < 64 -- предел)
Pro: При попытке зацикливания мы все равно сделаем не более указанного лимита итераций вглубь.
Contra: Нет гарантии, что мы не обработаем повторно одну и ту же запись например, на глубине 15 и 25, ну и дальше будет каждые +10. Да и про вширь никто ничего не обещал.
Формально, такая рекурсия не будет бесконечной, но если на каждом шаге количество записей увеличивается по экспоненте, мы все хорошо знаем, чем это кончается
Хранитель пути
Поочередно дописываем все встретившиеся нам по пути рекурсии идентификаторы объектов в массив, который является уникальным путем до него:
WITH RECURSIVE T AS ( SELECT ARRAY[id] path ...UNION ALL SELECT path || id ... WHERE id <> ALL(T.path) -- не совпадает ни с одним из)
Pro: При наличии цикла в данных мы абсолютно точно не станем обрабатывать повторно одну и ту же запись в рамках одного пути.
Contra: Но при этом мы можем обойти, буквально, все записи, так и не повторившись.
Ограничение длины пути
Чтобы избежать ситуации блуждания рекурсии на непонятной глубине, мы можем скомбинировать два предыдущих метода. Или, если не хотим поддерживать лишних полей, дополнить условие продолжения рекурсии оценкой длины пути:
WITH RECURSIVE T AS ( SELECT ARRAY[id] path ...UNION ALL SELECT path || id ... WHERE id <> ALL(T.path) AND array_length(T.path, 1) < 10)
Выбирайте способ на свой вкус!
