Czytałem o różnych implementacjach UPSERT w PostgreSQL, ale wszystkie te rozwiązania są stosunkowo stare lub stosunkowo egzotyczne (na przykład używa się CTE z możliwością zapisu ).
wszystko po to, aby od razu dowiedzieć się, czy te rozwiązania są stare, ponieważ są dobrze zalecane, czy też są (no cóż, prawie wszystkie) tylko przykładami zabawek, które nie nadają się do użytku produkcyjnego.
Jaki jest najbardziej bezpieczny dla wątków sposób implementacji UPSERT w PostgreSQL?
Odpowiedź
PostgreSQL teraz ma UPSERT .
Preferowana metoda zgodnie z podobnym pytaniem dotyczącym StackOverflow jest obecnie następująca:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, "david"); SELECT merge_db(1, "dennis"); Komentarze
- I ' d raczej używaj CTE z możliwością zapisu: stackoverflow.com/a/8702291/330315
- Co ' s przewaga zapisywalnego CTE w porównaniu z funkcją?
- @Fran ç o to przede wszystkim szybkość. Używając CTE raz trafisz do bazy danych. Robiąc to w ten sposób, możesz trafić go dwa lub więcej razy. Ponadto optymalizator może ' optymalizować procedury pl / pgsql tak wydajnie, jak czysty kod SQL.
- @Fran ç ois Po drugie, współbieżność. Ponieważ powyższy przykład zawiera wiele instrukcji SQL, musisz się martwić o warunki wyścigu (powód pętli klugey). Pojedyncza instrukcja SQL będzie atomowa. Zobacz ten link
- @Fran ç oisBeausoleil zobacz tutaj i tutaj , aby dowiedzieć się, dlaczego. Zasadniczo bez pętli ponownej próby musisz albo serializować, albo masz możliwość wystąpienia błędów wynikających z nieodłącznego stanu wyścigu.
Odpowiedź
AKTUALIZACJA (20.08.2015):
Istnieje teraz oficjalna implementacja obsługi upsertów za pomocą ON CONFLICT DO UPDATE (oficjalna dokumentacja) . W chwili pisania tego tekstu funkcja ta znajduje się obecnie w PostgreSQL 9.5 Alpha 2, która jest dostępna do pobrania tutaj: Źródłowe katalogi Postgres .
Oto przykład, zakładając, że item_id to Twój klucz podstawowy:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Oryginalny post …
Oto implementacja, na którą natrafiłem, gdy chciałem uzyskać wgląd w to, czy nastąpiło wstawienie, czy aktualizacja.
Definicja upsert_data polega na skonsolidowaniu wartości w jednym zasobie, zamiast konieczności dwukrotnego określania ceny i item_id: raz w celu aktualizacji, ponownie dla wstawki.
WITH upsert_data AS ( SELECT "19.99"::numeric(10,2) AS price, "abcdefg"::character varying AS item_id ), update_outcome AS ( UPDATE pricing_tbl SET price = upsert_data.price FROM upsert_data WHERE pricing_tbl.item_id = upsert_data.item_id RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT upsert_data.price AS price, upsert_data.item_id AS item_id FROM upsert_data WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome Jeśli nie „Nie podoba mi się użycie upsert_data, oto alternatywna implementacja:
WITH update_outcome AS ( UPDATE pricing_tbl SET price = "19.99" WHERE pricing_tbl.item_id = "abcdefg" RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT "19.99" AS price, "abcdefg" AS item_id WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome Komentarze
- Jak to działa?
- @jb. nie tak dobrze, jak bym chciał. ' zobaczysz znaczna kara za wydajność ies vs. wykonywanie prostych wkładek. Jednak w przypadku mniejszych partii (powiedzmy 1000 lub mniej) ten przykład powinien działać dobrze.
Odpowiedź
To poinformuje Cię, czy nastąpiło wstawienie lub aktualizacja:
with "update_items" as ( -- Update statement here update items set price = 3499, name = "Uncle Bob" where id = 1 returning * ) -- Insert statement here insert into items (price, name) -- But make sure you put your values like so select 3499, "Uncle Bob" where not exists ( select * from "update_items" ); Jeśli aktualizacja nastąpi, otrzymasz insert 0, w przeciwnym razie wstaw 1 lub błąd.