PostgreSQL Antipatterns: Datenänderungen umgehen Trigger

Früher oder später stehen viele vor der Notwendigkeit, etwas massenhaft in den Datensätzen einer Tabelle zu ändern. Ich habe bereits erklärt, wie man es am besten macht, und wie man es besser nicht macht. Heute werde ich über den zweiten Aspekt des massenhaften Updates sprechen — über das Auslösen von Triggern.

Nehmen wir an, in der Tabelle, die Sie ändern möchten, gibt es einen nervigen Trigger BEI ÄNDERUNG, der alle Änderungen in irgendwelche Aggregate überträgt. Sie müssen jedoch alles aktualisieren (zum Beispiel ein neues Feld initialisieren), und das so vorsichtig, dass diese Aggregate nicht berührt werden.

Lassen Sie uns einfach die Trigger deaktivieren!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- hier lange, lange
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Im Grunde ist das alles — alles hängt schon.

Denn ALTER TABLE die AccessExclusive-die Sperre, unter der niemand parallel ausgeführt wird, selbst ein einfacher SELECT, kann nichts aus der Tabelle lesen. Das heißt, solange diese Transaktion nicht abgeschlossen ist, werden alle, die ‚einfach nur lesen‘ wollen, warten müssen. Und wir erinnern uns, dass UPDATE wir eine ganz schön lange…

Lassen Sie uns also schnell deaktivieren und dann schnell wieder aktivieren!

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

UPDATE ...;

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

Hier sieht die Situation bereits besser aus, die Wartezeit ist deutlich kürzer. Doch zwei Probleme trüben das Bild:

• ALTER TABLE er wartet auf alle anderen Operationen in der Tabelle, einschließlich der langen. SELECT
• Solange der Trigger deaktiviert ist, wird jede Änderung in der Tabelle, selbst unsere, einfach ignoriert. Und sie wird auch nicht in die Aggregate aufgenommen, obwohl das der Fall sein sollte. Eine Katastrophe!

Verwaltung von Sitzungsvariablen

Beim vorherigen Ansatz sind wir auf einen entscheidenden Punkt gestoßen – wir müssen den Trigger irgendwie lehren, zwischen "unseren" Änderungen in der Tabelle und "nicht unseren" zu unterscheiden. "Unsere" sollen unverändert bleiben, während bei "nicht unseren" ausgelöst werden sollte. Dafür können wir Sitzungsvariablen verwenden..

session_replication_role

Lesen wir nach Handbuch:

Die Auslösemechanismen der Trigger werden auch von der Konfigurationsvariable beeinflusst. session_replication_roleTrigger, die ohne zusätzliche Vorgaben (standardmäßig) aktiviert sind, werden ausgelöst, wenn die Replikationsrolle "origin" (standardmäßig) oder "local" ist. Trigger, die mit der Angabe ENABLE REPLICA, werden nur ausgelöst, wenn der aktuelle Sitzungsmodus – "replica" ist, während Trigger, die mit der Angabe ENABLE ALWAYS, unabhängig vom aktuellen Replikationsmodus ausgelöst werden.

Ich möchte besonders betonen, dass die Konfiguration nicht für alle gleichzeitig gilt, sondern nur für unsere spezielle Verbindung. Das Gesamtziel besteht darin, sicherzustellen, dass keine Anwendungstrigger aktiviert werden: ALTER TABLESET session_replication_role = replica; -- Trigger deaktiviert UPDATE ...; SET session_replication_role = DEFAULT; -- wiederhergestellt

Bedingung innerhalb des Triggers

Die oben genannte Variante funktioniert jedoch für alle Trigger gleichzeitig (oder man muss die Trigger, die man nicht deaktivieren möchte, vorher ändern). Falls wir den Wunsch haben,

einen bestimmten Trigger zu deaktivieren benutzerdefinierte Session-Variable?

Dabei hilft uns Die Namen der Erweiterungsparameter werden wie folgt aufgeführt: Name der Erweiterung, Punkt und dann der eigentliche Parametername, ähnlich den vollständigen Objektnamen in SQL. Beispielsweise: plpgsql.variable_conflict.:

Da systemunabhängige Parameter in Prozessen gesetzt werden können, die das entsprechende Modul nicht laden, akzeptiert PostgreSQL
Werte für beliebige Namen mit zwei Komponenten Zuerst überarbeiten wir den Trigger, ungefähr so:.

BEGIN -- Der Konvertierungsprozess kann alles machen 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; ...

BEGIN
    -- Im Umwandlungsprozess kann alles gemacht werden
    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;
...

Übrigens kann man das „live“, ohne Sperren, über CREATE OR REPLACE für die Trigger-Funktion. Danach setzen wir in der speziellen Verbindung unsere Variable:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- zurück in den Ursprungszustand

Kennt ihr andere Methoden? Teilt sie in den Kommentaren.

Quelle: habr.com