PostgreSQL Antipatterns: muudame andmeid läbi triggeeritud

Varsti või hiljem seisavad paljud silmitsi vajadusega teha massilisi muudatusi tabeli sissekannetes. Ma olen juba rääkinud, kuidas seda paremini teha, ja kuidas — parem mitte teha. Täna räägin teise aspekti kohta massilisest uuendamisest — triggertest.

Näiteks, tabelil, kus peate midagi parandama, on kurjakuulutav trigger ON UPDATE, mis viib kõik muudatused mingitesse agregaatidesse. Ja teil on vaja kõik uuendada (uue vä field initsialiseerida), nii ettevaatlikult, et need agregaadid ei puutuks.

Lülitame lihtsalt triggerid välja!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- siin kaua-kaua
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Põhimõtteliselt, siin ongi kõik — kõik on juba kinni.

Sest ALTER TABLE kehtestab AccessExclusive-lukustuse, mille all keegi, kes paralleelselt teostab, isegi lihtne SELECT, ei suuda tabelist midagi lugeda. See tähendab, et seni, kuni see tehing ei lõpe, ootavad kõik soovijad isegi "lihtsalt lugeda". Ja me mäletame, et UPDATE meil on pööööörd.

Lülitame siis kiiresti välja, seejärel kiiresti sisse!

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

UPDATE ...;

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

Siin on olukord juba parem, ooteaeg on oluliselt lühem. Kuid kaks probleemi rikuvad kogu ilu:

• ALTER TABLE ise ootab kõiki teisi toiminguid tabelis, sealhulgas pikki SELECT
• Kuni trigger on välja lülitatud, "halveneb" iga muudatus tabelis, isegi mitte meie oma. Ja agregaatidesse see kuidagi ei satu, kuigi peaks. Häda!

Sessioonimuutujate haldamine

Nii et eelmise variandi puhul sattusime põhimõttelisele punktile — peame kuidagi õpetama triggerit eristama "meie" muudatused tabelis "mitte meie" omadest. "Meie" peate küljele jätma, kuid "mitte meie" puhul peab see toimima. Selleks võime kasutada sessioonimuutujat.

session_replication_role

Lugesime administraatori:

Triggerite käivitamise mehanismile mõjub ka konfiguratsioonimuutuja session_replication_role. Ilma täiendavate juhisteta (vaikimisi) aktiveeritud triggerid toimivad, kui replikatsioonirežiim on — "origin" (vaikimisi) või "local". Triggerid, mis aktiveeritakse määramisega ENABLE REPLICA, toimivad ainult siis, kui aktuaalne seansirežiim — "replica", ja triggerid, mis aktiveeritakse määramisega ENABLE ALWAYS, toimivad olenemata aktuaalsest replikatsioonirežiimist.

Eriti rõhutan, et seade ei kehti kõigi jaoks korraga, nagu ALTER TABLE, vaid meie eraldi spetsiaalset ühendust. Kokkuvõtteks, et mingeid rakenduse triggereid ei aktiveeruks:

SET session_replication_role = replica; -- välja lülitatud triggerid
UPDATE ...;
SET session_replication_role = DEFAULT; -- tagastatud algsesse olekusse

Tingimus triggeri sees

Kuid ülaltoodud variant töötab kõigi triggerite puhul korraga (või tuleb eelnevalt "muuta" need triggerid, mida ei soovita välja lülitada). Ja kui me peame "välja lülitama" ühe konkreetse triggeri?

Sellega aitab meid "kasutaja" sessioonimuutuja:

Laiendite parameetrite nimed kirjutatakse järgmiselt: laiendi nimi, punkt ja seejärel parameetri nimi, sarnaselt objektide täielikele nimedele SQL-is. Näiteks: plpgsql.variable_conflict.
Kuna mitte-süsteemsed parameetrid võivad olla seatud protsessides, mis ei lae vastavat laiendimoodulit, aktsepteerib PostgreSQL väärtused mis tahes kahe komponendiga nimede jaoks.

Esiteks viime triggeri töötluse läbi, umbes nii:

BEGIN
    -- konverteerimisprotsessis võib teha kõike
    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;
...

Muide, seda saab teha "realses ajas", ilma lukustusteta, läbi CREATE OR REPLACE -iga triggeri funktsiooni. Ja siis seadistame eraldi ühenduses "oma" muutuja:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- tagastatud algsesse olekusse

Kas tead muid viise? Jaga oma mõtteid kommentaarides.

Allikas: habr.com