Início / dba / Perguntas / 151199
Accepted
Jeff Turner
Asked: 2016-10-01 18:55:00 +0800 CST

Por que as linhas inseridas em um CTE não podem ser atualizadas na mesma instrução?

  • 772

No PostgreSQL 9.5, dada uma tabela simples criada com:

create table tbl (
    id serial primary key,
    val integer
);

Eu corro o SQL para INSERT um valor e, em seguida, ATUALIZO na mesma instrução:

WITH newval AS (
    INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;

O resultado é que UPDATE é ignorado:

testdb=> select * from tbl;
┌────┬─────┐
│ id │ val │
├────┼─────┤
│  1 │   1 │
└────┴─────┘

Por que é isso? Essa limitação faz parte do padrão SQL (ou seja, presente em outros bancos de dados) ou é algo específico do PostgreSQL que pode ser corrigido no futuro? A documentação de consultas WITH diz que vários UPDATEs não são suportados, mas não menciona INSERTs e UPDATEs.

postgresql cte

2 respostas

  1. Best Answer

    Todas as subdeclarações de uma consulta com CTEs acontecem praticamente ao mesmo tempo. Ou seja, eles são baseados no mesmo instantâneo do banco de dados.

    O UPDATEvê o mesmo estado da tabela subjacente que o INSERT, o que significa que a linha com val = 1ainda não está lá. O manual esclarece aqui:

    Todas as instruções são executadas com o mesmo instantâneo (consulte o Capítulo 13 ), portanto, elas não podem "ver" os efeitos umas das outras nas tabelas de destino.

    Cada instrução pode ver o que é retornado por outra CTE na RETURNINGcláusula. Mas as tabelas subjacentes parecem todas iguais para eles.

    Você precisaria de duas instruções (em uma única transação) para o que está tentando fazer. O exemplo dado deve ser apenas um único INSERTpara começar, mas isso pode ser devido ao exemplo simplificado.

    • 23

  2. Esta é uma decisão de implementação. Ele é descrito na documentação do Postgres, WITHQueries (Common Table Expressions) . Há dois parágrafos relacionados ao assunto.

    Primeiro, o motivo do comportamento observado:

    As subinstruções em WITHsão executadas simultaneamente umas com as outras e com a consulta principal . Portanto, ao usar instruções de modificação de dados em WITH, a ordem na qual as atualizações especificadas realmente acontecem é imprevisível. Todas as instruções são executadas com o mesmo instantâneo (consulte o Capítulo 13), portanto, elas não podem "ver" os efeitos umas das outras nas tabelas de destino. Isso alivia os efeitos da imprevisibilidade da ordem real das atualizações de linha e significa que RETURNINGos dados são a única maneira de comunicar alterações entre diferentes WITHsubdeclarações e a consulta principal. Um exemplo disso é que em...

    Depois que postei uma sugestão junto com pgsql-docs , Marko Tiikkaja explicou (que concorda com a resposta de Erwin):

    Os casos inserir-atualizar e inserir-excluir não funcionam porque os UPDATEs e DELETEs não têm como ver as linhas INSERTadas devido ao instantâneo ter sido obtido antes do INSERT acontecer. Não há nada de imprevisível nesses dois casos.

    Portanto, o motivo pelo qual sua declaração não é atualizada pode ser explicado no primeiro parágrafo acima (sobre "instantâneos"). O que acontece quando você modifica CTEs é que todos eles e a consulta principal são executados e "vêem" o mesmo instantâneo dos dados (tabelas), como estavam imediatamente antes da execução da instrução. Os CTEs podem passar informações sobre o que inseriram/atualizaram/excluíram entre si e para a consulta principal usando a RETURNINGcláusula, mas não podem ver as alterações nas tabelas diretamente. Então vamos ver o que acontece na sua declaração:

    WITH newval AS (
    INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;

    Temos 2 partes, o CTE ( newval):

    -- newval
     INSERT INTO tbl(val) VALUES (1) RETURNING id

    e a consulta principal:

    -- main 
UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id

    O fluxo de execução é mais ou menos assim:

               initial data: tbl
                id │ val 
                 (empty)
               /         \
              /           \
             /             \
    newval:                 \
       tbl (after newval)    \
           id │ val           \
            1 │   1           |
                              |
    newval: returns           |
           id                 |
            1                 |
               \              |
                \             |
                 \            |
                    main query

    Como resultado, quando a consulta principal junta tbl(como visto no instantâneo) com a newvaltabela, ela junta uma tabela vazia com uma tabela de 1 linha. Obviamente ele atualiza 0 linhas. Portanto, a instrução nunca realmente veio para modificar a linha recém-inserida e é isso que você vê.

    A solução no seu caso é reescrever a instrução para inserir os valores corretos em primeiro lugar ou usar 2 instruções. Um que insere e um segundo para atualizar.

    Existem outras situações semelhantes, como se a instrução tivesse um INSERTe depois um DELETEnas mesmas linhas. A exclusão falharia exatamente pelos mesmos motivos.

    Alguns outros casos, com atualização-atualização e atualização-exclusão e seu comportamento são explicados no parágrafo seguinte, na mesma página de documentação.

    Não há suporte para tentar atualizar a mesma linha duas vezes em uma única instrução. Apenas uma das modificações ocorre, mas não é fácil (e às vezes não é possível) prever com segurança qual delas. Isso também se aplica à exclusão de uma linha que já foi atualizada na mesma instrução: apenas a atualização é realizada. Portanto, você geralmente deve evitar tentar modificar uma única linha duas vezes em uma única instrução. Em particular, evite escrever subinstruções WITH que possam afetar as mesmas linhas alteradas pela instrução principal ou uma subinstrução irmã. Os efeitos de tal declaração não serão previsíveis.

    E na resposta de Marko Tiikkaja:

    Os casos atualizar-atualizar e atualizar-excluir explicitamente não são causados ​​pelo mesmo detalhe de implementação subjacente (como os casos inserir-atualizar e inserir-excluir).
    O caso atualização-atualização não funciona porque internamente se parece com o problema do Dia das Bruxas, e o Postgres não tem como saber quais tuplas poderiam ser atualizadas duas vezes e quais poderiam reintroduzir o problema do Dia das Bruxas.

    Portanto, o motivo é o mesmo (como os CTEs modificadores são implementados e como cada CTE vê o mesmo instantâneo), mas os detalhes diferem nesses 2 casos, pois são mais complexos e os resultados podem ser imprevisíveis no caso de atualização-atualização.

    No insert-update (conforme o seu caso) e em um insert-delete semelhante, os resultados são previsíveis. Apenas a inserção acontece, pois a segunda operação (atualizar ou excluir) não tem como ver e afetar as linhas recém-inserida.

    A solução sugerida, porém, é a mesma para todos os casos que tentam modificar as mesmas linhas mais de uma vez: Não faça isso. Escreva instruções que modifiquem cada linha uma vez ou use instruções separadas (2 ou mais).

    • 19

relate perguntas