¿Cómo usarías OLD y NEW de PostgreSQL 18 en RETURNING?

Problema y contexto

PostgreSQL 18 permite que RETURNING exponga explícitamente los valores antiguos y nuevos de las filas para INSERT, UPDATE, DELETE y MERGE. El resultado es generado por la misma sentencia que modifica los datos, lo que permite evitar una consulta posterior que compita con otro escritor. El lado antiguo o nuevo puede ser NULL cuando dicho lado no existe para una operación.

Supón que los eventos de auditoría necesitan los valores exactos modificados por una sentencia, que los reintentos deben ser idempotentes y que la aplicación no puede permitirse una segunda lectura entre la mutación y la emisión de la auditoría.

Qué evalúan los entrevistadores

Los entrevistadores buscan atomicidad a nivel de sentencia, semántica de operación correcta y un plan para los límites de transacción y reintentos. Las respuestas sólidas distinguen entre INSERT, UPDATE, DELETE, ON CONFLICT y MERGE, y explican en qué lugar se ubica la entrega de la auditoría respecto al commit.

Una respuesta ordinaria vuelve a consultar la fila después de la escritura. Una respuesta sólida utiliza RETURNING como la salida de la mutación, preserva la identidad de la operación y evita publicar un evento que la transacción luego revierta.

Preguntas para aclarar primero

• ¿El evento de auditoría debe emitirse solo después del commit, o basta con una fila en el outbox?
• ¿Puede una sola sentencia modificar muchas filas y cómo se asignan los ID de evento?
• ¿Qué significa “old” para un conflicto de upsert y para cada acción de MERGE?
• ¿Cómo evitarán los reintentos la duplicación de eventos de auditoría?
• ¿Hay columnas que deban ser redactadas antes de salir de la base de datos?

Si la entrega downstream debe seguir al commit, inserta una fila en el outbox en la misma transacción y publica de forma asíncrona. Si la auditoría es solo para un informe interno de SQL, RETURNING puede ser consumido directamente por el llamador.

Una respuesta de 30 segundos

“Haría que la sentencia de mutación retorne una operación explícita, los valores antiguos, los valores nuevos y una clave de evento estable. Para escrituras de múltiples filas, insertaría esos resultados en un outbox dentro de la misma transacción y luego publicaría después del commit. Probaría el lado NULL para inserciones y eliminaciones, definiría la semántica de upsert y MERGE, redactaría columnas sensibles y haría que los reintentos fueran idempotentes usando la clave de evento”.

Diseño paso a paso

1. Definir el contrato del evento. Incluye la identidad de la tabla, clave primaria, operación, proyección antigua, proyección nueva, ID de transacción o solicitud y una clave de idempotencia.
2. Usar alias explícitos. Escribe RETURNING WITH (OLD AS old_row, NEW AS new_row) o la sintaxis documentada equivalente en lugar de depender de RETURNING *.
3. Manejar la semántica de la operación. Las inserciones normalmente no tienen fila antigua; las eliminaciones normalmente no tienen fila nueva. Los upserts y MERGE necesitan un valor de operación específico para cada rama.
4. Persistir de forma atómica. Inserta los registros retornados en un outbox dentro de la misma transacción. Una lectura separada o publicación externa antes del commit puede observar un estado que luego se revierte.
5. Proteger los datos y los reintentos. Redacta campos, calcula hashes de valores sensibles donde sea apropiado y exige una clave de evento única para que una sentencia reintentada no duplique la entrega.
6. Verificar la concurrencia. Ejecuta escritores concurrentes, conflictos, rollbacks, sentencias de múltiples filas y ramas parciales de MERGE. Compara las filas de auditoría con el estado comprometido de la tabla.

Las alternativas incluyen triggers para aplicación centralizada, decodificación lógica para captura a nivel de toda la base de datos o eventos de aplicación para semántica de dominio. RETURNING es más fuerte cuando la mutación ya posee el cambio preciso a nivel de fila.

Respuesta de ejemplo

“La actualización de la aplicación retorna una operación explícita, clave primaria, proyección antigua, proyección nueva y clave de evento. Para un conflicto de upsert, etiqueto el resultado como update; para MERGE, cada rama suministra su propia operación. La transacción inserta cada fila retornada en un outbox y realiza el commit una sola vez. Un worker publica después del commit con una clave de evento única y reintenta de forma segura. Las eliminaciones tienen una proyección nueva nula, las inserciones una proyección antigua nula, y las columnas sensibles se eliminan antes de que el evento salga de la base de datos”.

Errores comunes

• Error: Volver a consultar después de la mutación → Por qué falla: otro escritor puede modificar la fila → Solución: consumir RETURNING de la misma sentencia.
• Error: Publicar antes del commit → Por qué falla: un evento de auditoría puede describir datos revertidos → Solución: usar un outbox transaccional.
• Error: Tratar cada upsert como insert → Por qué falla: las actualizaciones por conflicto necesitan semánticas diferentes → Solución: emitir una operación explícita.
• Error: Retornar todas las columnas a ciegas → Por qué falla: se filtran secretos o datos personales → Solución: usar una proyección de lista permitida y redacción.

Preguntas de seguimiento y respuestas

¿Qué contiene OLD para un INSERT?

Normalmente no existe una fila previa, por lo que el lado antiguo es NULL. El contrato del evento debe modelar esa ausencia en lugar de inventar una fila por defecto.

¿Cómo se captura un MERGE de múltiples filas?

Consume un resultado RETURNING por cada fila afectada, incluye la operación de la rama e inserta cada evento en la misma transacción del outbox.

¿Qué pasa si la inserción en el outbox falla?

La transacción debe fallar y revertir la mutación. No confirmes la escritura de negocio mientras se descarta silenciosamente su registro de auditoría.

¿Cuándo preferirías la decodificación lógica?

Usa decodificación lógica para la captura amplia de cambios a nivel de toda la base de datos o sistemas que no pueden modificar sentencias. Prefiere RETURNING cuando la aplicación necesita proyecciones conscientes del dominio y una semántica precisa por sentencia.