Obecnie akceptowane odpowiedź wydaje się ok, na jeden cel konfliktom, kilka małych konfliktów, krotki i nie wyzwala. Pozwala uniknąć problemu współbieżności 1 (patrz poniżej) przy użyciu brutalnej siły. Proste rozwiązanie ma swój urok, skutki uboczne mogą być mniej ważne.
Jednak we wszystkich innych przypadkach nie aktualizuj identycznych wierszy bez potrzeby. Nawet jeśli nie widać różnicy na powierzchni, istnieją różne skutki uboczne :
Może uruchomić wyzwalacze, których nie należy uruchamiać.
Zapisuje „niewinne” wiersze, prawdopodobnie powodując koszty równoległych transakcji.
Może to sprawić, że wiersz będzie wydawał się nowy, chociaż jest stary (znacznik czasu transakcji).
Co najważniejsze , w modelu MVCC PostgreSQL- a nowa wersja wiersza jest zapisywana dla każdego UPDATE
, bez względu na to, czy zmieniły się dane wiersza. Wiąże się to z obniżeniem wydajności samego UPSERT, rozdęciem tabeli, wzrostem indeksu, spadkiem wydajności dla kolejnych operacji na stole, VACUUM
kosztem. Efekt moll na kilka duplikatów, ale ogromny dla większości powtórzeń.
Ponadto czasami jest to niepraktyczne lub nawet możliwe w użyciu ON CONFLICT DO UPDATE
. Instrukcja:
Dla ON CONFLICT DO UPDATE
, A conflict_target
musi być zapewnione.
Single „target konflikt” nie jest możliwe w przypadku wielu indeksów / ograniczenia są zaangażowani.
Możesz osiągnąć (prawie) to samo bez pustych aktualizacji i skutków ubocznych. Niektóre z poniższych rozwiązań działają również z ON CONFLICT DO NOTHING
(bez „celu konfliktu”), wychwytując wszystkie możliwe konflikty, które mogą się pojawić - co może być pożądane lub nie.
Bez jednoczesnego ładowania zapisu
WITH input_rows(usr, contact, name) AS (
VALUES
(text 'foo1', text 'bar1', text 'bob1') -- type casts in first row
, ('foo2', 'bar2', 'bob2')
-- more?
)
, ins AS (
INSERT INTO chats (usr, contact, name)
SELECT * FROM input_rows
ON CONFLICT (usr, contact) DO NOTHING
RETURNING id --, usr, contact -- return more columns?
)
SELECT 'i' AS source -- 'i' for 'inserted'
, id --, usr, contact -- return more columns?
FROM ins
UNION ALL
SELECT 's' AS source -- 's' for 'selected'
, c.id --, usr, contact -- return more columns?
FROM input_rows
JOIN chats c USING (usr, contact); -- columns of unique index
source
Kolumna jest opcjonalnym dodatkiem do wykazania, w jaki sposób to działa. W rzeczywistości możesz go potrzebować, aby odróżnić oba przypadki (kolejna przewaga nad pustymi zapisami).
Wersja ostateczna JOIN chats
działa, ponieważ nowo wstawione wiersze z dołączonego CTE modyfikującego dane nie są jeszcze widoczne w podstawowej tabeli. (Wszystkie części tej samej instrukcji SQL wyświetlają te same migawki bazowych tabel).
Ponieważ VALUES
wyrażenie jest wolnostojące (nie jest bezpośrednio dołączone do INSERT
), Postgres nie może wyprowadzać typów danych z kolumn docelowych i może być konieczne dodanie jawnych rzutów typów. Instrukcja:
Gdy VALUES
jest używany w INSERT
, wszystkie wartości są automatycznie przekształcane do typu danych odpowiedniej kolumny docelowej. Gdy jest używany w innych kontekstach, może być konieczne określenie prawidłowego typu danych. Jeśli wszystkie wpisy są stałymi literałowymi w cudzysłowach, wymuszenie pierwszej jest wystarczające do określenia zakładanego typu dla wszystkich.
Samo zapytanie (nie licząc efektów ubocznych) może być nieco droższe dla kilku powtórzeń, ze względu na narzut CTE i dodatkowy SELECT
(który powinien być tani, ponieważ z definicji jest tam doskonały indeks - unikalne ograniczenie jest realizowane z indeks).
Może być (dużo) szybsze dla wielu duplikatów. Efektywny koszt dodatkowych zapisów zależy od wielu czynników.
Ale w każdym przypadku jest mniej skutków ubocznych i ukrytych kosztów . Najprawdopodobniej jest ogólnie tańszy.
Dołączone sekwencje są nadal zaawansowane, ponieważ wartości domyślne są wypełniane przed testowaniem pod kątem konfliktów.
O CTE:
Przy jednoczesnym obciążeniu zapisu
Zakładając domyślną READ COMMITTED
izolację transakcji . Związane z:
Najlepsza strategia obrony przed warunkami wyścigu zależy od dokładnych wymagań, liczby i rozmiaru wierszy w tabeli i na tablicach UPSERT, liczby równoległych transakcji, prawdopodobieństwa wystąpienia konfliktów, dostępnych zasobów i innych czynników ...
Kwestia współbieżności 1
Jeśli równoległa transakcja została zapisana w wierszu, który Twoja transakcja próbuje teraz wykonać do UPSERT, transakcja musi poczekać na zakończenie drugiej.
Jeśli druga transakcja zakończy się ROLLBACK
(lub jakimkolwiek błędem, np. Automatycznie ROLLBACK
), transakcja może przebiegać normalnie. Niewielki możliwy efekt uboczny: luki w kolejnych liczbach. Ale żadnych brakujących rzędów.
Jeśli druga transakcja zakończy się normalnie (niejawnie lub jawnie COMMIT
), INSERT
wykryjesz konflikt ( UNIQUE
indeks / ograniczenie jest bezwzględne), a DO NOTHING
zatem również nie zwróci wiersza. (Nie można również zablokować wiersza, jak pokazano w problemie współbieżności 2 poniżej, ponieważ nie jest on widoczny ). SELECT
Widzi tę samą migawkę od początku zapytania, a także nie może zwrócić jeszcze niewidocznego wiersza.
Brakuje takich wierszy w zestawie wyników (nawet jeśli istnieją w tabeli bazowej)!
To może być w porządku, jak jest . Zwłaszcza jeśli nie zwracasz wierszy, jak w przykładzie i jesteś zadowolony, wiedząc, że wiersz tam jest. Jeśli to nie wystarczy, można to obejść na różne sposoby.
Możesz sprawdzić liczbę wierszy danych wyjściowych i powtórzyć instrukcję, jeśli nie zgadza się z liczbą wierszy danych wejściowych. Może wystarczyć w rzadkich przypadkach. Chodzi o to, aby rozpocząć nowe zapytanie (może być w tej samej transakcji), co spowoduje wyświetlenie nowo zatwierdzonych wierszy.
Lub sprawdź, czy w tym samym zapytaniu nie ma brakujących wierszy wyników i nadpisz je za pomocą sztuczki brutalnej siły przedstawionej w odpowiedzi Alextoni .
WITH input_rows(usr, contact, name) AS ( ... ) -- see above
, ins AS (
INSERT INTO chats AS c (usr, contact, name)
SELECT * FROM input_rows
ON CONFLICT (usr, contact) DO NOTHING
RETURNING id, usr, contact -- we need unique columns for later join
)
, sel AS (
SELECT 'i'::"char" AS source -- 'i' for 'inserted'
, id, usr, contact
FROM ins
UNION ALL
SELECT 's'::"char" AS source -- 's' for 'selected'
, c.id, usr, contact
FROM input_rows
JOIN chats c USING (usr, contact)
)
, ups AS ( -- RARE corner case
INSERT INTO chats AS c (usr, contact, name) -- another UPSERT, not just UPDATE
SELECT i.*
FROM input_rows i
LEFT JOIN sel s USING (usr, contact) -- columns of unique index
WHERE s.usr IS NULL -- missing!
ON CONFLICT (usr, contact) DO UPDATE -- we've asked nicely the 1st time ...
SET name = c.name -- ... this time we overwrite with old value
-- SET name = EXCLUDED.name -- alternatively overwrite with *new* value
RETURNING 'u'::"char" AS source -- 'u' for updated
, id --, usr, contact -- return more columns?
)
SELECT source, id FROM sel
UNION ALL
TABLE ups;
To jest podobne do zapytania powyżej, ale dodajemy jeszcze jeden krok z CTE ups
, zanim zwrócimy pełny zestaw wyników. Ten ostatni CTE przez większość czasu nic nie da. Tylko jeśli brakuje wierszy w zwracanym wyniku, używamy brutalnej siły.
Jeszcze więcej. Im więcej konfliktów z istniejącymi wcześniej wierszami, tym większe prawdopodobieństwo, że będzie to skuteczniejsze niż proste podejście.
Jeden efekt uboczny: Drugi UPSERT zapisuje wiersze poza kolejnością, więc ponownie wprowadza możliwość zakleszczenia (patrz poniżej), jeśli trzy lub więcej transakcji zapisujących w tych samych wierszach zachodzi na siebie. Jeśli to problem, potrzebujesz innego rozwiązania - na przykład powtórzenia całej instrukcji, jak wspomniano powyżej.
Kwestia współbieżności 2
Jeśli współbieżne transakcje mogą zapisywać w odpowiednich kolumnach wierszy, których dotyczy problem, i musisz upewnić się, że znalezione wiersze nadal znajdują się na późniejszym etapie tej samej transakcji, możesz tanio zablokować istniejące wiersze w CTE ins
(który w przeciwnym razie zostałby odblokowany) z:
...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE -- never executed, but still locks the row
...
I dodaj również klauzulę blokującą SELECT
, na przykładFOR UPDATE
.
To sprawia, że konkurujące operacje zapisu czekają do końca transakcji, kiedy wszystkie blokady zostaną zwolnione. Więc bądź zwięzły.
Więcej szczegółów i wyjaśnienia:
Impas?
Chroń się przed zakleszczeniami , wstawiając wiersze w stałej kolejności . Widzieć:
Typy danych i rzuty
Istniejąca tabela jako szablon dla typów danych ...
Jawne rzutowania typu dla pierwszego wiersza danych w wolnostojącym VALUES
wyrażeniu mogą być niewygodne. Są sposoby na obejście tego. Możesz użyć dowolnej istniejącej relacji (tabela, widok, ...) jako szablonu wiersza. Tabela docelowa jest oczywistym wyborem dla przypadku użycia. Dane wejściowe są automatycznie wymuszane na odpowiednie typy, jak w VALUES
klauzuli INSERT
:
WITH input_rows AS (
(SELECT usr, contact, name FROM chats LIMIT 0) -- only copies column names and types
UNION ALL
VALUES
('foo1', 'bar1', 'bob1') -- no type casts here
, ('foo2', 'bar2', 'bob2')
)
...
To nie działa w przypadku niektórych typów danych. Widzieć:
... i nazwiska
Działa to również dla wszystkich typów danych.
Podczas wstawiania do wszystkich (wiodących) kolumn tabeli można pominąć nazwy kolumn. Zakładając, że tabela chats
w przykładzie składa się tylko z 3 kolumn użytych w UPSERT:
WITH input_rows AS (
SELECT * FROM (
VALUES
((NULL::chats).*) -- copies whole row definition
('foo1', 'bar1', 'bob1') -- no type casts needed
, ('foo2', 'bar2', 'bob2')
) sub
OFFSET 1
)
...
Poza tym: nie używaj zastrzeżonych słów, takich "user"
jak identyfikator. To naładowany pistolet. Użyj prawidłowych, małych i niecytowanych identyfikatorów. Zastąpiłem go usr
.
ON CONFLICT UPDATE
aby nastąpiła zmiana w wierszu. WtedyRETURNING
go złapie.