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 = ''; -- вернули в исходное состояние
    

    Знаете другие способы? Поделитесь в комментариях.
    Тензор
    Разработчик системы СБИС

    Комментарии 18

      0
      А нельзя локально выполнить все изменения в структуре базы данных, предварительно отключив все триггеры, а потом залить новую версию базы на ресурс, предварительно запретив доступ на него на время пока база заливается?
        +1
        Если у вас сервис должен работать 24x7, а размер базы исчисляется сотнями GB, то нет.
        –1
        Я бы создал отдельного пользователя no_triggers и в каждом тригере сделал бы проверку имени от которого выполняется и при совпадении ничего не делал.
          0
          del
            0

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

              0
              Вроде должно сработать, но это наложит блокировку на запись триггера на период этой транзакции, насколько я понимаю. То есть любая DML-операция, подпадающая под его условия, впадет в ожидание.
              +1
              session_replication_role опасен. В документации написано, что это простой способ случайно сломать ссылочную целостность.

              Так как внешние ключи реализованы посредством триггеров, присвоение этому параметру значения replica влечёт отключение всех проверок внешних ключей, что может привести к нарушению согласованности данных при некорректном использовании.


              Ещё 1 безопасный способ без блокировок: в начале своей транзакции подменить триггерную функцию на пустышку, а в конце транзакции — вернуть как было.
                +1
                Ещё 1 безопасный способ без блокировок: в начале своей транзакции подменить триггерную функцию на пустышку, а в конце транзакции — вернуть как было.
                А не случится ли тогда вот это?
                  +1
                  ALTER TABLE..DISABLE TRIGGER заблокирует саму таблицу, ничего в соседних транзакциях с таблицей не сделаешь.

                  Пересоздание триггерной функции не блокирует таблицу, это легко проверить. Вероятно, блокирует только строку в pg_proc, то есть в соседней транзакции ту же функцию будет какое-то время не пересоздать.
                    0
                    Я бы сказал, что и не выполнить.
                    То есть и триггер отработать не сможет, и пишущая операция подвиснет.
                      0
                      Был неправ. Действительно, до окончания транзакции, меняющей триггерную функцию, блокировки не возникает — просто выполняется старая версия.
                      CREATE TABLE tst(a integer);
                      
                      CREATE OR REPLACE FUNCTION trg() RETURNS trigger AS $$
                      BEGIN
                      	RAISE NOTICE 'trg';
                      	RETURN NEW;
                      END;
                      $$ LANGUAGE plpgsql;
                      
                      CREATE TRIGGER trg AFTER INSERT ON tst EXECUTE PROCEDURE trg();
                      
                      INSERT INTO tst(a)VALUES(1);
                      -- меняем в другом подключении
                      BEGIN;
                      CREATE OR REPLACE FUNCTION trg() RETURNS trigger AS $$
                      BEGIN
                      	RAISE NOTICE 'trgX';
                      	RETURN NEW;
                      END;
                      $$ LANGUAGE plpgsql;
                      COMMIT;
                      
                      
                      
                        0

                        получается, это лучший вариант? хотя, конечно, сильно полагающийся на специфику postgresql (хотя бы то, что нет read uncommited)

                          0
                          У этого способа есть негативный эффект — очень долгая и «жирная» транзакция, включающая все UPDATE, что может приводить к проблемам. Вариант с интеграцией условия в триггер будет работать и без этого.
                            0

                            не понял.
                            если нам нужна транзакция — нам от неё никуда не деться.
                            если нам не нужна транзакция — кто нам мешает каждую операцию обернуть в транзакцию с временной подменой триггера?

                              0
                              Тогда если нам надо выполнить 100k UPDATE — это значит, что pg_proc перенесет 200k перезаписей тела триггерной функции. А проблемы table bloat никто неотменял.
                                0

                                ну если мне нужно будет сделать 100k UPDATE за раз, то разумеется, я не буду так поступать.
                                а если 100k в течение года — не вижу проблем.

                                  0
                                  Если мы подменяем триггер однократно, врезая туда условие от переменной, то вроде как эффективнее получается. Если, конечно, микросекунды на этой проверке некритичны.
                  0
                  Сделать параллельную таблицу для внесения изменений. Триггер ON UPDATE основной таблицы пусть смотрит также эту таблицу и переносит к себе из нее обновление, а после этого удаляет запись в параллельной.
                  При SELECT-е основной таблицы нефиксированные накаты добавляются из параллельной, например через правило. Это чтоб все планируемые изменения работали через основную таблицу уже с самого начала.
                  Постепенно при естественных UPDATE все обновления из параллельной таблицы перенесутся в основную, и когда та опустеет — можно будет отключить правило, вернуть ON UPDATE как было и удалить параллельную таблицу.

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое