PostgreSQL Antipatterns: zmiana danych omijając wyzwalacz

Prędzej czy później wielu użytkowników staje w obliczu potrzeby masowej edycji rekordów w tabeli. Już wcześniej opowiadałem, jak to zrobić lepiej, a jak — lepiej tego nie robić. Dziś opowiem o drugim aspekcie masowej aktualizacji — o działaniu wyzwalaczy.

Na przykład, na tabeli, w której musisz coś poprawić, wisi złośliwy wyzwalacz ON UPDATE, przenoszący wszystkie zmiany do jakichś agregatów. A Ty musisz wszystko zaktualizować (na przykład zainicjować nowe pole) tak ostrożnie, aby te agregaty nie zostały naruszone.

Po prostu wyłączmy wyzwalacze!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- trwa to długo
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Właściwie, to wszystko — wszystko już wisi.

Ponieważ ALTER TABLE nakłada AccessExclusive-blokada, pod którą nikt równolegle działający, nawet zwykły SELECT, nie będzie w stanie niczego przeczytać z tabeli. To znaczy, że dopóki ta transakcja nie zakończy się, wszyscy chętni nawet "po prostu przeczytać" będą musieli czekać. A my pamiętamy, że UPDATE mamy do-oo-olga…

To może szybko wyłączmy, potem szybko włączmy!

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

UPDATE ...;

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

Sytuacja jest już lepsza, czas oczekiwania znacznie krótszy. Ale dwie problemy psują całą sytuację:

• ALTER TABLE sam czeka na wszystkie inne operacje na tabeli, w tym długie SELECT
• Gdy wyzwalacz jest wyłączony, "przeoczy" każdą zmianę w tabeli, nawet nie naszą. I do agregatów nie trafi, chociaż powinno. Problem!

Zarządzanie zmiennymi sesji

W poprzedniej wersji natknęliśmy się na kluczowy moment — trzeba jakoś nauczyć wyzwalacz odróżniać „nasze” zmiany w tabeli od „nie naszych”. „Nasze” przepuszczać jak jest, a na „nie nasze” — działać. W tym celu można wykorzystać zmienne sesji.

session_replication_role

Czytamy podręcznik:

Mechanizm uruchamiania wyzwalaczy wpływa również na zmienną konfiguracyjną session_replication_role. Włączone bez dodatkowych wskazówek (domyślnie) wyzwalacze będą działać, gdy rola replikacji — „origin” (domyślnie) lub „local”. Wyzwalacze, włączone z wskazaniem ENABLE REPLICA, będą działać tylko jeśli aktualny tryb sesji to „replica”, a wyzwalacze, włączone z wskazaniem ENABLE ALWAYS, będą działać niezależnie od aktualnego trybu replikacji.

Szczególnie podkreślam, że ustawienia dotyczą nie wszystkich naraz, jak ALTER TABLE, a tylko do naszego oddzielnego specjalnego połączenia. Podsumowując, aby żadne wyzwalacze aplikacyjne się nie aktywowały:

SET session_replication_role = replica; -- wyłączyliśmy wyzwalacze
UPDATE ...;
SET session_replication_role = DEFAULT; -- przywrócono do pierwotnego stanu

Warunek wewnątrz wyzwalacza

Jednak powyższa opcja działa dla wszystkich wyzwalaczy jednocześnie (lub musimy wcześniej „zmienić” wyzwalacze, które chcemy pozostawić włączone). A jeśli musimy „wyłączyć” jeden konkretny wyzwalacz?

W tym pomoże nam „użytkownikowa” zmienna sesji:

Nazwy parametrów rozszerzeń są zapisywane w następujący sposób: nazwa rozszerzenia, kropka, a następnie właściwa nazwa parametru, podobnie jak pełne nazwy obiektów w SQL. Na przykład: plpgsql.variable_conflict.
Ponieważ parametry systemowe mogą być ustawiane w procesach, które nie ładują odpowiedniego modułu rozszerzenia, PostgreSQL akceptuje wartości dla dowolnych nazw z dwoma komponentami.

Najpierw rozwijamy wyzwalacz, mniej więcej tak:

BEGIN
    -- w procesie konwersji można robić wszystko
    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;
...

Swoją drogą, można to zrobić „na żywo”, bez blokad, przez CREATE OR REPLACE dla funkcji wyzwalacza. A potem w specjalnym połączeniu uruchamiamy „swoją” zmienną:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- przywrócono do pierwotnego stanu

Znasz inne sposoby? Podziel się nimi w komentarzach.

Źródło: habr.com