Триггер

Рекурсия очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия вглубь. Но неконтролируемая рекурсия зло, которое может приводить или к бесконечному выполнению процесса, или (что случается чаще) к выжиранию всей доступной памяти.


СУБД в этом отношении работают по тем же принципам "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и уронить всю базу целиком, съев всю доступную память. Поэтому защита от бесконечной рекурсии обязанность самого разработчика.

В 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)

Выбирайте способ на свой вкус!

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

Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:

• накрутка serial при ON CONFLICT
• накрутка счетчика транзакций

Накрутка serial при ON CONFLICT

Давайте представим, что нам понадобилась небольшая таблица-словарь на пару десятков тысяч записей что-то вроде списка форматов ошибок PostgreSQL.

Наше приложение (или все-таки разработчики?) любит суррогатные ключи, поэтому сразу добавим в таблицу в качестве PRIMARY KEY автоинкремент-поле с типом serial. Точнее, smallserial ведь мы точно знаем, что строк будет не больше 2¹⁵:

CREATE TABLE tbl(  pk    smallserial      PRIMARY KEY, val    integer      UNIQUE);

Пытаться вставлять данные в него мы иногда будем, но новых среди них будет немного. Поэтому для удобства вставки, чтобы не заниматься обработкой исключений уникальности в своем коде, воспользуемся появившимся с версии 9.5 функционалом INSERT ... ON CONFLICT ...:

INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;-- 1 строкаINSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING;-- 0 строк, и никаких ошибок!

Теперь оставим наше приложение спокойно работать, и Вот ровно с такой ошибкой к нам и прибегут через несколько дней или недель:

ERROR:  nextval: reached maximum value of sequence "tbl_pk_seq" (32767)

И потом начнут приходить все чаще и чаще. Но как появилось столько записей? Почему не сработал ON CONFLICT?

Дело в том, что они и не появились. Давайте еще раз с нуля посмотрим на происходящее в нашей таблице:

TRUNCATE TABLE tbl RESTART IDENTITY;INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 1, val = 1INSERT INTO tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 0 строкINSERT INTO tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 3, val = 2

Как так pk = 3, ведь вставилось всего 2 строки? Мы запутались


На самом-то деле, все объясняется просто. Посмотрим внимательно, что из себя представляет наша таблица:

_tmp=# \d tbl                             Table "public.tbl" Column |   Type   | Collation | Nullable |             Default--------+----------+-----------+----------+--------------------------------- pk     | smallint |           | not null | nextval('tbl_pk_seq'::regclass) val    | integer  |           |          |Indexes:    "tbl_pkey" PRIMARY KEY, btree (pk)    "tbl_val_key" UNIQUE CONSTRAINT, btree (val)

Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД). В текущей реализации запись:

CREATE TABLE имя_таблицы (    имя_столбца SERIAL);

равнозначна следующим командам:

CREATE SEQUENCE имя_таблицы_имя_столбца_seq AS integer;CREATE TABLE имя_таблицы (    имя_столбца integer NOT NULL DEFAULT nextval('имя_таблицы_имя_столбца_seq'));ALTER SEQUENCE имя_таблицы_имя_столбца_seq OWNED BY имя_таблицы.имя_столбца;

То есть при определении такого типа создаётся целочисленный столбец со значением по умолчанию, извлекаемым из генератора последовательности.

То есть наш smallserial превратился в тыкву в поле smallint с DEFAULT-значением из последовательности tbl_pk_seq.

А последовательность штука нетранзакционная:

Значение, выделенное из последовательности, считается задействованным, даже если строку с этим значением не удалось вставить в таблицу. Это может произойти, например, при откате транзакции, добавляющей данные.

То есть мы сначала сгенерировали DEFAULT-значение, использовали значение pk = 2, а потом его не вставили в таблицу из-за конфликта уникальности val, скрыв проблему с помощью ON CONFLICT DO NOTHING. И после очередной такой попытки у нас просто кончилась последовательность.

Что делать?

• хорошо
  Стараться не использовать лишние суррогатные ключи в таблицах, где уникальный ключ и так уже есть.
• просто
  Сконвертировать поле и вместо smallserial использовать serial или bigserial это позволит продлить агонию приложения на месяцы или даже годы.
• разумно
  Не использовать serial и ON CONFLICT на таблицах с ожидаемо существенным количеством конфликтующих вставок.
