PostgreSQL Antipatterns: endre data utenom trigger

Tid eller annet veit mange at dei må gjere ei masseoppdatering i tabellar. Eg har allereie fortalt om korleis dette kan gjerast best, og om korleis det er best å unngå det. I dag vil eg ta for meg den andre aspekten ved masseoppdatering — utløsing av triggarar.

For eksempel, i tabellen der du må gjere endringar, heng det ein ondskapsfull trigger ON UPDATE, som flyttar alle endringar til nokre aggregat. Og du må oppdatere alt (for eksempel initialisere eit nytt felt) så forsiktig at desse aggregata ikkje blir påverka.

La oss berre slå av triggerane!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- her tar det tid
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Eigentleg er det alt — alt heng i.

Fordi ALTER TABLE det påleggjer AccessExclusive-låsing, som gjer at ingen som står parallel med spørsmålet, til og med ein enkel SELECT, kan lese noko frå tabellen. Dette betyr at så lenge denne transaksjonen ikkje er avslutta, vil dei som vil nysgjerrig sjå, vere i kø. Og vi husker at UPDATE vi har ei laaaaang...

La oss då slå av raskt, og så slå på igjen raskt!

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

UPDATE ...;

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

Her er situasjonen mykje betre, ventetida er betydelig kortare. Men det er to problem som øydelegg all skjønnhet:

• ALTER TABLE den ventar på alle andre operasjonar i tabellen, inkludert lange SELECT
• Mens triggeren er slått av, "vil glippe" alle endringar i tabellen, til og med ikkje våre. Og dei vil ikkje bli plukka opp i aggregata, sjølv om det burde. Forferdeleg!

Håndtering av sesjonsvariabler

Så, i forrige alternativ møtte vi eit grunnleggjande poeng — vi må på ein eller anna måte lære triggeren å skille mellom "våre" endringar i tabellen og "ikkje våre". La "våre" gå som dei er, og reager på "ikkje våre". For dette kan ein bruke sesjonsvariabelar.

session_replication_role

Les manualen:

Den mekanismen for utløysing av triggarar påverkas også av den konfigurasjonsvariabelen session_replication_role. Inkludert utan ekstra instruksjonar (standard) vil triggarar bli utløyst når replikasjonsrolla er "origin" (standard) eller "local". Triggarar som er inkludert med ENABLE REPLICA, vil bli utløyst berre dersom den noverande sesjonsmodus er "replica", og triggarar som er inkludert med ENABLE ALWAYS, vil bli utløyst uavhengig av den noverande replikasjonsmodusen.

Eg vil spesielt understreke at innstillinga gjelder ikkje alle samtidige, som ALTER TABLE, men kun til vår separate spesialforbindelse. Så for å unngå at noen applikasjonstriggere utløses:

SET session_replication_role = replica; -- deaktiverte triggerne
UPDATE ...;
SET session_replication_role = DEFAULT; -- tilbake til standardstatus

Betingelsen inne i triggeren

Men alternativet ovenfor fungerer for alle triggerne på en gang (eller må vi «endre» på forhånd de triggerne vi ikke ønsker å deaktivere). Men hvis vi trenger å «deaktivere» én spesifikk trigger?

Dette vil hjelpe oss med «brukerdefinert» sesjonsvariabel:

Navnene på parametrene for utvidelser skrives på følgende måte: utvidelsesnavn, punktum og så selve parameterens navn, lik fullstendige objektnavn i SQL. For eksempel: plpgsql.variable_conflict.
Siden ikke-systemparametere kan settes i prosesser som ikke laster den relevante utvidelsesmodulen, godtar PostgreSQL verdier for alle navn med to komponenter.

Først oppdaterer vi triggeren, omtrent slik:

BEGIN
    -- prosessen for konvertering kan gjøre alt
    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;
...

Forresten, dette kan gjøres «live», uten lås, via CREATE OR REPLACE for triggerfunksjonen. Og så i spesialforbindelsen setter vi vår egen variabel:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- tilbake til standardstatus

Kjenner du andre metoder? Del i kommentarfeltet.

Kilde: habr.com