PostgreSQL-Antipatterns: Ändern Sie Daten unter Umgehung eines Triggers

Früher oder später stehen viele vor der Notwendigkeit, etwas in den Tabellenaufzeichnungen massiv zu korrigieren. Ich habe bereits Sag mir, wie ich es besser machen kann, und wie - es ist besser, es nicht zu tun. Heute werde ich über den zweiten Aspekt des Massenupdates sprechen – über Auslöser.

An einem Tisch, an dem Sie etwas reparieren müssen, hängt beispielsweise ein böser Auslöser ON UPDATE, Übertragung aller Änderungen an einige Aggregate. Und Sie müssen alles so sorgfältig aktualisieren (z. B. ein neues Feld initialisieren), dass diese Aggregate nicht beeinträchtigt werden.

Deaktivieren wir einfach die Auslöser!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Eigentlich ist das alles - alles hängt.

Da ALTER TABLE auferlegt Exklusiver Zugriff- eine Sperre, unter der niemand parallel läuft, auch nicht eine einfache SELECT, wird nichts aus der Tabelle lesen können. Das heißt, bis diese Transaktion endet, wird jeder warten, der auch nur „nur lesen“ möchte. Und daran erinnern wir uns UPDATE wir haben eine lange Zeit...

Lass es uns schnell ausschalten und dann schnell wieder einschalten!

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

UPDATE ...;

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

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

• ALTER TABLE selbst wartet auf alle anderen Operationen auf der Tabelle, auch auf lange SELECT
• Während der Auslöser ausgeschaltet ist, jede Änderung „vorbeifliegen“. in der Tabelle, nicht einmal unsere. Und es wird nicht in die Aggregate gelangen, obwohl es sollte. Problem!

Sitzungsvariablen verwalten

In der vorherigen Version sind wir also auf einen grundlegenden Punkt gestoßen: Wir müssen dem Trigger irgendwie beibringen, „unsere“ Änderungen in der Tabelle von „nicht unseren“ zu unterscheiden. „Unsere“ werden unverändert übersprungen, aber bei „nicht unsere“ werden sie ausgelöst. Hierfür können Sie verwenden Sitzungsvariablen.

session_replication_role

Wir lesen Handbuch:

Der Auslösemechanismus wird auch durch die Konfigurationsvariable beeinflusst session_replication_role. Ohne zusätzliche Anweisungen aktiviert (Standard). Trigger werden ausgelöst, wenn die Replikationsrolle „Origin“ (Standard) oder „Lokal“ ist. Durch Angabe aktivierte Trigger ENABLE REPLICA, funktioniert nur, wenn aktuellen Sitzungsmodus - „Replikat“ und durch Angabe aktivierte Trigger ENABLE ALWAYSfunktioniert unabhängig vom aktuellen Replikationsmodus.

Ich möchte besonders betonen, dass die Einstellung nicht für alle auf einmal gilt, da ALTER TABLE, aber nur zu unserer separaten Sonderverbindung. Insgesamt, damit keine Anwendung auslöst:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Bedingung innerhalb des Triggers

Die obige Option funktioniert jedoch für alle Trigger gleichzeitig (oder Sie müssen die Trigger, die Sie nicht deaktivieren möchten, im Voraus „abwechseln“). Und wenn wir es brauchen einen bestimmten Auslöser „ausschalten“.?

Das wird uns helfen Sitzungsvariable „Benutzer“.:

Erweiterungsparameternamen werden wie folgt geschrieben: der Erweiterungsname, gefolgt von einem Punkt und dann dem Parameternamen selbst, ähnlich den vollständigen Objektnamen in SQL. Beispiel: plpgsql.variable_conflict.
Da in Prozessen, die nicht das entsprechende Erweiterungsmodul laden, Optionen außerhalb des Systems festgelegt werden können, akzeptiert PostgreSQL dies Werte für beliebige Namen mit zwei Komponenten.

Zuerst finalisieren wir den Auslöser, etwa so:

BEGIN
    -- процессу конвертации можно делать все
    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;
...

Dies kann übrigens „mit Gewinn“, ohne Blockierung, durch erfolgen CREATE OR REPLACE für die Triggerfunktion. Und dann spannen wir in der speziellen Verbindung „unsere“ Variable:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Kennen Sie andere Möglichkeiten? Teilen Sie es in den Kommentaren.

Source: habr.com