🥇PostgreSQL Antipatterns: mainām datus apejot trigerus

Agrāk vai vēlāk daudz cilvēku saskaras ar nepieciešamību masveidā veikt izmaiņas tabulas ierakstos. Es jau stāstījis, kā to labāk darīt, un kā to labāk nedarīt. Šodien stāstīšu par otro aspektu masu atjaunināšanā — par trigeru izsistēm.

Piemēram, tabulā, kurā jums jāveic izmaiņas, var būt ļaunprātīgs triggers ON UPDATE, kas pārnes visus grozījumus uz kādām agregātām. Bet jums ir nepieciešams veikt visus atjauninājumus (jaunu lauku inicializēt, piemēram) tik uzmanīgi, lai šie agregāti netiktu skarti.

Tad vienkārši atslēgsim trigerus!

SĀKT;
  ALTER TABLE ... DIENĀ TRIGGER ...;
  UPDATE ...; -- šeit ilgi
  ALTER TABLE ... ATJAUNOT TRIGGER ...;
APSTIPRINĀT;

Pa īsumam, viss jau ir šeit — viss jau ir piekārts.

Tāpēc, ka ALTER TABLE uzliek AccessExclusive-bloķēšana, zem kuras neviens paralēli veicamais pat vienkārši SELECT, neko no tabulas izlasīt nevarēs. Tas nozīmē, ka, kamēr šī transakcija nebūs pabeigta, visi interesenti pat "vienkārši izlasīt" gaidīs. Un mēs atceramies, ka UPDATE mums ir ļoti ilgi...

Tad ātri atslēgsim, pēc tam ātri ieslēgsim!

SĀKT;
  ALTER TABLE ... DIENĀ TRIGGER ...;
APSTIPRINĀT;

UPDATE ...;

SĀKT;
  ALTER TABLE ... ATJAUNOT TRIGGER ...;
APSTIPRINĀT;

Šeit situācija jau ir labāka, gaidīšanas laiks ir ievērojami mazāks. Bet divas problēmas sabojā visu skaistumu:

ALTER TABLE viņš gaida visas citas operācijas tabulā, tostarp garas SELECT
Kamēr triggers ir izslēgts, "neizbēgs svaiga izmaiņa tabulā, pat ne mūsu. Un agregātos, nu nekādā gadījumā netiks, lai arī vajadzētu. Traģēdija!

Sesijas mainīgo pārvaldība

Tādējādi, iepriekšējā variantā mēs sastapāmies ar būtisku aspektu — jāmācās trigera atšķirt "mūsu" izmaiņas tabulā no "ne mūsu". "Mūsu" laist cauri tā, kā ir, bet uz "ne mūsu" — iedarboties. Tam var izmantot sesijas mainīgos.

session_replication_role

Lasām administratora rokasgrāmatā, kas pieejama:

Uz trigera aktivizācijas mehānismu ietekmē konfigurācijas mainīgais session_replication_role. Iekļautie bez papildu norādēm (pēc noklusējuma) trigeri darbosies, kad reprodukcijas loma ir "origin" (pēc noklusējuma) vai "local". Trigeri, kas iekļauti ar norādi ENABLE REPLICA, darbosies tikai tad, ja aktuālā sesijas režīms ir "replica", bet trigeri, kas iekļauti ar norādi ENABLE ALWAYS, darbosies neatkarīgi no pašreizējā reprodukcijas režīma.

Īpaši uzsveru, ka iestatījums attiecas nevis uz visiem uzreiz, kā ALTER TABLE, bet tikai mūsu atsevišķajam speciālajam savienojumam. Tātad, lai nekādi lietojumprogrammu trigeri neaktivizētos:

SET session_replication_role = replica; -- izslēdzām trigerus
UPDATE ...;
SET session_replication_role = DEFAULT; -- atgriezām sākotnējā stāvoklī

Noteikums trigerī

Tomēr iepriekš minētais risinājums darbojas visiem trigeriem uzreiz (vai arī ir jā"alterē" iepriekš trigeri, kurus nevēlaties izslēgt). Bet, ja mums vajag izslēgt” tieši vienu konkrētu trigeri?

Šajā mums palīdzēs lietotāja sesijas mainīgais:

Paplašinājumu parametru nosaukumi tiek rakstīti šādi: paplašinājuma nosaukums, punkts un pēc tam paša parametra nosaukums, līdzīgi kā objektu pilnie nosaukumi SQL. Piemēram: plpgsql.variable_conflict.
Tā kā nesistēmas parametri var tikt iestatīti procesos, kas nenovērtē attiecīgo paplašinājuma moduļu, PostgreSQL pieņem vērtības jebkādiem nosaukumiem ar divām komponentēm.

Vispirms pārrakstām trigeri, aptuveni tā:

BEGIN
    -- konvertēšanas procesā var darīt visu
    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;
...

Starp citu, to var izdarīt „dzīvajā”, bez bloķēšanas, caur CREATE OR REPLACE trigeru funkcijai. Un tad īpašajā savienojumā aktivizējam „savu” mainīgo:


SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- atgriezām sākotnējā stāvoklī

Zināt citus veidus? Dalieties komentāros.

Avots: habr.com