PostgreSQL antipatterns: az adatok módosítása a trigger megkerülésével

Előbb-utóbb sokan szembesülnek azzal, hogy valamit tömegesen javítani kell a táblázatban. Már mondd meg, hogyan csináljam jobban, és hogyan - jobb nem csinálni. Ma a tömeges frissítés második aspektusáról fogok beszélni - triggerekről.

Például egy asztalon, amelyen valamit meg kell javítani, egy gonosz kioldó lóg ON UPDATE, az összes változást átviszi néhány aggregátumba. És mindent frissíteni kell (például új mezőt kell inicializálni), olyan óvatosan, hogy ez ne érintse ezeket az aggregátumokat.

Csak kapcsoljuk ki a triggereket!

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

Valójában ez minden... minden lóg.

Mert ALTER TABLE előírja Exkluzív hozzáférés- egy zár, amely alatt senki nem fut párhuzamosan, még egy egyszerű sem SELECT, nem fog tudni leolvasni semmit a táblázatból. Vagyis amíg ez a tranzakció le nem zárul, mindenki várni fog, aki akár „csak olvasni” akar. És erre emlékszünk UPDATE van egy hosszú...

Gyorsan kapcsoljuk ki, aztán gyorsan kapcsoljuk be!

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

UPDATE ...;

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

Itt már jobb a helyzet, sokkal kevesebb a várakozási idő. De csak két probléma rontja el a szépséget:

• ALTER TABLE maga várja az összes többi műveletet az asztalon, beleértve a hosszúakat is SELECT
• Amíg a trigger ki van kapcsolva "elrepül" minden változás táblázatban, még a miénk sem. És nem fog bekerülni az aggregátumokba, pedig kellene. Baj!

Munkamenet változók kezelése

Tehát az előző verzióban egy alapvető pontba botlottunk - valahogy meg kell tanítanunk a triggert, hogy megkülönböztesse a táblázat „mi” változásait a „nem a miénktől”. A „miénk” kihagyásra kerül, de a „nem a miénk” esetén aktiválódik. Ehhez használhatja munkamenet változók.

session_replication_role

olvas kézikönyv:

A triggermechanizmust a konfigurációs változó is befolyásolja session_replication_role. További utasítások nélkül engedélyezve (alapértelmezett), az eseményindítók akkor aktiválódnak, ha a replikációs szerepkör „eredeti” (alapértelmezett) vagy „helyi”. A triggerek megadásával engedélyezettek ENABLE REPLICA, csak akkor fog működni, ha aktuális munkamenet mód - "replica", és a triggerek megadásával engedélyezettek ENABLE ALWAYS, az aktuális replikációs módtól függetlenül működik.

Külön hangsúlyozom, hogy a beállítás nem vonatkozik egyszerre mindenre, mint ALTER TABLE, de csak külön külön kapcsolatunkra. Összességében, hogy egyetlen alkalmazás trigger sem működjön:

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

A trigger belsejében lévő állapot

De a fenti lehetőség az összes triggerre egyszerre működik (vagy előre "változtatnia" kell azokat a triggereket, amelyeket nem szeretne letiltani). És ha kell „kikapcsolni” egy adott triggert?

Ez segíteni fog nekünk "user" session változó:

A kiterjesztési paraméterek nevei a következőképpen írhatók: a kiterjesztés nevét egy pont követi, majd maga a paraméternév, hasonlóan az SQL-ben lévő teljes objektumnevekhez. Például: plpgsql.variable_conflict.
Mivel a rendszeren kívüli beállítások beállíthatók olyan folyamatokban, amelyek nem töltik be a megfelelő bővítőmodult, a PostgreSQL elfogadja értékeket bármely két komponensű névhez.

Először is véglegesítjük a triggert, valami ilyesmi:

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

Ezt egyébként "profitból", blokkolás nélkül, keresztül lehet csinálni CREATE OR REPLACE a trigger funkcióhoz. És akkor a speciális kapcsolatban felhúzzuk a "mi" változónkat:

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

Tudsz más módokat? Oszd meg a megjegyzésekben.

Forrás: will.com