Dlaczego wiersze wstawione do CTE nie mogą być aktualizowane w tym samym zestawieniu?

W PostgreSQL 9.5 podano prostą tabelę utworzoną za pomocą:

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

Uruchamiam SQL, aby WSTAWIĆ wartość, a następnie AKTUALIZOWAĆ ją w tej samej instrukcji:

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

W wyniku tego aktualizacja jest ignorowana:

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

Dlaczego to? Czy to ograniczenie jest częścią standardu SQL (tj. Występuje w innych bazach danych), czy może jest czymś specyficznym dla PostgreSQL, które można naprawić w przyszłości? Dokumentacja zapytań WITH mówi, że wiele AKTUALIZACJI nie jest obsługiwanych, ale nie wspomina o INSERTACH i AKTUALIZACJACH.

Wszystkie oświadczenia w CTE zdarzają się praktycznie jednocześnie. Tj. Są oparte na tej samej migawce bazy danych.

UPDATEWidzi ten sam stan tabeli podstawowej jako INSERT, co oznacza wiersz ze val = 1nie ma tam jeszcze. Podręcznik wyjaśnia tutaj:

Wszystkie instrukcje są wykonywane z tą samą migawką (patrz Rozdział 13 ), więc nie mogą „widzieć” siebie nawzajem na tabelach docelowych.

Każda instrukcja może zobaczyć, co zostało zwrócone przez inny CTE w RETURNINGklauzuli. Ale podstawowe tabele wyglądają dla nich tak samo.

Potrzebujesz dwóch wyciągów (w jednej transakcji) do tego, co próbujesz zrobić. Podany przykład powinien być INSERTna początek jednym , ale może to wynikać z uproszczonego przykładu.

To jest decyzja wdrożeniowa. Jest to opisane w dokumentacji Postgres, WITHZapytania (wspólne wyrażenia tabelowe) . Istnieją dwa akapity związane z tym problemem.

Po pierwsze, powód zaobserwowanego zachowania:

Instrukcje podrzędne WITHsą wykonywane jednocześnie ze sobą i z głównym zapytaniem . Dlatego podczas korzystania z instrukcji modyfikujących dane w WITHkolejności, w której faktycznie odbywają się określone aktualizacje, jest nieprzewidywalna. Wszystkie instrukcje są wykonywane z tą samą migawką (patrz Rozdział 13), więc nie mogą „widzieć” siebie nawzajem na tabelach docelowych. Łagodzi to skutki nieprzewidywalności rzeczywistej kolejności aktualizacji wierszy i oznacza, że RETURNINGdane są jedynym sposobem komunikowania zmian między różnymi WITHinstrukcjami podrzędnymi a głównym zapytaniem. Przykładem tego jest ...

Po wysłaniu sugestii do pgsql-docs , Marko Tiikkaja wyjaśnił (co zgadza się z odpowiedzią Erwina):

Przypadki wstawiania-aktualizacji i wstawiania-usuwania nie działają, ponieważ AKTUALIZACJE i USUŃ nie mają możliwości zobaczenia WSTAWIONYCH wierszy z powodu ich migawki wykonanej przed wystąpieniem WSTAWIANIA. W tych dwóch przypadkach nie ma nic nieprzewidywalnego.

Tak więc powód, dla którego twoja instrukcja się nie aktualizuje, można wyjaśnić w pierwszym akapicie powyżej (o „migawkach”). Gdy modyfikujesz CTE, dzieje się tak, że wszystkie z nich i główne zapytanie są wykonywane i „widzą” tę samą migawkę danych (tabel), jak były bezpośrednio przed wykonaniem instrukcji. CTE mogą przekazywać sobie informacje o tym, co wstawili / zaktualizowali / usunęli oraz do głównego zapytania za pomocą RETURNINGklauzuli, ale nie widzą bezpośrednio zmian w tabelach. Zobaczmy więc, co dzieje się w twoim oświadczeniu:

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

Mamy 2 części, CTE ( newval):

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

i główne zapytanie:

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

Przebieg wykonania jest mniej więcej taki:

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

W rezultacie, gdy zapytanie główne łączy tbl(jak widać na migawce) z newvaltabelą, łączy pustą tabelę z tabelą 1-wierszową. Oczywiście aktualizuje 0 wierszy. Tak więc oświadczenie nigdy tak naprawdę nie przyszło do modyfikacji nowo wstawionego wiersza i właśnie to widać.

Rozwiązaniem w twoim przypadku jest albo przepisanie instrukcji, aby w pierwszej kolejności wstawić poprawne wartości, albo użycie 2 instrukcji. Jeden, który wstawia i drugi do aktualizacji.

Istnieją inne podobne sytuacje, na przykład jeśli instrukcja zawierała, INSERTa następnie a DELETEw tych samych wierszach. Usunięcie zakończy się niepowodzeniem z dokładnie tych samych powodów.

Niektóre inne przypadki aktualizacji-aktualizacji i aktualizacji-usuwania oraz ich zachowanie zostały wyjaśnione w następnym akapicie na tej samej stronie dokumentacji.

Próba dwukrotnej aktualizacji tego samego wiersza w jednej instrukcji nie jest obsługiwana. Występuje tylko jedna z modyfikacji, ale nie jest łatwo (a czasem nie jest to możliwe) rzetelnie przewidzieć, która z nich. Dotyczy to również usuwania wiersza, który został już zaktualizowany w tej samej instrukcji: wykonywana jest tylko aktualizacja. Dlatego ogólnie powinieneś unikać próby modyfikacji jednego wiersza dwa razy w jednej instrukcji. W szczególności unikaj pisania pod-instrukcji, które mogłyby wpływać na te same wiersze, które zostały zmienione przez instrukcję główną lub instrukcję podrzędną rodzeństwa. Skutki takiego oświadczenia nie będą przewidywalne.

I w odpowiedzi Marko Tiikkaja:

Przypadki aktualizacji-aktualizacji i aktualizacji-usunięcia nie są wyraźnie spowodowane przez te same szczegóły implementacji (jak przypadki wstawiania aktualizacji i wstawiania-usuwania).
Sprawa aktualizacji-aktualizacji nie działa, ponieważ wewnętrznie wygląda jak problem z Halloween, a Postgres nie ma możliwości dowiedzenia się, które krotki można zaktualizować dwukrotnie, a które mogą przywrócić problem Halloween.

Powód jest taki sam (w jaki sposób modyfikowane CTE są wdrażane i jak każdy CTE widzi tę samą migawkę), ale szczegóły różnią się w tych 2 przypadkach, ponieważ są bardziej złożone, a wyniki mogą być nieprzewidywalne w przypadku aktualizacji-aktualizacji.

W przypadku aktualizacji wstawiania (jak w twoim przypadku) i podobnej operacji usuwania-wstawiania wyniki są przewidywalne. Tylko wstawianie odbywa się, ponieważ druga operacja (aktualizacja lub usunięcie) nie ma sposobu, aby zobaczyć i wpłynąć na nowo wstawione wiersze.

Sugerowane rozwiązanie jest jednak takie samo dla wszystkich przypadków, które próbują zmodyfikować te same wiersze więcej niż jeden raz: nie rób tego. Napisz instrukcje, które modyfikują każdy wiersz jeden raz lub użyj oddzielnych (2 lub więcej) instrukcji.