Mover filas de una tabla a otra parece trivial: INSERT ... SELECT y luego DELETE. Pero entre esas dos sentencias hay una ventana en la que los datos se duplican o se pierden. Los CTE que modifican datos en PostgreSQL reducen todo a una sola sentencia atomica.
El problema de las dos sentencias
El tipico fragmento de "archivar y luego purgar" es asi:
INSERT INTO orders_archive
SELECT * FROM orders WHERE status = 'cancelled';
DELETE FROM orders WHERE status = 'cancelled';
Dos cosas pueden salir mal. Primero, entre el INSERT y el DELETE otra sesion puede modificar o borrar una fila, asi que archivas una cosa y borras otra. Segundo, si ocurre un fallo entre ambas sentencias, las filas quedan en las dos tablas a la vez. Envolverlo en una transaccion resuelve el fallo, pero la logica sigue partida en dos sentencias y recorre la tabla dos veces.
DELETE ... RETURNING dentro de WITH
PostgreSQL permite poner INSERT, UPDATE y DELETE directamente en una clausula WITH. Las filas borradas vuelven mediante RETURNING y alimentan directamente el siguiente paso:
WITH moved AS (
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;
Es una unica sentencia. Las filas se borran y se archivan en la misma transaccion, sin ventana intermedia y sin un segundo recorrido de la tabla. Enumera las columnas explicitamente cuando los esquemas difieren:
WITH moved AS (
DELETE FROM orders
WHERE created_at < now() - interval '1 year'
RETURNING id, user_id, amount, status, created_at
)
INSERT INTO orders_archive (id, user_id, amount, status, archived_at)
SELECT id, user_id, amount, status, now()
FROM moved;
La regla de la instantanea unica
El matiz clave: cada subsentencia de un CTE que modifica datos ve la misma instantanea, tomada al iniciar la sentencia. Los cambios de un CTE no son visibles para los demas mediante un SELECT normal; solo son visibles a traves de RETURNING.
- Si
users referencia los orders que borras, un SELECT FROM orders en la misma sentencia todavia "ve" las filas que borra un CTE hermano.
- El orden de ejecucion de las subsentencias no esta definido; confia solo en el flujo de datos a traves de
RETURNING.
- Intentar modificar la misma fila dos veces en una sentencia (dos
UPDATE, por ejemplo) da un resultado indefinido y no se recomienda.
Esta regla es justo lo que hace correcto el traslado: operas sobre un conjunto fijo de filas borradas, no sobre una tabla viva y cambiante.
Particionar filas en una sola pasada
El mismo truco reparte filas hacia varios destinos. Movemos a los empleados dados de baja y contamos cuantos salieron de cada departamento al mismo tiempo:
WITH removed AS (
DELETE FROM employees
WHERE salary IS NULL
RETURNING id, name, manager_id, dept, salary
), archived AS (
INSERT INTO employees_archive
SELECT * FROM removed
RETURNING dept
)
SELECT dept, count(*) AS moved
FROM archived
GROUP BY dept;
El SELECT final devuelve un informe del trabajo realizado, comodo para registros y comprobaciones.
Trampas y diferencias entre motores
- Trampa: los disparadores
AFTER DELETE de la tabla origen si se ejecutan, pero los CTE que modifican datos no garantizan el orden respecto a ellos; no metas los efectos secundarios de disparadores en tu logica de traslado.
RETURNING * arrastra todas las columnas de las filas borradas; si cambia el esquema origen se desplaza en silencio, asi que enumera las columnas en el codigo de produccion.- MySQL no tiene CTE que modifiquen datos: un
DELETE dentro de WITH se rechaza. Alli mueves filas con una transaccion que contiene INSERT ... SELECT mas DELETE, o INSERT ... SELECT ... FOR UPDATE para bloquear las filas.
- ClickHouse es un motor analitico: alli
DELETE es una mutacion pesada y RETURNING no existe. Los traslados se hacen con INSERT INTO ... SELECT mas una mutacion aparte o manipulacion de particiones.
Los CTE que modifican datos son la forma idiomatica de PostgreSQL de expresar "mueve estas filas" como una intencion unica, atomica y legible.
Mover filas de una tabla a otra parece trivial:
INSERT ... SELECTy luegoDELETE. Pero entre esas dos sentencias hay una ventana en la que los datos se duplican o se pierden. Los CTE que modifican datos en PostgreSQL reducen todo a una sola sentencia atomica.El problema de las dos sentencias
El tipico fragmento de "archivar y luego purgar" es asi:
INSERT INTO orders_archive SELECT * FROM orders WHERE status = 'cancelled'; DELETE FROM orders WHERE status = 'cancelled';Dos cosas pueden salir mal. Primero, entre el
INSERTy elDELETEotra sesion puede modificar o borrar una fila, asi que archivas una cosa y borras otra. Segundo, si ocurre un fallo entre ambas sentencias, las filas quedan en las dos tablas a la vez. Envolverlo en una transaccion resuelve el fallo, pero la logica sigue partida en dos sentencias y recorre la tabla dos veces.DELETE ... RETURNING dentro de WITH
PostgreSQL permite poner
INSERT,UPDATEyDELETEdirectamente en una clausulaWITH. Las filas borradas vuelven medianteRETURNINGy alimentan directamente el siguiente paso:WITH moved AS ( DELETE FROM orders WHERE status = 'cancelled' RETURNING * ) INSERT INTO orders_archive SELECT * FROM moved;Es una unica sentencia. Las filas se borran y se archivan en la misma transaccion, sin ventana intermedia y sin un segundo recorrido de la tabla. Enumera las columnas explicitamente cuando los esquemas difieren:
WITH moved AS ( DELETE FROM orders WHERE created_at < now() - interval '1 year' RETURNING id, user_id, amount, status, created_at ) INSERT INTO orders_archive (id, user_id, amount, status, archived_at) SELECT id, user_id, amount, status, now() FROM moved;La regla de la instantanea unica
El matiz clave: cada subsentencia de un CTE que modifica datos ve la misma instantanea, tomada al iniciar la sentencia. Los cambios de un CTE no son visibles para los demas mediante un
SELECTnormal; solo son visibles a traves deRETURNING.usersreferencia losordersque borras, unSELECT FROM ordersen la misma sentencia todavia "ve" las filas que borra un CTE hermano.RETURNING.UPDATE, por ejemplo) da un resultado indefinido y no se recomienda.Esta regla es justo lo que hace correcto el traslado: operas sobre un conjunto fijo de filas borradas, no sobre una tabla viva y cambiante.
Particionar filas en una sola pasada
El mismo truco reparte filas hacia varios destinos. Movemos a los empleados dados de baja y contamos cuantos salieron de cada departamento al mismo tiempo:
WITH removed AS ( DELETE FROM employees WHERE salary IS NULL RETURNING id, name, manager_id, dept, salary ), archived AS ( INSERT INTO employees_archive SELECT * FROM removed RETURNING dept ) SELECT dept, count(*) AS moved FROM archived GROUP BY dept;El
SELECTfinal devuelve un informe del trabajo realizado, comodo para registros y comprobaciones.Trampas y diferencias entre motores
AFTER DELETEde la tabla origen si se ejecutan, pero los CTE que modifican datos no garantizan el orden respecto a ellos; no metas los efectos secundarios de disparadores en tu logica de traslado.RETURNING *arrastra todas las columnas de las filas borradas; si cambia el esquema origen se desplaza en silencio, asi que enumera las columnas en el codigo de produccion.DELETEdentro deWITHse rechaza. Alli mueves filas con una transaccion que contieneINSERT ... SELECTmasDELETE, oINSERT ... SELECT ... FOR UPDATEpara bloquear las filas.DELETEes una mutacion pesada yRETURNINGno existe. Los traslados se hacen conINSERT INTO ... SELECTmas una mutacion aparte o manipulacion de particiones.Los CTE que modifican datos son la forma idiomatica de PostgreSQL de expresar "mueve estas filas" como una intencion unica, atomica y legible.