PostgreSQL Antipatterns: ændrer data uden om triggeren

Tidligere eller senere står mange over for behovet for at massere noget i tabellerne. Jeg har allerede fortalt om, hvordan man gør det bedst, og hvordan det er bedre ikke at gøre. I dag vil jeg tale om den anden aspekt af masseopdatering — udførelsen af triggere.

For eksempel, på en tabel, som du skal ændre, er der en ondsindet trigger ON UPDATE, der overfører alle ændringer til nogle aggregeringer. Og du skal opdatere alt (for at initialisere et nyt felt, for eksempel) så forsigtigt, at disse aggregeringer ikke bliver berørt.

Lad os bare deaktivere triggerne!

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

Egentlig er det det hele — alt hænger allerede.

Fordi ALTER TABLE påvirker AccessExclusive-låsen, under hvilken ingen der kører parallelt, selv en simpel SELECT, kan læse noget fra tabellen. Det vil sige, så længe denne transaktion ikke er afsluttet, vil alle, der ønsker at «bare læse», vente. Og vi husker, at OPDATERING vi har en lang…

Lad os så hurtigt deaktivere, og så hurtigt aktivere igen!

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

UPDATE ...;

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

Her er situationen allerede bedre, ventetiden er betydeligt kortere. Men to problemer ødelægger stadig skønheden:

• ALTER TABLE selv venter på alle andre operationer på tabellen, inklusive lange SELECT
• Mens triggeren er deaktiveret, vil enhver ændring i tabellen, som vi ikke har foretaget, «flyve forbi». Og det vil på ingen måde komme ind i aggregeringerne, selvom det burde. Det er et problem!

Styring af sessionens variabler

Så i den tidligere variant stødte vi på et principielt punkt - vi skal lære triggeren at skelne mellem «vores» ændringer i tabellen og «ikke vores». «Vore» skal passere som de er, og for «ikke vores» - skal den udløses. For dette kan vi bruge sessionens variabler.

session_replication_role

Vi læser manual:

Mekanismen for triggerens aktivering påvirkes også af den konfiguration variabel session_replication_role. Aktiverede uden yderligere angivelser (som standard) triggere vil aktiveres, når replikationsrollen er «origin» (som standard) eller «local». Triggere aktiveret ved angivelse ENABLE REPLICA, vil kun aktiveres, hvis den aktuelle sessionstilstand er «replica», og triggere aktiveret ved angivelse ENABLE ALWAYS, vil aktiveres uanset den aktuelle replikationsstatus.

Jeg vil særligt fremhæve, at indstillingen ikke gælder for alle med det samme, som ALTER TABLE, men kun til vores separate specielle forbindelse. I alt, for at undgå at nogen anvendte triggere aktiveres:

SET session_replication_role = replica; -- deaktiverede triggere
UPDATE ...;
SET session_replication_role = DEFAULT; -- vendte tilbage til oprindelig tilstand

Betingelsen inde i triggeren

Men den ovenfor nævnte metode virker for alle triggere på én gang (eller man skal "ændre" på forhånd de triggere, man ikke ønsker at deaktivere). Og hvad hvis vi skal deaktivere" en specifik trigger?

Dette vil hjælpe os med "brugerdefineret" session variabel:

Navnene på parameterne i udvidelser skrives som følger: navnet på udvidelsen, punktum og derefter selve parameterens navn, ligesom de fulde navne på objekter i SQL. For eksempel: plpgsql.variable_conflict.
Da ikke-system parametre kan indstilles i processer, der ikke indlæser den tilsvarende udvidelsesmodul, accepterer PostgreSQL værdier for alle navne med to komponenter.

Først forbedrer vi triggeren, omtrent således:

BEGIN
    -- process til konvertering kan gø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 gøres "live", uden låsninger, via CREATE OR REPLACE for triggerfunktionen. Og så aktiverer vi "vores" variabel i den specielle forbindelse:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vendte tilbage til oprindelig tilstand

Kender du andre måder? Del venligst i kommentarerne.

Kilde: habr.com