PostgreSQL antipatterny: měníme data obcházíme spouštěče

Dříve či později se mnoho lidí setkává s potřebou hromadně něco opravit v záznamech tabulky. Už jsem vyprávěl, jak to dělat lépe, a jak — lépe nedělat. Dnes vám povím o druhém aspektu hromadné aktualizace — o zpracování triggerů.

Například na tabulce, ve které potřebujete něco opravit, je zlý trigger ON UPDATE, který přenáší veškeré změny do nějakých agregátů. A vy potřebujete všechny aktualizovat (například inicializovat nové pole) tak opatrně, aby se tyto agregáty nedotkly.

Prostě deaktivujme triggery!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- to bude dlouhé
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Vlastně, to je vše — vše už visí.

Protože ALTER TABLE aplikuje AccessExclusive-uzamčení, pod kterým nikdo, kdo provádí paralelní operace, dokonce ani jednoduché nebude prováděn při nepravdivosti první podmínky. Opravy pomocí, nebude moci nic z tabulky přečíst. To znamená, že dokud tato transakce neskončí, všichni, kdo chtějí „prostě číst“, budou čekat. A pamatujeme, že AKTUALIZACE máme d-ů-ů-lhý…

Tak rychle deaktivujme, pak rychle znovu aktivujeme!

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

UPDATE ...;

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

Situace je už lepší, čas čekání je podstatně kratší. Ale dvě problémy kazí celou nádheru:

• ALTER TABLE on čeká na všechny ostatní operace na tabulce, včetně dlouhých nebude prováděn při nepravdivosti první podmínky. Opravy pomocí
• Dokud je trigger vypnutý, jakákoli změna v tabulce, dokonce ani naše, „proletí kolem“. A do agregátů se nedostane, i když by měla. To je problém!

Správa sezení s proměnnými

Takže v předchozím variantě jsme narazili na zásadní bod — musíme nějak naučit trigger odlišovat „naše“ změny v tabulce od „ne našich“. „Naše“ nechat tak, jak jsou, a „ne naše“ — spouštět. K tomu lze použít proměnné sezení.

session_replication_role

Přečtěte si návod:

Na mechanismus spouštění triggerů také vplývá konfigurační proměnná session_replication_role. Spuštěné bez dalších pokynů (ve výchozím nastavení) triggery se spustí, když je replikační role — „origin“ (ve výchozím nastavení) nebo „local“. Triggery spuštěné určením ENABLE REPLICA, se spustí pouze pokud aktuální režim sezení — „replica“, a triggery spuštěné určením ENABLE ALWAYS, se spustí bez ohledu na aktuální replikační režim.

Zvlášť zdůrazním, že nastavení se netýká všech najednou, jako ALTER TABLE, a pouze k našemu speciálnímu připojení. Celkem, aby se žádné aplikační spouštěče neaktivovaly:

SET session_replication_role = replica; -- vypnuly se spouštěče
UPDATE ...;
SET session_replication_role = DEFAULT; -- vrátili do původního stavu

Podmínka uvnitř spouštěče

Ale výše uvedená varianta funguje pro všechny spouštěče najednou (nebo je třeba "alterovat" předem spouštěče, které nechceme vypnout). A pokud potřebujeme "vypnout" jeden konkrétní spouštěč?

S tím nám pomůže "uživatelská" proměnná relace:

Názvy parametrů rozšíření se zapisují následujícím způsobem: název rozšíření, tečka a poté vlastní název parametru, podobně jako plná jména objektů v SQL. Například: plpgsql.variable_conflict.
Jelikož systémové parametry mohou být nastaveny v procesech, které nenačítají odpovídající modul rozšíření, PostgreSQL přijímá hodnoty pro jakákoliv jména se dvěma komponenty.

Nejprve upravíme spouštěč, přibližně takto:

BEGIN
    -- procesu konverze lze dělat všechno
    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;
...

Mimochodem, to lze udělat "naživo", bez zámků, prostřednictvím CREATE OR REPLACE pro spouštěcí funkci. A poté v speciálním připojení nastavujeme "naši" proměnnou:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vrátili do původního stavu

Znáte jiné způsoby? Podělte se v komentářích.

Zdroj: habr.com