PostgreSQL Antywzorce: zmień dane z pominięciem wyzwalacza

Wcześniej czy później wielu staje przed koniecznością masowej naprawy czegoś w rekordach tabeli. Już to zrobiłem powiedz mi jak to zrobić lepieji jak - lepiej tego nie robić. Dziś opowiem o drugim aspekcie masowej aktualizacji - o wyzwalaczach.

Na przykład na stole, na którym trzeba coś naprawić, wisi zły spust ON UPDATE, przenosząc wszystkie zmiany do niektórych agregatów. Musisz także zaktualizować wszystko (na przykład zainicjować nowe pole) tak ostrożnie, aby nie miało to wpływu na te agregaty.

Po prostu wyłączmy wyzwalacze!

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

Właściwie to wszystko - wszystko wisi.

Ponieważ ALTER TABLE narzuca Dostęp na wyłączność- zamek, pod którym nikt nie biegnie równolegle, nawet prosty SELECT, nie będzie można nic odczytać z tabeli. Oznacza to, że do zakończenia tej transakcji wszyscy, którzy będą chcieli chociaż „po prostu przeczytać”, będą czekać. I o tym pamiętamy UPDATE mamy długie...

Szybko to wyłączmy, a potem szybko włączmy!

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

UPDATE ...;

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

Tutaj sytuacja jest już lepsza, czas oczekiwania jest znacznie krótszy. Ale tylko dwa problemy psują całe piękno:

• ALTER TABLE sam czeka na wszystkie inne operacje na stole, łącznie z długimi SELECT
• Gdy spust jest wyłączony, „przelecieć obok” jakiejkolwiek zmiany w stole, nawet nie naszym. I nie dostanie się do agregatów, chociaż powinno. Kłopoty!

Zarządzanie zmiennymi sesji

Tak więc w poprzedniej wersji natknęliśmy się na fundamentalną kwestię - musimy w jakiś sposób nauczyć spust odróżniać „nasze” zmiany w tabeli od „nie naszych”. „Nasze” są pomijane w obecnej postaci, ale w przypadku „nie naszych” są uruchamiane. Do tego możesz użyć zmienne sesyjne.

rola_replikacji sesji

Czytaj podręcznik:

Na mechanizm wyzwalający wpływa również zmienna konfiguracyjna rola_replikacji sesji. Włączone bez dodatkowych instrukcji (domyślnie), wyzwalacze będą uruchamiane, gdy rola replikacji to „Origin” (domyślnie) lub „lokalna”. Wyzwalacze włączone przez określenie ENABLE REPLICA, będzie działać tylko wtedy, gdy bieżący tryb sesji - „replika” i wyzwalacze włączone przez określenie ENABLE ALWAYS, będzie działać niezależnie od bieżącego trybu replikacji.

Szczególnie podkreślę, że ustawienie nie dotyczy wszystkich-wszystkich na raz, gdyż ALTER TABLE, ale tylko do naszego osobnego specjalnego połączenia. W sumie, aby żadne wyzwalacze aplikacji nie działały:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Stan wewnątrz spustu

Ale powyższa opcja działa dla wszystkich wyzwalaczy jednocześnie (lub musisz wcześniej „zmienić” wyzwalacze, których nie chcesz wyłączać). A jeśli potrzebujemy „wyłączyć” jeden konkretny wyzwalacz?

To nam pomoże zmienna sesji „użytkownika”.:

Nazwy parametrów rozszerzeń zapisuje się następująco: nazwa rozszerzenia, po której następuje kropka, a następnie sama nazwa parametru, podobnie jak pełne nazwy obiektów w języku SQL. Na przykład: plpgsql.variable_conflikt.
Ponieważ opcje pozasystemowe można ustawić w procesach, które nie ładują odpowiedniego modułu rozszerzeń, PostgreSQL to akceptuje wartości dla dowolnych nazw z dwoma składnikami.

Najpierw finalizujemy wyzwalacz, mniej więcej tak:

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;
...

Nawiasem mówiąc, można to zrobić „dla zysku”, bez blokowania CREATE OR REPLACE dla funkcji wyzwalania. A następnie w specjalnym połączeniu przekręcamy „naszą” zmienną:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Znasz inne sposoby? Podziel się w komentarzach.

Źródło: www.habr.com