PostgreSQL Antipatterns: modificați datele ocolind un declanșator

Mai devreme sau mai târziu, mulți se confruntă cu nevoia de a repara masiv ceva în înregistrările tabelului. Am deja spune-mi cum să o fac mai bine, și cum - este mai bine să nu o faci. Astăzi voi vorbi despre al doilea aspect al actualizării în masă - despre declanșatoare.

De exemplu, pe o masă în care trebuie să reparați ceva, un declanșator rău atârnă ON UPDATE, transferând toate modificările către unele agregate. Și trebuie să actualizați totul (inițializați un câmp nou, de exemplu) atât de atent încât aceste agregate să nu fie afectate.

Să dezactivăm pur și simplu declanșatoarele!

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

De fapt, asta e tot - totul atârnă.

Pentru că ALTER TABLE impune Acces Exclusiv- o lacăt sub care nimeni nu alergă în paralel, chiar și unul simplu SELECT, nu va putea citi nimic din tabel. Adică, până la încheierea acestei tranzacții, toți cei care vor chiar „să citească” vor aștepta. Și ne amintim asta UPDATE avem o lungă...

Să o oprim repede, apoi să o pornim repede!

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 mult mai mic. Dar doar două probleme strică toată frumusețea:

• ALTER TABLE el însuși așteaptă toate celelalte operațiuni de pe masă, inclusiv cele lungi SELECT
• În timp ce declanșatorul este oprit „zboară” orice schimbare în masă, nici măcar al nostru. Și nu va intra în agregate, deși ar trebui. Probleme!

Gestionarea variabilelor de sesiune

Deci, în versiunea anterioară, am dat peste un punct fundamental - trebuie să învățăm cumva declanșatorul să distingă modificările „noastre” din tabel de „nu ale noastre”. „Ai noștri” sunt săriți așa cum sunt, dar pe „nu al nostru” sunt declanșați. Pentru aceasta puteți folosi variabile de sesiune.

rol_sesiune_replicare

citit manual:

Mecanismul de declanșare este, de asemenea, afectat de variabila de configurare rol_sesiune_replicare. Activați fără instrucțiuni suplimentare (implicit), declanșatoarele se vor declanșa atunci când rolul de replicare este „origine” (implicit) sau „local”. Declanșatoarele sunt activate prin specificarea ENABLE REPLICA, va funcționa numai dacă modul de sesiune curent - „replica” și declanșatoarele activate prin specificarea ENABLE ALWAYS, va funcționa indiferent de modul de replicare curent.

Voi sublinia în special faptul că setarea nu se aplică tuturor deodată, așa cum ALTER TABLE, dar numai la conexiunea noastră specială separată. În total, astfel încât nicio aplicație declanșatoare să funcționeze:

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

Condiție în interiorul declanșatorului

Dar opțiunea de mai sus funcționează pentru toate declanșatoarele simultan (sau trebuie să „alternați” declanșatoarele în avans pe care nu doriți să le dezactivați). Și dacă avem nevoie „opriți” un anumit declanșator?

Acest lucru ne va ajuta variabila de sesiune „utilizator”.:

Numele parametrilor de extensie sunt scrise după cum urmează: numele extensiei urmat de un punct și apoi numele parametrului însuși, similar cu numele de obiecte complete în SQL. De exemplu: plpgsql.variable_conflict.
Deoarece opțiunile din afara sistemului pot fi setate în procesele care nu încarcă modulul de extensie adecvat, PostgreSQL acceptă valori pentru orice nume cu două componente.

Mai întâi, finalizăm declanșatorul, ceva de genul acesta:

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

Apropo, acest lucru se poate face „pentru profit”, fără blocare, prin CREATE OR REPLACE pentru funcția de declanșare. Și apoi, în conexiunea specială, punem la punct variabila "noastre":

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

Stii si alte modalitati? Distribuie in comentarii.

Sursa: www.habr.com