Antipatterns PostgreSQL: modificăm datele ocolind trigger-ul

Mai devreme sau mai târziu, mulți se confruntă cu necesitatea de a corecta în masă înregistrările dintr-un tabel. Eu deja am povestit despre cum să faci acest lucru mai bine, dar cum — mai bine nu ar trebui făcut. Astăzi voi vorbi despre al doilea aspect al actualizării în masă — declanșarea trigger-elor.

De exemplu, pe tabela pe care trebuie să corectăm ceva, există un trigger agresiv ON UPDATE, care mută toate modificările în anumite agregate. Și trebuie să actualizăm tot (de exemplu, să inițializăm un nou câmp) în așa fel încât aceste agregate să nu fie afectate.

Să dezactivăm trigger-urile!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- aici durează mult
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

De fapt, asta e tot — totul este deja suspendat.

Pentru că ALTER TABLE impune AccessExclusive-blocarea, sub care nimeni nu va putea citi, chiar și cele mai simple SELECT, nu va putea recupera nimic din tabel. Cu alte cuvinte, până nu se termină această tranzacție, toți cei care doresc chiar să „citească” vor aștepta. Iar noi ne amintim că UPDATE avem o așteptare lungă…

Atunci să dezactivăm rapid, apoi să activăm rapid!

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

UPDATE ...;

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

Aici situația este deja mai bună, timpul de așteptare este semnificativ mai mic. Dar două probleme strică toată frumusețea:

• ALTER TABLE așteaptă toate celelalte operațiuni pe tabel, inclusiv cele lungi SELECT
• În timp ce trigger-ul este oprit, orice modificare în tabel, chiar dacă nu este a noastră, va „trece pe lângă”. Și în agregate nu va ajunge, deși ar trebui. O problemă!

Gestionarea variabilelor de sesiune

Deci, în varianta anterioară ne-am lovit de o chestiune principială — trebuie să învățăm trigger-ul să distingă modificările „noastre” în tabel de cele „neale noastre”. „Ale noastre” să fie acceptate așa cum sunt, iar la „neale noastre” — să reacționeze. Pentru aceasta putem folosi variabilele de sesiune.

session_replication_role

Citim manualul:

Mecanismul de declanșare a trigger-elor este influențat și de variabila de configurare session_replication_role. Trigger-ele activate fără indicații suplimentare (implicit) se vor declanșa atunci când rolul de replicare este — „origin” (implicit) sau „local”. Trigger-ele activate prin indicația ENABLE REPLICA, se vor declanșa doar dacă modul curent al sesiunii este „replica”, iar trigger-urile activate prin indicația ENABLE ALWAYS, se vor declanșa indiferent de modul curent al replicării.

Vreau să subliniez că această setare nu se aplică tuturor simultan, așa cum ALTER TABLE, ci doar la conexiunea noastră specială separată. Așadar, pentru a nu activa niciun declanșator aplicativ:

SET session_replication_role = replica; -- am dezactivat declanșatoarele
UPDATE ...;
SET session_replication_role = DEFAULT; -- am revenit la starea inițială

Condiția din interiorul declanșatorului

Dar varianta de mai sus funcționează pentru toate declanșatoarele deodată (sau trebuie să «alterăm» dinainte declanșatoarele pe care nu dorim să le dezactivăm). Dar dacă trebuie să «dezactivăm» un declanșator specific?

Acest lucru ne va ajuta variabila de sesiune «utilizator»:

Numele parametrilor extensiilor sunt scrise astfel: numele extensiei, punct și apoi numele parametrului, similar cu numele complete ale obiectelor în SQL. De exemplu: plpgsql.variable_conflict.
Deoarece parametrii extra-sistemici pot fi setați în procese care nu încarcă modulul extensiei corespunzătoare, PostgreSQL acceptă valoarea pentru orice nume cu două componente.

Mai întâi, modificăm declanșatorul, cam așa:

BEGIN
    -- procesului de conversie îi este permis să facă tot
    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;
...

Apropo, asta se poate face „în live”, fără blocaje, prin CREATE OR REPLACE pentru funcția declanșator. Apoi, în conexiunea specială setăm variabila „a noastră”:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- am revenit la starea inițială

Cunoașteți alte metode? Împărtășiți în comentarii.

Sursa: habr.com