PostgreSQL Antipatterns: промяна на данни, заобикаляйки тригер

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

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

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

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

Всъщност това е всичко - всичко виси.

защото ALTER TABLE налага Изключителен достъп- ключалка, под която никой не работи паралелно, дори и обикновен SELECT, няма да може да прочете нищо от таблицата. Тоест, докато тази транзакция приключи, всеки, който иска дори „само да чете“, ще чака. И ние помним това UPDATE имаме дълго...

Хайде бързо да го изключим, после бързо да го включим!

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

UPDATE ...;

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

Тук положението вече е по-добро, чакането е много по-малко. Но само два проблема развалят цялата красота:

• ALTER TABLE сам изчаква всички други операции на масата, включително дългите SELECT
• Докато спусъкът е изключен, "прелита" всяка промяна в таблицата, дори и нашата. И няма да влезе в агрегатите, въпреки че трябва. неприятности!

Управление на сесийни променливи

И така, в предишната версия се натъкнахме на фундаментална точка - трябва по някакъв начин да научим тригера да различава „нашите“ промени в таблицата от „не нашите“. „Нашите“ се пропускат така, както са, но на „не нашите“ се задействат. За това можете да използвате сесийни променливи.

сесия_репликация_роля

Прочети наръчник:

Задействащият механизъм също се влияе от конфигурационната променлива сесия_репликация_роля. Активирани без допълнителни инструкции (по подразбиране), тригерите ще се задействат, когато ролята на репликация е "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 = ''; -- вернули в исходное состояние

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

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