PostgreSQL Antipatterns: меняем данные в обход триггера

Рано или поздно многие сталкиваются с необходимостью что-то массово исправить в записях таблицы. Я уже рассказывал, как это делать лучше, а как — лучше не делать. Сегодня расскажу о втором аспекте массового обновления — о сработке триггеров.

Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.

Давайте просто отключим триггеры!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Собственно, тут и все — все уже висит.

Потому что ALTER TABLE накладывает AccessExclusive-блокировку, под которой никто параллельно выполняющийся, даже простой SELECT, ничего из таблицы прочитать не сможет. То есть пока эта транзакция не закончится, все желающие даже «просто почитать» будут ждать. А мы помним, что UPDATE у нас до-о-олгий…

Давайте тогда быстро отключим, потом быстро включим!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Тут ситуация уже лучше, время ожидания существенно меньше. Но всего две проблемы портят всю красоту:

• ALTER TABLE сам ждет все другие операции на таблице, включая длинные SELECT
• Пока триггер выключен, «пролетит мимо» любое изменение в таблице, даже не наше. И в агрегаты ну никак не попадет, хотя и должно. Беда!

Управление переменными сессии

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

session_replication_role

Читаем мануал:

На механизм срабатывания триггеров также влияет конфигурационная переменная session_replication_role. Включённые без дополнительных указаний (по умолчанию) триггеры будут срабатывать, когда роль репликации — «origin» (по умолчанию) или «local». Триггеры, включённые указанием ENABLE REPLICA, будут срабатывать, только если текущий режим сеанса — «replica», а триггеры, включённые указанием ENABLE ALWAYS, будут срабатывать независимо от текущего режима репликации.

Особо подчеркну, что настройка относится к не ко всем-всем сразу, как ALTER TABLE, а только к нашему отдельному спец-коннекту. Итого, чтобы не срабатывали никакие прикладные триггеры:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Условие внутри триггера

Но приведенный выше вариант работает для всех триггеров сразу (или надо «альтерить» заранее триггеры, которые не хочется отключать). А если нам надо «выключить» один конкретный триггер?

В этом нам поможет «пользовательская» переменная сессии:

Имена параметров расширений записываются следующим образом: имя расширения, точка и затем собственно имя параметра, подобно полным именам объектов в SQL. Например: plpgsql.variable_conflict.
Так как внесистемные параметры могут быть установлены в процессах, не загружающих соответствующий модуль расширения, PostgreSQL принимает значения для любых имён с двумя компонентами.

Сначала дорабатываем триггер, примерно так:

BEGIN
    -- процессу конвертации можно делать все
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

Кстати, это можно сделать «наживую», без блокировок, через CREATE OR REPLACE для триггерной функции. А потом в спец-коннекте взводим «свою» переменную:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Знаете другие способы? Поделитесь в комментариях.