PostgreSQL-antimønstre: endre data ved å omgå en trigger

Før eller siden står mange overfor behovet for å massivt fikse noe i tabellpostene. Jeg har allerede fortell meg hvordan jeg kan gjøre det bedre, og hvordan - det er bedre å ikke gjøre det. I dag skal jeg snakke om det andre aspektet av masseoppdateringen - om triggere.

For eksempel, på et bord der du trenger å fikse noe, henger en ond utløser ON UPDATE, overfører alle endringer til noen aggregater. Og du må oppdatere alt (initialisere et nytt felt, for eksempel) så nøye at disse aggregatene ikke påvirkes.

La oss bare deaktivere utløserne!

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

Faktisk, det er alt - alt henger.

Fordi ALTER TABLE pålegger Eksklusiv tilgang- en lås der ingen løper parallelt, selv ikke en enkel SELECT, vil ikke kunne lese noe fra tabellen. Det vil si at inntil denne transaksjonen avsluttes, vil alle som ønsker å "bare lese" vente. Og det husker vi UPDATE vi har en lang...

La oss raskt slå den av, og så raskt slå den på!

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

UPDATE ...;

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

Her er situasjonen allerede bedre, ventetiden er mye mindre. Men bare to problemer ødelegger all skjønnheten:

• ALTER TABLE selv venter på alle andre operasjoner på bordet, inkludert lange SELECT
• Mens avtrekkeren er av "fly forbi" enhver endring i tabellen, ikke engang vår. Og det vil ikke komme inn i aggregatene, selv om det burde. Problemer!

Administrere øktvariabler

Så i den forrige versjonen snublet vi over et grunnleggende poeng - vi må på en eller annen måte lære utløseren for å skille "våre" endringer i tabellen fra "ikke våre". «Vår» hoppes over som den er, men på «ikke vår» utløses de. Til dette kan du bruke øktvariabler.

session_replication_rolle

Lese Håndbok:

Utløsermekanismen påvirkes også av konfigurasjonsvariabelen session_replication_rolle. Aktivert uten tilleggsinstruksjoner (standard), vil utløsere utløses når replikeringsrollen er "opprinnelse" (standard) eller "lokal". Utløsere aktivert ved å spesifisere ENABLE REPLICA, fungerer bare hvis gjeldende øktmodus - "replika", og utløsere aktivert ved å spesifisere ENABLE ALWAYS, vil fungere uavhengig av gjeldende replikeringsmodus.

Jeg vil spesielt understreke at innstillingen ikke gjelder alt-alt på en gang, som ALTER TABLE, men bare til vår separate spesielle forbindelse. Totalt, slik at ingen applikasjonsutløsere fungerer:

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

Tilstand innvendig avtrekker

Men alternativet ovenfor fungerer for alle utløsere samtidig (eller du må "veksle" utløsere på forhånd som du ikke vil deaktivere). Og hvis vi trenger "slå av" en spesifikk utløser?

Dette vil hjelpe oss "bruker" sesjonsvariabel:

Utvidelsesparameternavn skrives som følger: utvidelsesnavnet etterfulgt av en prikk og deretter selve parameternavnet, på samme måte som fullstendige objektnavn i SQL. For eksempel: plpgsql.variable_conflict.
Fordi alternativer utenfor systemet kan settes i prosesser som ikke laster den riktige utvidelsesmodulen, godtar PostgreSQL verdier for alle navn med to komponenter.

Først fullfører vi utløseren, noe som dette:

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

Dette kan forresten gjøres "for profitt", uten å blokkere, gjennom CREATE OR REPLACE for triggerfunksjonen. Og så i den spesielle forbindelsen trykker vi på "vår" variabel:

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

Kjenner du andre måter? Del i kommentarene.

Kilde: www.habr.com