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