Antipatrones de PostgreSQL: cambiando datos eludiendo el trigger

Tarde o temprano, muchos se enfrentan a la necesidad de realizar correcciones masivas en los registros de una tabla. Ya he hablado sobre cómo hacerlo mejor, y cómo — mejor no hacerlo. Hoy hablaré del segundo aspecto de la actualización masiva — la activación de disparadores.

Por ejemplo, en una tabla donde necesitas corregir algo, hay un agresivo disparador ON UPDATE, que transfiere todos los cambios a ciertos agregados. Y necesitas actualizar todo (inicializar un nuevo campo, por ejemplo) de manera tan cuidadosa que estos agregados no sean afectados.

¡Simplemente desactivemos los disparadores!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- aquí será largo
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

De hecho, aquí está todo — todo está en espera.

Porque ALTER TABLE impone AccessExclusive-bloqueo, bajo el cual nadie que esté ejecutando algo en paralelo, ni siquiera simple SELECCIONAR, podrá leer nada de la tabla. Es decir, mientras esta transacción no termine, todos los interesados, incluso solo para «leer», tendrán que esperar. Y recordamos que ACTUALIZAR tenemos un largo…

¡Entonces desactivemos rápidamente, y luego activemos rápidamente!

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

UPDATE ...;

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

Aquí la situación es ya mejor, el tiempo de espera es considerablemente menor. Pero hay dos problemas que arruinan toda la belleza:

• ALTER TABLE espera todas las demás operaciones en la tabla, incluidas las largas SELECCIONAR
• Mientras el disparador está desactivado, cualquier cambio en la tabla, incluso si no es nuestro, «pasará de largo». Y no llegará a los agregados, aunque debería. ¡Problema!

Gestión de variables de sesión

Así que, en la variante anterior nos encontramos con un punto fundamental: hay que enseñar de alguna manera al disparador a distinguir entre los cambios «nuestros» y los «no nuestros» en la tabla. Dejar pasar los «nuestros» tal como son, y activar para los «no nuestros». Para esto, se pueden usar variables de sesión.

session_replication_role

Leamos manual:

El mecanismo de activación de los disparadores también está influenciado por la variable de configuración session_replication_role. Los disparadores habilitados sin instrucciones adicionales (por defecto) se activarán cuando el rol de replicación sea «origin» (por defecto) o «local». Los disparadores habilitados con la especificación ENABLE REPLICA, se activarán solo si el modo actual de la sesión es «replica», y los disparadores habilitados con la especificación ENABLE ALWAYS, se activarán independientemente del modo actual de replicación.

Quiero subrayar que esta configuración no se aplica a todos todos a la vez, como ALTER TABLE, sino a nuestra conexión especial. En total, para evitar que se active ningún trigger aplicado:

SET session_replication_role = replica; -- desactivamos triggers
UPDATE ...;
SET session_replication_role = DEFAULT; -- volvemos al estado original

Condición dentro del trigger

Pero la opción anterior funciona para todos los triggers a la vez (o se deben "alterar" previamente los triggers que no queremos desactivar). Y si necesitamos "desactivar" un trigger específico?

Esto nos ayudará "variable" de sesión personalizada:

Los nombres de los parámetros de las extensiones se escriben de la siguiente manera: nombre de la extensión, punto y luego el nombre del parámetro, similar a los nombres completos de los objetos en SQL. Por ejemplo: plpgsql.variable_conflict.
Dado que los parámetros no sistémicos pueden ser establecidos en procesos que no cargan el módulo de extensión correspondiente, PostgreSQL acepta valores para cualquier nombre con dos componentes.

Primero modificamos el trigger, más o menos así:

BEGIN
    -- el proceso de conversión puede hacer todo
    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;
...

Por cierto, esto se puede hacer "en vivo", sin bloqueos, a través de CREATE OR REPLACE para la función del trigger. Y luego en la conexión especial configuramos nuestra variable:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- volvimos al estado original

¿Conocen otros métodos? Compartan en los comentarios.

Fuente: habr.com