PostgreSQL Антипатерни: променяме данни без тригери

Рано или късно много хора се сблъскват с необходимостта от масови корекции в записите на таблица. Аз вече разказвах как може да се направи по-добре, а как — по-добре да не се прави. Днес ще говоря за втория аспект на масовото обновление — сработването на тригерите.

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

Нека просто деактивираме тригерите!

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

Собственно, тук и всичко — всичко вече висяло.

Защото ALTER TABLE налага AccessExclusive-блокировката, под която никой съпоставящ паралелно, дори прост SELECT, нищо от таблицата няма да може да прочете. Тоест, докато тази транзакция не приключи, всички желаещи, дори „просто да прочетат“, ще чакат. А ние помним, че АКТУАЛИЗИРАЙ имаме мнооого…

Тогава да деактивираме бързо, след това бързо да включим!

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, ще сработват само ако текущият режим на сесия е „реплика“, а тригери, включени с указване 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