PostgreSQL Antipatterns: meníme dáta obchádzajúci spúšťač

Ráno alebo neskôr sa mnohí stretávajú s potrebou hromadne upraviť záznamy v tabuľke. Už som rozprával, ako to robiť najlepšie, a ako — radšej nie. Dnes sa podelím o druhý aspekt hromadného aktualizovania — o aktivácii spúšťacích mechanizmov..

Napríklad na tabuľke, v ktorej treba niečo opraviť, visí nepríjemná spúšť ON UPDATE, ktorá presúva všetky zmeny do nejakých agregátov. A vy potrebujete všetko aktualizovať (nové pole inicializovať, napríklad) tak opatrne, aby sa tie agregáty nedotkli.

Jednoducho vypnime spúšťače!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- tu dlho
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Vlastne, to je všetko — všetko už visí..

Pretože ALTER TABLE AccessExclusive -zámok-blokovanie, pod ktorým nikto paralelne vykonávajúci, ani jednoduché SELECT, nič z tabuľky prečítať nemôže. To znamená, že pokiaľ táto transakcia neskončí, všetci zainteresovaní aj „len si prečítať“ budú čakať. A pamätáme, že UPDATE máme dlhú…

Takže rýchlo vypnime, potom rýchlo zapneme!

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

UPDATE ...;

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

Tu je situácia už lepšia, čas čakania je podstatne kratší. Ale iba dve problémy kazia celú krásu:

• ALTER TABLE sám sa čaká na všetky ostatné operácie na tabuľke, vrátane dlhých. SELECT
• Kým je spúšťač vypnutý, „prebehne popri“ akákoľvek zmena v tabuľke, aj keď nie naša. A do agregátov sa jednoducho nedostane, hoci by mala. Problém!

Správa premenných relácie

Takže, v predchádzajúcej variante sme narazili na zásadný moment — treba nejakým spôsobom naučiť spúšťač rozlišovať „naše“ zmeny v tabuľke od „ne našich“. „Naše“ nechať ako sú, a na „ne naše“ — reagovať. Na tento účel môžeme využiť premenné relácie.

session_replication_role

Čítame príručku:

Na mechanizmus aktivácie spúšťačov tiež vplýva konfiguračná premenná session_replication_role. Spúšťače, ktoré sú zapnuté bez dodatočných pokynov (predvolene), budú fungovať, keď je úloha replikácie — „origin“ (predvolene) alebo „local“. Spúšťače zapnuté s pokynom ENABLE REPLICA, budú fungovať iba ak aktuálny režim relácie — „replica“, a spúšťače zapnuté s pokynom ENABLE ALWAYS, budú fungovať nezávisle od aktuálneho režimu replikácie.

Osobitne podčiarkujem, že nastavenie sa netýka všetkých naraz, ako ALTER TABLE, ale iba do našej špeciálnej pripojenia. Takže, aby sa nespustili žiadne aplikačné spúšťače:

SET session_replication_role = replica; -- vypnuli sme spúšťače
UPDATE ...;
SET session_replication_role = DEFAULT; -- vrátili sme do pôvodného stavu

Podmienka v rámci spúšťača

Ale uvedená varianta funguje pre všetky spúšťače naraz (alebo je potrebné „alterovať“ vopred spúšťače, ktoré nechceme vypínať). A ak potrebujeme vypnúť jeden konkrétny spúšťač?

S týmto nám pomôže užívateľská premenná relácie:

Názvy parametrov rozšírení sú zapisované nasledovne: názov rozšírenia, bodka a potom vlastne názov parametra, podobne ako úplné názvy objektov v SQL. Napríklad: plpgsql.variable_conflict.
Pretože nevlastné parametre môžu byť nastavené v procesoch, ktoré nenačítavajú zodpovedajúci modul rozšírenia, PostgreSQL prijíma hodnoty pre akékoľvek názvy s dvoma komponentmi.

Najprv upravíme spúšťač, približne takto:

BEGIN
    -- proces konverzie môže robiť všetko
    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;
...

Mimochodom, toto sa dá urobiť „naživo“, bez blokovania, cez CREATE OR REPLACE pre spúšťaciu funkciu. A potom v špeciálnom pripojení nastavíme „našu“ premennú:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vrátili sme do pôvodného stavu

Poznáte iné spôsoby? Podeľte sa o ne v komentároch.

Zdroj: habr.com