- PVSM.RU - https://www.pvsm.ru -
Рано или поздно многие сталкиваются с необходимостью что-то массово исправить в записях таблицы. Я уже рассказывал, как это делать лучше [1], а как — лучше не делать. Сегодня расскажу о втором аспекте массового обновления — о сработке триггеров.
Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер 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
Итак, на предыдущем варианте мы наткнулись на принципиальный момент — надо как-то научить триггер отличать «наши» изменения в таблице от «не наших». «Наши» пропускать как есть, а на «не наши» — срабатывать. Для этого можно воспользоваться переменными сессии [2].
Читаем мануал [3]:
На механизм срабатывания триггеров также влияет конфигурационная переменная session_replication_role [4]. Включённые без дополнительных указаний (по умолчанию) триггеры будут срабатывать, когда роль репликации — «origin» (по умолчанию) или «local». Триггеры, включённые указанием
ENABLE REPLICA
, будут срабатывать, только если текущий режим сеанса — «replica», а триггеры, включённые указаниемENABLE ALWAYS
, будут срабатывать независимо от текущего режима репликации.
Особо подчеркну, что настройка относится к не ко всем-всем сразу, как ALTER TABLE
, а только к нашему отдельному спец-коннекту. Итого, чтобы не срабатывали никакие прикладные триггеры:
SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние
Но приведенный выше вариант работает для всех триггеров сразу (или надо «альтерить» заранее триггеры, которые не хочется отключать). А если нам надо «выключить» один конкретный триггер?
В этом нам поможет «пользовательская» переменная сессии [5]:
Имена параметров расширений записываются следующим образом: имя расширения, точка и затем собственно имя параметра, подобно полным именам объектов в 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 = ''; -- вернули в исходное состояние
Знаете другие способы? Поделитесь в комментариях.
Автор: Боровиков Кирилл
Источник [6]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/348293
Ссылки в тексте:
[1] рассказывал, как это делать лучше: https://habr.com/post/481610/
[2] переменными сессии: https://postgrespro.ru/docs/postgresql/12/runtime-config-client
[3] мануал: https://postgrespro.ru/docs/postgresql/12/sql-altertable
[4] session_replication_role: https://postgrespro.ru/docs/postgresql/12/runtime-config-client#GUC-SESSION-REPLICATION-ROLE
[5] «пользовательская» переменная сессии: https://postgrespro.ru/docs/postgresql/12/runtime-config-custom
[6] Источник: https://habr.com/ru/post/489900/?utm_source=habrahabr&utm_medium=rss&utm_campaign=489900
Нажмите здесь для печати.