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 = ''; -- върнахме в първоначално състояние

Знаете ли други начини? Споделете в коментарите.

Източник: habr.com