¿Qué mecanismo permite a los triggers de **PostgreSQL** acceder al conjunto completo de filas modificadas por una única declaración **DML** sin dispararse una vez por cada fila?

Respuesta a la pregunta

Las tablas de transición, introducidas en PostgreSQL 10, proporcionan esta capacidad a través de la cláusula REFERENCING en triggers a nivel de declaración. Este mecanismo expone todo el conjunto de resultados de una modificación como estructuras de tabla temporales: NEW TABLE para operaciones de INSERT/UPDATE y OLD TABLE para operaciones de DELETE/UPDATE, lo que permite que la función del trigger realice un procesamiento basado en conjuntos. En lugar de iterar a través de las filas individualmente, puedes ejecutar una sola declaración SQL que opere en todas las filas afectadas simultáneamente.

CREATE TRIGGER bulk_audit_trigger
AFTER UPDATE ON inventory
REFERENCING NEW TABLE AS updated_items OLD TABLE AS previous_items
FOR EACH STATEMENT
EXECUTE FUNCTION log_inventory_changes();

Dentro de log_inventory_changes(), consultar updated_items devuelve todas las filas afectadas por la UPDATE que disparó el trigger, lo que permite realizar operaciones masivas eficientes como INSERT INTO audit_log SELECT * FROM updated_items;.

Situación de la vida real

Una plataforma de comercio electrónico sufrió una grave degradación del rendimiento durante ajustes masivos de precios nocturnos que afectaron a 100,000 registros de productos. La arquitectura existente utilizaba un trigger AFTER a nivel de fila para escribir entradas de auditoría, lo que provocaba 100,000 ejecuciones secuenciales de INSERT en la tabla price_history y agotaba los recursos del pool de conexiones.

Solución 1: Procesamiento por lotes del lado de la aplicación El equipo consideró eliminar el trigger y manejar las auditorías dentro de la aplicación Java utilizando inserciones por lotes de JDBC. Si bien esto reduciría la carga de CPU de la base de datos, introduciría riesgos críticos de consistencia: si la aplicación se bloqueaba a mitad de lote, las actualizaciones de precios comprometidas carecerían permanentemente de registros de auditoría correspondientes, lo que violaría el cumplimiento de SOX. Además, este enfoque requería una gestión compleja de transacciones distribuidas entre el servidor de aplicaciones y PostgreSQL.

Solución 2: Cola de mensajes asíncrona Otra propuesta consistía en escribir identificadores de fila en un stream de Redis durante la actualización, y luego procesar las auditorías a través de un trabajador en segundo plano. Esto desacoplaba las rutas de escritura pero sacrificaba la consistencia transaccional inmediata. El trabajador asíncrono podría retrasarse durante cargas altas, creando brechas temporales en la pista de auditoría que los auditores regulatorios marcarían. Además, garantizar la semántica de exactamente una vez entre PostgreSQL y Redis añadía una complejidad significativa a la infraestructura.

Solución 3: Trigger a nivel de declaración con tablas de transición El enfoque seleccionado reemplazó el trigger a nivel de fila con un trigger a nivel de declaración que utilizaba REFERENCING NEW TABLE AS new_prices. La función del trigger realizó una única operación basada en conjuntos: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Esto mantuvo un estricto cumplimiento de ACID dentro de la misma transacción mientras procesaba todas las modificaciones en una sola pasada.

Resultado: El tiempo de finalización del lote nocturno se redujo de 45 segundos a 300 milisegundos. La generación de WAL (Write-Ahead Log) disminuyó en un 90%, y el sistema eliminó los picos de contención de bloqueo previamente causados por la masiva recursión de triggers.

Lo que los candidatos a menudo pasan por alto

¿Cómo interactúan las tablas de transición con los triggers BEFORE y INSTEAD OF en vistas?

Las tablas de transición están exclusivamente disponibles en triggers AFTER en tablas ordinarias. Los triggers BEFORE operan en filas individuales antes de que se materialice el conjunto de resultados final de la declaración, por lo que la colección completa de filas modificadas aún no existe. Los triggers INSTEAD OF en vistas no pueden usar tablas de transición porque definen rutas de ejecución alternativas en lugar de observar el resultado real de las operaciones DML en las tablas base subyacentes.

¿Puede la función del trigger modificar datos dentro de la NEW TABLE o OLD TABLE para alterar el resultado final?

No, las tablas de transición son instantáneas de solo lectura accesibles solo durante la ejecución del trigger. Representan vistas inmutables de las filas afectadas por la declaración y no pueden ser modificadas utilizando operaciones UPDATE, DELETE o INSERT. Para cambiar valores antes de que sean comprometidos, debes usar triggers BEFORE a nivel de fila que manipulen directamente la variable de registro NEW, o realizar una DML separada contra tablas permanentes.

¿Por qué las operaciones TRUNCATE no poblacionan la tabla de transición OLD TABLE?

Aunque PostgreSQL admite triggers en TRUNCATE a nivel de declaración, este comando DDL elimina todas las filas sin generar estados de transición de filas individuales a través del sistema MVCC. TRUNCATE omite el mecanismo de versionado de tuplas utilizado para construir relaciones de transición, por lo que no puede poblar OLD TABLE con las filas eliminadas. Auditoría de las operaciones TRUNCATE requiere mecanismos alternativos como event triggers o streams de decodificación lógica.