Mover linhas de uma tabela para outra parece trivial: INSERT ... SELECT e depois DELETE. Mas entre esses dois comandos existe uma janela em que os dados ficam duplicados ou se perdem. Os CTEs que modificam dados no PostgreSQL reduzem tudo a um unico comando atomico.
O problema dos dois comandos
O classico trecho de "arquivar e depois apagar" e assim:
INSERT INTO orders_archive
SELECT * FROM orders WHERE status = 'cancelled';
DELETE FROM orders WHERE status = 'cancelled';
Duas coisas podem dar errado. Primeiro, entre o INSERT e o DELETE outra sessao pode alterar ou remover uma linha, entao voce arquiva uma coisa e apaga outra. Segundo, se uma falha cair entre os comandos, as linhas acabam nas duas tabelas ao mesmo tempo. Envolver tudo em uma transacao resolve a falha, mas a logica continua dividida em dois comandos e percorre a tabela duas vezes.
DELETE ... RETURNING dentro do WITH
O PostgreSQL permite colocar INSERT, UPDATE e DELETE diretamente em uma clausula WITH. As linhas apagadas voltam pelo RETURNING e alimentam direto o proximo passo:
WITH moved AS (
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;
E um unico comando. As linhas sao apagadas e arquivadas na mesma transacao, sem janela intermediaria e sem uma segunda varredura da tabela. Liste as colunas explicitamente quando os esquemas diferem:
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;
A regra do snapshot unico
O detalhe central: cada subcomando de um CTE que modifica dados enxerga o mesmo snapshot, tirado quando o comando comeca. As mudancas de um CTE nao sao visiveis para os outros por um SELECT comum; elas sao visiveis apenas pelo RETURNING.
- Se
users referencia os orders que voce apaga, um SELECT FROM orders no mesmo comando ainda "enxerga" as linhas que um CTE irmao esta apagando.
- A ordem de execucao dos subcomandos nao e definida; confie apenas no fluxo de dados pelo
RETURNING.
- Tentar alterar a mesma linha duas vezes em um comando (dois
UPDATE, por exemplo) gera um resultado indefinido e nao e recomendado.
Essa regra e justamente o que torna a mudanca correta: voce opera sobre um conjunto fixo de linhas apagadas, nao sobre uma tabela viva e mutavel.
Particionar linhas em uma unica passada
O mesmo truque distribui linhas para varios destinos. Movemos os funcionarios desligados e contamos quantos sairam de cada departamento ao mesmo tempo:
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;
O SELECT final devolve um relatorio do trabalho feito, util para logs e verificacoes.
Pegadinhas e diferencas entre motores
- Pegadinha: os gatilhos
AFTER DELETE da tabela de origem sao disparados, mas os CTEs que modificam dados nao garantem a ordem em relacao a eles; nao coloque efeitos colaterais de gatilhos na sua logica de mudanca.
RETURNING * puxa todas as colunas das linhas apagadas; se o esquema de origem mudar ele se desloca em silencio, entao liste as colunas no codigo de producao.- O MySQL nao tem CTEs que modificam dados: um
DELETE dentro de WITH e rejeitado. Ali voce move linhas com uma transacao contendo INSERT ... SELECT mais DELETE, ou INSERT ... SELECT ... FOR UPDATE para travar as linhas.
- O ClickHouse e um motor analitico: ali o
DELETE e uma mutacao pesada e o RETURNING nao existe. As mudancas sao feitas com INSERT INTO ... SELECT mais uma mutacao a parte ou manipulacao de particoes.
Os CTEs que modificam dados sao a forma idiomatica do PostgreSQL de expressar "mova estas linhas" como uma intencao unica, atomica e legivel.
Mover linhas de uma tabela para outra parece trivial:
INSERT ... SELECTe depoisDELETE. Mas entre esses dois comandos existe uma janela em que os dados ficam duplicados ou se perdem. Os CTEs que modificam dados no PostgreSQL reduzem tudo a um unico comando atomico.O problema dos dois comandos
O classico trecho de "arquivar e depois apagar" e assim:
INSERT INTO orders_archive SELECT * FROM orders WHERE status = 'cancelled'; DELETE FROM orders WHERE status = 'cancelled';Duas coisas podem dar errado. Primeiro, entre o
INSERTe oDELETEoutra sessao pode alterar ou remover uma linha, entao voce arquiva uma coisa e apaga outra. Segundo, se uma falha cair entre os comandos, as linhas acabam nas duas tabelas ao mesmo tempo. Envolver tudo em uma transacao resolve a falha, mas a logica continua dividida em dois comandos e percorre a tabela duas vezes.DELETE ... RETURNING dentro do WITH
O PostgreSQL permite colocar
INSERT,UPDATEeDELETEdiretamente em uma clausulaWITH. As linhas apagadas voltam peloRETURNINGe alimentam direto o proximo passo:WITH moved AS ( DELETE FROM orders WHERE status = 'cancelled' RETURNING * ) INSERT INTO orders_archive SELECT * FROM moved;E um unico comando. As linhas sao apagadas e arquivadas na mesma transacao, sem janela intermediaria e sem uma segunda varredura da tabela. Liste as colunas explicitamente quando os esquemas diferem:
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;A regra do snapshot unico
O detalhe central: cada subcomando de um CTE que modifica dados enxerga o mesmo snapshot, tirado quando o comando comeca. As mudancas de um CTE nao sao visiveis para os outros por um
SELECTcomum; elas sao visiveis apenas peloRETURNING.usersreferencia osordersque voce apaga, umSELECT FROM ordersno mesmo comando ainda "enxerga" as linhas que um CTE irmao esta apagando.RETURNING.UPDATE, por exemplo) gera um resultado indefinido e nao e recomendado.Essa regra e justamente o que torna a mudanca correta: voce opera sobre um conjunto fixo de linhas apagadas, nao sobre uma tabela viva e mutavel.
Particionar linhas em uma unica passada
O mesmo truque distribui linhas para varios destinos. Movemos os funcionarios desligados e contamos quantos sairam de cada departamento ao mesmo tempo:
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;O
SELECTfinal devolve um relatorio do trabalho feito, util para logs e verificacoes.Pegadinhas e diferencas entre motores
AFTER DELETEda tabela de origem sao disparados, mas os CTEs que modificam dados nao garantem a ordem em relacao a eles; nao coloque efeitos colaterais de gatilhos na sua logica de mudanca.RETURNING *puxa todas as colunas das linhas apagadas; se o esquema de origem mudar ele se desloca em silencio, entao liste as colunas no codigo de producao.DELETEdentro deWITHe rejeitado. Ali voce move linhas com uma transacao contendoINSERT ... SELECTmaisDELETE, ouINSERT ... SELECT ... FOR UPDATEpara travar as linhas.DELETEe uma mutacao pesada e oRETURNINGnao existe. As mudancas sao feitas comINSERT INTO ... SELECTmais uma mutacao a parte ou manipulacao de particoes.Os CTEs que modificam dados sao a forma idiomatica do PostgreSQL de expressar "mova estas linhas" como uma intencao unica, atomica e legivel.