• странно
  Написать триггер INSTEAD OF для аналогичного по структуре VIEW (или можно хранимую процедуру, но мы ведь не ищем легких путей).

Давайте в научно-познавательных целях попробуем собрать последний вариант:

CREATE TABLE tbl(  pk    smallserial      PRIMARY KEY, val    integer      UNIQUE);-- отвязываем DEFAULTALTER TABLE tbl ALTER COLUMN pk DROP DEFAULT;-- создаем "промежуточное" VIEWCREATE VIEW _tbl AS TABLE tbl;CREATE OR REPLACE FUNCTION tbl_serial() RETURNS trigger AS $$BEGIN  IF NEW.pk IS NULL THEN    LOOP -- эмуляция UPSERT через цикл      PERFORM 1 FROM tbl WHERE val = NEW.val;      EXIT WHEN FOUND; -- выходим при наличии такого значения в словаре      BEGIN        NEW.pk = nextval(pg_get_serial_sequence('tbl', 'pk'));        INSERT INTO tbl VALUES(NEW.*);        RETURN NEW;      EXCEPTION        WHEN unique_violation THEN -- защита от конкурентной вставки      END;    END LOOP;  END IF;  RETURN NULL;END;$$ LANGUAGE plpgsql;-- триггер INSTEAD OF выполняется "вместо" заказанной операции над VIEWCREATE TRIGGER serial INSTEAD OF INSERT ON _tbl  FOR EACH ROW    EXECUTE PROCEDURE tbl_serial();

Обратите внимание, что дальнейшие вставки мы производим как бы во VIEW:

INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 1, val = 1INSERT INTO _tbl(val) VALUES(1) ON CONFLICT DO NOTHING RETURNING *;-- 0 строкINSERT INTO _tbl(val) VALUES(2) ON CONFLICT DO NOTHING RETURNING *;-- 1 строка: pk = 2, val = 2

Ура! Получили ровно то, что хотели, хоть и весьма нетривиально. Поэтому все получилось аккуратно, но котик несколько насторожен.



Он понимает, что со следующей ситуацией так просто уже не разобраться.

Накрутка счетчика транзакций

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

Давайте установим некоторое предельное значение времени ожидания, на которое мы готовы пойти:

SET statement_timeout = '1s';

Но в случае возникновения ошибки мы потеряем с откатом транзакции весь достигнутый прогресс 9 записей успешно вставили, на 10-й получили таймаут и все опять заново. Чтобы не терять сразу все, воспользуемся возможностью создания точек сохранения SAVEPOINT:

BEGIN TRANSACTION;  INSERT INTO _tbl(val) SELECT 1 FROM pg_sleep(0.1); -- эмулируем задержку  SAVEPOINT sp1;  INSERT INTO _tbl(val) SELECT 2 FROM pg_sleep(0.6);  SAVEPOINT sp2;  INSERT INTO _tbl(val) SELECT 3 FROM pg_sleep(1.1);  -- ERROR:  canceling statement due to statement timeout  ROLLBACK TO SAVEPOINT sp2;COMMIT TRANSACTION;

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

_tmp=# SELECT xmin, * FROM tbl;   xmin    | pk | val-----------+----+----- 926944639 |  1 |   1 926944641 |  2 |   2(2 rows)

Вот только у наших записей оказался разный идентификатор создавшей транзакции он увеличивается с каждым вызовом SAVEPOINT.

Для детального понимания внутренней механики работы транзакций, субтранзакций и 2PC в PostgreSQL рекомендую ознакомиться со статьей Transactions in PostgreSQL and their mechanism от Movead Li.

На практике такая ситуация приводит к тому, что autovacuum: VACUUM ... (to prevent wraparound) мы будем видеть очень и очень часто, а если ресурсы сервера не резиновые это может стать проблемой.

Что делать?

• не можем позволить себе ждать завершения операции бесконечно
• не хотим терять весь прогресс транзакции
• не должны неоправданно накручивать счетчик транзакций

Единственный приемлемый вариант лавировать между Сциллой и Харибдой.

Мы можем допустить потерю части прогресса, а не всего сразу. Тогда, фиксируя SAVEPOINT не после каждой операции, а только после некоторой группы, мы будем накручивать счетчик транзакций пропорционально меньше.

Ну как, стало немного полегче?..