9.5 i nowsze:
PostgreSQL 9.5 i nowsze wsparcie INSERT ... ON CONFLICT UPDATE
(i ON CONFLICT DO NOTHING
), tj. Upsert.
Porównanie zON DUPLICATE KEY UPDATE
.
Szybkie wyjaśnienie .
Aby zapoznać się z użytkowaniem, zobacz instrukcję - w szczególności klauzulę o konfliktach działań na schemacie składni oraz tekst wyjaśniający .
W przeciwieństwie do rozwiązań dla wersji 9.4 i starszych, które podano poniżej, ta funkcja działa z wieloma sprzecznymi wierszami i nie wymaga wyłącznego blokowania ani ponownej próby.
Zatwierdzenie dodające funkcję jest tutaj, a dyskusja wokół jej rozwoju jest tutaj .
Jeśli korzystasz z wersji 9.5 i nie musisz być kompatybilny wstecz, możesz teraz przestać czytać .
9.4 i starsze:
PostgreSQL nie ma żadnej wbudowanej UPSERT
(lub MERGE
) funkcji, a robienie tego skutecznie w obliczu równoczesnego użycia jest bardzo trudne.
W tym artykule szczegółowo omówiono problem .
Ogólnie rzecz biorąc, musisz wybrać jedną z dwóch opcji:
- Indywidualne operacje wstawiania / aktualizacji w pętli ponownych prób; lub
- Blokowanie stołu i łączenie partii
Pętla ponawiania pojedynczych wierszy
Użycie pojedynczych poprawek wierszy w pętli ponownej próby jest rozsądną opcją, jeśli chcesz, aby wiele połączeń jednocześnie próbowało wykonać wstawienia.
Dokumentacja PostgreSQL zawiera przydatną procedurę, która pozwoli ci to zrobić w pętli wewnątrz bazy danych . Chroni przed utraconymi aktualizacjami i wstawia rasy, w przeciwieństwie do większości naiwnych rozwiązań. Działa tylko w READ COMMITTED
trybie i jest bezpieczny tylko wtedy, gdy jest to jedyna rzecz, którą robisz w transakcji. Funkcja nie będzie działać poprawnie, jeśli wyzwalacze lub dodatkowe unikalne klucze powodują unikalne naruszenia.
Ta strategia jest bardzo nieefektywna. Kiedy tylko jest to praktyczne, powinieneś ustawiać w kolejce prace i wykonać zbiorczy upsert, jak opisano poniżej.
Wiele prób rozwiązania tego problemu nie uwzględnia wycofania, więc powodują niekompletne aktualizacje. Dwie transakcje ścigają się ze sobą; jeden z nich pomyślnie INSERT
; drugi otrzymuje duplikat błędu klucza i UPDATE
zamiast tego robi błąd . Te UPDATE
bloki czekają na INSERT
wycofywania lub zatwierdzenia. Kiedy się wycofuje, UPDATE
warunek ponownego sprawdzania dopasowuje zero wierszy, więc nawet jeśli UPDATE
zatwierdzenia nie wykonały oczekiwanego upsert. Musisz sprawdzić liczbę wierszy wyników i spróbować ponownie w razie potrzeby.
Niektóre próby rozwiązania nie uwzględniają również wyścigów SELECT. Jeśli spróbujesz oczywistego i prostego:
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.
BEGIN;
UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;
-- Remember, this is WRONG. Do NOT COPY IT.
INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);
COMMIT;
wtedy, gdy dwa działają jednocześnie, istnieje kilka trybów awarii. Jednym z nich jest już omówiony problem z ponownym sprawdzaniem aktualizacji. Innym jest, gdy oba UPDATE
jednocześnie, dopasowując zero wierszy i kontynuując. Potem oboje zrobić EXISTS
test, który dzieje się przedINSERT
. Oba mają zero wierszy, więc oba robią INSERT
. Jeden kończy się niepowodzeniem z duplikatem błędu klucza.
Dlatego potrzebujesz pętli ponownej próby. Możesz pomyśleć, że możesz zapobiec zduplikowanym kluczowym błędom lub utracie aktualizacji dzięki sprytnemu SQL, ale nie możesz. Musisz sprawdzić liczbę wierszy lub obsłużyć zduplikowane błędy klucza (w zależności od wybranego podejścia) i spróbować ponownie.
Nie rzucaj na to własnym rozwiązaniem. Podobnie jak w przypadku kolejkowania wiadomości, prawdopodobnie jest to złe.
Masywny upsert z zamkiem
Czasami chcesz zrobić zbiorczy upsert, w którym masz nowy zestaw danych, który chcesz scalić ze starszym istniejącym zestawem danych. Jest to znacznie bardziej wydajne niż wstawianie pojedynczych rzędów i powinno być preferowane, gdy jest to praktyczne.
W takim przypadku zazwyczaj postępujesz według następującego procesu:
CREATE
TEMPORARY
stół
COPY
lub wstaw zbiorczo nowe dane do tabeli temp
LOCK
tabela docelowa IN EXCLUSIVE MODE
. Pozwala to na inne transakcje SELECT
, ale nie wprowadza żadnych zmian w tabeli.
Wykonaj jeden UPDATE ... FROM
z istniejących rekordów, używając wartości z tabeli temp;
Wykonaj jeden INSERT
z wierszy, które jeszcze nie istnieją w tabeli docelowej;
COMMIT
, zwalniając blokadę.
Na przykład w przykładzie podanym w pytaniu, używając wielowartościowego INSERT
do wypełnienia tabeli temp:
BEGIN;
CREATE TEMPORARY TABLE newvals(id integer, somedata text);
INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
LOCK TABLE testtable IN EXCLUSIVE MODE;
UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;
INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;
COMMIT;
Powiązane czytanie
Co MERGE
?
Standard SQL MERGE
ma właściwie źle zdefiniowaną semantykę współbieżności i nie nadaje się do upserowania bez uprzedniego zablokowania tabeli.
To naprawdę przydatna instrukcja OLAP do łączenia danych, ale tak naprawdę nie jest to przydatne rozwiązanie dla bezpiecznego współbieżności. Istnieje wiele porad dla osób korzystających z innych DBMS do używania MERGE
upserts, ale tak naprawdę jest to złe.
Inne bazy danych: