PostgreSQL Antipatterns: zmena údajov obchádzaním spúšťača

Skôr či neskôr sa mnohí stretávajú s potrebou masívne opraviť niečo v tabuľkových záznamoch. Už som povedz mi, ako to urobiť lepšie, a ako - je lepšie to nerobiť. Dnes budem hovoriť o druhom aspekte hromadnej aktualizácie - o spúšťačoch.

Napríklad na stole, v ktorom potrebujete niečo opraviť, visí zlá spúšť ON UPDATE, ktorý prenesie všetky zmeny do niektorých agregátov. A všetko musíte aktualizovať (napríklad inicializovať nové pole) tak opatrne, aby tieto agregáty neboli ovplyvnené.

Len vypnime spúšťače!

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

Vlastne, to je všetko - všetko visí.

Pretože ALTER TABLE ukladá Exkluzívny prístup- zámok, pod ktorým nikto paralelne nebeží, ani jednoduchý SELECT, nebude môcť z tabuľky nič prečítať. To znamená, že kým sa táto transakcia neskončí, každý, kto chce „len čítať“, bude čakať. A pamätáme si to UPDATE máme dlho...

Poďme to rýchlo vypnúť, potom rýchlo zapnúť!

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

UPDATE ...;

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

Tu je už situácia lepšia, čakacia doba je oveľa kratšia. Ale len dva problémy kazia všetku krásu:

• ALTER TABLE sám čaká na všetky ostatné operácie na stole, vrátane dlhých SELECT
• Kým je spúšť vypnutá „preletieť“ akúkoľvek zmenu v tabuľke, ani naša. A nedostane sa do agregátov, hoci by mal. Problémy!

Správa premenných relácie

Takže v predchádzajúcej verzii sme narazili na základný bod - musíme nejakým spôsobom naučiť spúšťač, aby rozlíšil „naše“ zmeny v tabuľke od „nie naše“. „Naše“ sa preskočia tak, ako sú, ale na „nie naše“ sa spustia. Na to môžete použiť premenné relácie.

session_replication_role

prečítať Manuálny:

Spúšťací mechanizmus je ovplyvnený aj konfiguračnou premennou session_replication_role. Ak sú povolené bez ďalších pokynov (predvolené), spúšťače sa spustia, keď je rola replikácie „pôvodná“ (predvolená) alebo „lokálna“. Spúšťače povolené zadaním ENABLE REPLICA, bude fungovať iba ak režim aktuálnej relácie - "replika" a spúšťače povolené zadaním ENABLE ALWAYS, bude fungovať bez ohľadu na aktuálny režim replikácie.

Zvlášť zdôrazním, že nastavenie sa nevzťahuje na všetko naraz, ako ALTER TABLE, ale len do nášho samostatného špeciálneho spojenia. Celkovo, aby nefungovali žiadne spúšťače aplikácií:

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

Stav vnútri spúšte

Vyššie uvedená možnosť však funguje pre všetky spúšťače naraz (alebo musíte vopred „striedať“ spúšťače, ktoré nechcete deaktivovať). A ak potrebujeme „vypnúť“ jeden konkrétny spúšťač?

Toto nám pomôže premenná relácie „používateľa“.:

Názvy parametrov rozšírenia sa píšu takto: názov rozšírenia, za ktorým nasleduje bodka a potom samotný názov parametra, podobne ako úplné názvy objektov v SQL. Napríklad: plpgsql.variable_conflict.
Pretože možnosti mimo systému možno nastaviť v procesoch, ktoré nenačítajú príslušný rozširujúci modul, PostgreSQL akceptuje hodnoty pre ľubovoľné názvy s dvoma komponentmi.

Najprv dokončíme spúšťač, niečo 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;
...

Mimochodom, dá sa to urobiť „pre zisk“, bez blokovania, prostredníctvom CREATE OR REPLACE pre spúšťaciu funkciu. A potom v špeciálnom spojení vyberieme „našu“ premennú:

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

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

Zdroj: hab.com