PostgreSQL Antipatterns: Ändern von Daten обход триггера

Früher oder später stehen viele vor der Notwendigkeit, in einer Tabelle etwas massenhaft zu korrigieren. Ich habe bereits erzählt, wie man es besser macht, und wie — besser nicht. Heute erzähle ich von dem zweiten Aspekt der massiven Aktualisierung — vom Auslösen von Triggern..

Zum Beispiel hängt an der Tabelle, in der Sie etwas korrigieren müssen, ein bösartiger Trigger ON UPDATE, der alle Änderungen in irgendwelche Aggregate überträgt. Und Sie müssen alles aktualisieren (ein neues Feld initialisieren, zum Beispiel) 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;

Eigentlich ist das alles — alles hängt schon..

Denn ALTER TABLE legt AccessExclusive-Sperre auf, unter der niemand, der parallel läuft, selbst ein einfacher SELECT, nichts aus der Tabelle lesen kann. Das heißt, solange diese Transaktion nicht abgeschlossen ist, werden alle, die “einfach lesen” wollen, warten müssen. Und wir erinnern uns, dass UPDATE bei uns sehr lange dauert...

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

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

UPDATE ...;

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

Hier ist die Situation bereits besser, die Wartezeit ist erheblich kürzer. Aber zwei Probleme trüben die ganze Schönheit:

• ALTER TABLE selbst wartet auf alle anderen Operationen in der Tabelle, einschließlich langer. SELECT
• Solange der Trigger deaktiviert ist, entkommt jede Änderung in der Tabelle, selbst nicht unsere. Und sie wird auf keinen Fall in die Aggregate gelangen, obwohl sie sollte. Das ist ein Problem!

Verwaltung von Sitzungsvariablen

Nun, im vorherigen Beispiel sind wir auf einen prinzipiellen Punkt gestoßen — wir müssen die Trigger irgendwie lehren, "unsere" Änderungen in der Tabelle von "nicht unseren" zu unterscheiden. "Unsere" lassen wir unberührt, und die "nicht unseren" sollten ausgelöst werden. Dazu können wir Sitzungsvariablen.

session_replication_role

Lesen Sie das Handbuch:

Auch die Auslösemechanismen der Trigger werden von der Konfigurationsvariablen beeinflusst session_replication_role. Trigger, die ohne zusätzliche Angaben (standardmäßig) aktiviert werden, lösen sich aus, wenn die Replikationsrolle — "origin" (standardmäßig) oder "local" ist. Trigger, die durch die Angabe ENABLE REPLICAaktualisiert wurden, lösen sich nur aus, wenn der aktuelle Sitzungsmodus — "replica" ist, und Trigger, die durch die Angabe ENABLE ALWAYS, lösen sich unabhängig vom aktuellen Replikationsmodus aus.

Ich möchte besonders betonen, dass die Einstellung nicht für alle gleichzeitig gilt, wie ALTER TABLE, sondern nur zu unserem speziellen Einzelverbindung. Insgesamt, um zu verhindern, dass irgendwelche Anwendungstrigger aktiv werden:

SET session_replication_role = replica; -- Trigger deaktiviert
UPDATE ...;
SET session_replication_role = DEFAULT; -- wieder in den ursprünglichen Zustand versetzt

Bedingung innerhalb des Triggers

Aber die oben angegebene Variante funktioniert für alle Trigger gleichzeitig (oder man muss im Voraus die Trigger "altern", die man nicht deaktivieren möchte). Und wenn wir „einen bestimmten Trigger deaktivieren“?

Dabei hilft uns „benutzerdefinierte“ Sitzungsvariable:

Die Namen der Erweiterungsparameter werden folgendermaßen geschrieben: Name der Erweiterung, Punkt und dann der Name des Parameters, ähnlich wie die vollständigen Namen der Objekte in SQL. Zum Beispiel: plpgsql.variable_conflict.
Da eventdata-spezifische Parameter in Prozessen gesetzt werden können, die das entsprechende Modulpaket nicht laden, akzeptiert PostgreSQL Werte für beliebige Namen mit zwei Komponenten.

Zuerst überarbeiten wir den Trigger, ungefähr so:

BEGIN
    -- Im Konvertierungsprozess 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 Triggerfunktion. Und dann 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 ursprünglichen Zustand versetzt

Kennt ihr andere Methoden? Teilt sie in den Kommentaren.

Quelle: habr.com