PostgreSQL Antipatterns: spreminjanje podatkov mimo sprožilnikov

Prej ali slej se mnogi soočajo z nujno potrebo po masivnem popravilu v zapisih tabele. Že sem razlagal, kako to narediti pravilno, kako pa - ne prav. Danes bom govoril o drugem vidiku masovnega posodabljanja - o aktiviranju sprožilnikov.

Na primer, na tabeli, kjer morate nekaj popraviti, visi zlohotni sprožilec ON UPDATE, ki prenaša vse spremembe v kakšne agregate. In potrebujete, da vse posodobite (novo polje inicializirate, na primer) tako previdno, da se ti agregati ne poškodujejo.

Enostavno izklopimo sprožilnike!

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

Pravzaprav, to je vse - vse že visi.

Ker ALTER TABLE vsiljuje AccessExclusive-zaklep, pod katerim nihče, ki se izvaja hkrati, tudi preprosto SELECT, ne more nič prebrati iz tabele. To pomeni, da dokler ta transakcija ne konča, bodo vsi, ki bi želeli »samo prebrati«, čakali. In spomnimo se, da UPDATE imamo dol-oo-čak…

Hitro torej izklopimo, nato hitro vklopimo!

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

UPDATE ...;

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

Tu je situacija že boljša, čas čakanja je bistveno krajši. A dve težavi pokvarita vse lepo:

• ALTER TABLE sam čaka vse druge operacije na tabeli, vključno z dolgimi SELECT
• Dokler je sprožilec izklopljen, bo katera koli sprememba v tabeli, tudi ne naša, »mlada preletela«. In v agregate ne bo prišla, čeprav bi morala. Težava!

Upravljanje s sejami spremenljivk

Torej, v prejšnji opciji smo naleteli na pomemben vidik - kako naučiti sprožilec razlikovat »naše« spremembe v tabeli od »ne naših«. »Naše« spustimo, medtem ko na »ne naše« - se sproži. Za to lahko uporabimo sejne spremenljivke.

session_replication_role

Bodimo pozorni na navodila:

Tudi na mehanizem aktiviranja sprožilnikov vpliva konfiguracijska spremenljivka session_replication_role. Sprožilci, vključeni brez dodatnih navodil (privzeto), se aktivirajo, ko je vloga replikacije - »origin« (privzeto) ali »local«. Sprožilci, vključeni z navedbo ENABLE REPLICA, se aktivirajo le, če je trenutni način seje »replica«, sprožilci, vključeni z navedbo ENABLE ALWAYS, pa se aktivirajo ne glede na trenutni način replikacije.

Posebno naglašavam da se podešavanje odnosi ne na sve odjednom, već samo na naš poseban spec-konek. ALTER TABLEUkratko, kako ne bi aktivirali nikakve aplikativne triggere:

SET session_replication_role = replica; -- isključili smo triggere
UPDATE ...;
SET session_replication_role = DEFAULT; -- vratili u prvobitno stanje

Uslov unutar trigera

Ali navedena opcija radi za sve triggere odjednom (ili treba "alterovati" unapred triggere koje ne želimo isključiti). A ako želimo "isključiti" jedan konkretan trigger?

U tome će nam pomoći "korisnička" sesijska promenljiva:

Imena parametara ekstenzija se beleže na sledeći način: ime ekstenzije, tačka, i zatim samo ime parametra, slično punim imenima objekata u SQL-u. Na primer: plpgsql.variable_conflict.
Budući da eksterni parametri mogu biti postavljeni u procesima koji ne učitavaju odgovarajući modul ekstenzije, PostgreSQL prihvata vrednosti za bilo koja imena sa dve komponente.

Prvo doradimo trigger, otprilike ovako:

BEGIN
    -- procesu konverzije možemo raditi sve
    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;
...

Usput, to se može uraditi "uživo", bez blokada, kroz CREATE OR REPLACE za funkciju trigera. A onda u spec-konekciji aktiviramo "svoju" promenljivu:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vratili u prvobitno stanje

Znate druge načine? Podelite u komentarima.

Izvor: habr.com