Ich habe über verschiedene UPSERT -Implementierungen in PostgreSQL gelesen, aber alle Diese Lösungen sind relativ alt oder relativ exotisch (zum Beispiel mit beschreibbarem CTE ).
Und ich bin einfach kein psql-Experte bei Alle müssen sofort herausfinden, ob diese Lösungen alt sind, weil sie gut empfohlen werden, oder ob sie (fast alle) nur Spielzeugbeispiele sind, die nicht für den Produktionsgebrauch geeignet sind.
Was ist die thread-sicherste Methode, um UPSERT in PostgreSQL zu implementieren?
Antwort
PostgreSQL jetzt hat UPSERT .
Die bevorzugte Methode gemäß eine ähnliche StackOverflow-Frage ist derzeit wie folgt:
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"); Kommentare
- I ' verwenden Sie lieber einen beschreibbaren CTE: stackoverflow.com/a/8702291/330315
- Was ' ist der Vorteil eines beschreibbaren CTE gegenüber einer Funktion?
- @Fran ç ois für eine Sache, Geschwindigkeit. Mit einem CTE haben Sie die Datenbank einmal aufgerufen. Wenn Sie es auf diese Weise tun, können Sie es zwei- oder mehrmals treffen. Außerdem kann der Optimierer ' pl / pgsql-Prozeduren nicht so effizient optimieren wie reinen SQL-Code.
- @Fran ç ois Zum anderen Parallelität. Da das obige Beispiel mehrere SQL-Anweisungen enthält, müssen Sie sich über die Rennbedingungen Gedanken machen (der Grund für die Klugey-Schleife). Eine einzelne SQL-Anweisung ist atomar. Siehe diesen Link
- @Fran ç oisBeausoleil siehe hier und hier für warum. Grundsätzlich müssen Sie ohne eine Wiederholungsschleife entweder serialisieren oder es besteht die Möglichkeit von Fehlern aufgrund der inhärenten Racebedingung.
Antwort
UPDATE (20.08.2015):
Es gibt jetzt eine offizielle Implementierung für die Behandlung von Upsets mithilfe von ON CONFLICT DO UPDATE (offizielle Dokumentation) . Zum Zeitpunkt dieses Schreibens befindet sich diese Funktion derzeit in PostgreSQL 9.5 Alpha 2, das hier heruntergeladen werden kann: Postgres-Quellverzeichnisse .
Hier ein Beispiel unter der Annahme, dass item_id Ihr Primärschlüssel ist:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Original Post …
Hier ist eine Implementierung, auf die ich gestoßen bin, als ich einen Einblick in das Einfügen oder Aktualisieren erhalten wollte.
Die Definition von upsert_data besteht in der Konsolidierung die Werte in einer einzelnen Ressource, anstatt den Preis und die item_id zweimal angeben zu müssen: Einmal für die Aktualisierung, erneut für die Einfügung.
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 Wenn Sie dies nicht tun „Wie bei der Verwendung von upsert_data ist hier eine alternative Implementierung:
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 Kommentare
- Wie funktioniert es?
- @jb. nicht so gut, wie ich es gerne hätte. Sie ' werden es sehen signifikante Leistung Strafe ies vs. gerade Einsätze ausführen. Für kleinere Stapel (z. B. 1000 oder weniger) sollte dieses Beispiel jedoch einwandfrei funktionieren.
Antwort
Dies Hier erfahren Sie, ob das Einfügen oder Aktualisieren stattgefunden hat:
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" ); Wenn das Update erfolgt, erhalten Sie eine Einfügung 0, andernfalls 1 oder einen Fehler.