Antipatterns de PostgreSQL: canvieu les dades sense passar un activador

Tard o d'hora, molts s'enfronten a la necessitat de solucionar massivament alguna cosa als registres de la taula. Ja tinc digues-me com fer-ho millor, i com - és millor no fer-ho. Avui parlaré del segon aspecte de l'actualització massiva: sobre els desencadenants.

Per exemple, en una taula en què cal arreglar alguna cosa, penja un disparador dolent ON UPDATE, transferint tots els canvis a alguns agregats. I cal actualitzar-ho tot (inicialitzar un camp nou, per exemple) amb tanta cura que aquests agregats no es vegin afectats.

Només desactivem els activadors!

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

De fet, això és tot... tot està penjat.

Perquè ALTER TABLE imposa Accés exclusiu- un pany sota el qual ningú corre en paral·lel, ni tan sols un simple SELECT, no podrà llegir res de la taula. És a dir, fins que acabi aquesta transacció, tots els que vulguin "només llegir" esperaran. I això ho recordem UPDATE tenim un llarg...

Apaguem-lo ràpidament i després encenem-lo ràpidament!

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

UPDATE ...;

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

Aquí la situació ja és millor, el temps d'espera és molt menor. Però només dos problemes fan malbé tota la bellesa:

• ALTER TABLE ell mateix espera totes les altres operacions sobre la taula, incloses les llargues SELECT
• Mentre el disparador està apagat "volar per" qualsevol canvi a la taula, ni tan sols la nostra. I no entrarà als agregats, encara que hauria de ser. Problemes!

Gestió de variables de sessió

Per tant, a la versió anterior, ens vam trobar amb un punt fonamental: hem d'ensenyar d'alguna manera el desencadenant a distingir els "nostres" canvis a la taula dels "no els nostres". Els "nostres" es salten tal com estan, però en "no els nostres" es desencadenen. Per a això podeu utilitzar variables de sessió.

rol_de_replicació de sessió

Llegiu manual:

El mecanisme d'activació també es veu afectat per la variable de configuració rol_de_replicació de sessió. Activats sense instruccions addicionals (per defecte), els activadors s'activaran quan la funció de replicació sigui "origen" (predeterminada) o "local". Activadors activats especificant ENABLE REPLICA, només funcionarà si mode de sessió actual - "rèplica" i activadors activats especificant ENABLE ALWAYS, funcionarà independentment del mode de replicació actual.

Destacaré especialment que la configuració no s'aplica a tots alhora, ja que ALTER TABLE, però només a la nostra connexió especial separada. En total, perquè cap aplicació desencadenant funcioni:

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

Condició dins del disparador

Però l'opció anterior funciona per a tots els activadors alhora (o cal "alternar" per endavant els activadors que no voleu desactivar). I si necessitem "apaga" un activador específic?

Això ens ajudarà variable de sessió "usuari".:

Els noms dels paràmetres d'extensió s'escriuen de la següent manera: el nom de l'extensió seguit d'un punt i després el propi nom del paràmetre, de manera similar als noms d'objecte complets a SQL. Per exemple: plpgsql.variable_conflict.
Com que les opcions fora del sistema es poden establir en processos que no carreguen el mòdul d'extensió adequat, PostgreSQL accepta valors per a qualsevol nom amb dos components.

Primer, finalitzem el disparador, una cosa així:

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;
...

Per cert, això es pot fer "amb benefici", sense bloquejar, a través CREATE OR REPLACE per a la funció de disparador. I després, a la connexió especial, anotem la "nostra" variable:

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

Coneixes altres maneres? Comparteix en els comentaris.

Font: www.habr.com