Eine sehr häufig gestellte Frage ist, wie man eine Upsert-Operation durchführt, also das, was MySQL INSERT ... ON DUPLICATE UPDATE
nennt und der Standard als Teil der MERGE
-Operation unterstützt.
Da PostgreSQL dies nicht direkt unterstützt (vor pg 9.5), wie kann man dies tun? Betrachten Sie das Folgende:
CREATE TABLE testtable (
id integer PRIMARY KEY,
somedata text NOT NULL
);
INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');
Nun stellen Sie sich vor, dass Sie die Tupel (2, 'Joe')
, (3, 'Alan')
"upsert" wollen, so dass der neue Tabelleninhalt wäre:
(1, 'fred'),
(2, 'Joe'), -- Changed value of existing tuple
(3, 'Alan') -- Added new tuple
Das ist es, wovon die Leute reden, wenn sie über ein "Upsert" sprechen. Entscheidend ist, dass jeder Ansatz sicher ist, wenn mehrere Transaktionen auf derselben Tabelle arbeiten - entweder durch explizites Sperren oder durch andere Maßnahmen gegen die daraus resultierenden Race Conditions.
Dieses Thema wird ausführlich auf https://stackoverflow.com/q/1109061/398670 diskutiert, aber dort geht es um Alternativen zur MySQL-Syntax, und es sind im Laufe der Zeit eine ganze Menge unzusammenhängender Details hinzugekommen. Ich arbeite an definitiven Antworten.
Diese Techniken sind auch nützlich für "insert if not exists, otherwise do nothing", d.h. "insert ... on duplicate key ignore".
INSERT ... ON CONFLICT UPDATE
(und ON CONFLICT DO NOTHING
), d.h. Upsert.
Vergleich mit ON DUPLICATE KEY UPDATE
.
Kurze Erklärung.
Zur Verwendung siehe das Handbuch - insbesondere die Klausel conflict_action im Syntaxdiagramm, und den erklärenden Text.
Im Gegensatz zu den Lösungen für 9.4 und älter, die unten angegeben sind, funktioniert diese Funktion mit mehreren konfliktbehafteten Zeilen und erfordert kein exklusives Sperren oder eine Wiederholungsschleife.
Der Commit, der die Funktion hinzufügt, ist hier und die Diskussion um die Entwicklung ist hier.PostgreSQL hat keine eingebaute UPSERT
(oder MERGE
) Möglichkeit, und es ist sehr schwierig, dies angesichts der gleichzeitigen Nutzung effizient durchzuführen.
Dieser Artikel diskutiert das Problem in nützlichen Details.
Im Allgemeinen müssen Sie zwischen zwei Optionen wählen:
Die Verwendung einzelner Zeilen-Upserts in einer Wiederholungsschleife ist die vernünftigste Option, wenn viele Verbindungen gleichzeitig versuchen, Einfügungen vorzunehmen.
[Die PostgreSQL-Dokumentation enthält eine nützliche Prozedur, mit der Sie dies in einer Schleife innerhalb der Datenbank tun können (http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING). Im Gegensatz zu den meisten naiven Lösungen schützt es vor verlorenen Aktualisierungen und Einfügevorgängen. Sie funktioniert nur im Modus READ COMMITTED
und ist nur dann sicher, wenn sie das einzige ist, was Sie in der Transaktion tun. Die Funktion wird nicht korrekt funktionieren, wenn Trigger oder sekundäre eindeutige Schlüssel zu Verletzungen der Eindeutigkeit führen.
Diese Strategie ist sehr ineffizient. Wann immer es praktisch ist, sollten Sie die Arbeit in eine Warteschlange stellen und stattdessen ein Massen-Upsert wie unten beschrieben durchführen.
Viele Lösungsversuche für dieses Problem berücksichtigen keine Rollbacks, so dass sie zu unvollständigen Aktualisierungen führen. Zwei Transaktionen konkurrieren miteinander; eine von ihnen führt erfolgreich INSERT
durch; die andere bekommt einen Fehler wegen eines doppelten Schlüssels und führt stattdessen ein UPDATE
durch. Die "UPDATE"-Transaktion blockiert und wartet darauf, dass die "INSERT"-Transaktion einen Rollback oder eine Übergabe durchführt. Beim Rollback ergibt die erneute Prüfung der UPDATE
-Bedingung null Zeilen, so dass, obwohl das UPDATE
festgeschrieben wird, es nicht die erwartete Einfügung vorgenommen hat. Sie müssen die Anzahl der Ergebniszeilen überprüfen und gegebenenfalls einen neuen Versuch starten.
Einige Lösungsversuche lassen auch SELECT-Races außer Acht. Wenn Sie die offensichtliche und einfache Lösung versuchen:
-- 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;
versuchen, gibt es mehrere Fehlermöglichkeiten, wenn zwei gleichzeitig ausgeführt werden. Eine davon ist das bereits besprochene Problem mit einer erneuten Überprüfung der Aktualisierung. Eine andere ist, dass beide UPDATE
zur gleichen Zeit ausführen, Nullzeilen finden und fortfahren. Dann machen beide den "EXISTS"-Test, der vor dem "INSERT" stattfindet. Beide erhalten null Zeilen, also machen beide das INSERT
. Eine schlägt mit einem Fehler wegen eines doppelten Schlüssels fehl.
Aus diesem Grund brauchen Sie eine Wiederholungsschleife. Man könnte meinen, dass man Fehler durch doppelte Schlüssel oder verlorene Aktualisierungen mit cleverem SQL verhindern kann, aber das kann man nicht. Sie müssen die Zeilenzahl überprüfen oder Fehler bei doppelten Schlüsseln behandeln (je nach gewähltem Ansatz) und einen neuen Versuch starten.
Bitte entwickeln Sie nicht Ihre eigene Lösung für dieses Problem. Wie bei der Nachrichtenwarteschlange ist es wahrscheinlich falsch.
Manchmal möchte man einen Bulk-Upsert durchführen, bei dem man einen neuen Datensatz mit einem älteren bestehenden Datensatz zusammenführen möchte. Dies ist wesentlich effizienter als das Einfügen einzelner Zeilen und sollte daher immer bevorzugt werden. In diesem Fall gehen Sie normalerweise wie folgt vor:
CREATE
eine TEMPORARY
TabelleLOCK
der Zieltabelle IN EXCLUSIVE MODE
. Das erlaubt anderen Transaktionen, SELECT
zu machen, aber keine Änderungen an der Tabelle vorzunehmen.UPDATE ... FROM
bestehender Datensätze unter Verwendung der Werte in der Temp-Tabelle;COMMIT
, Freigabe der Sperre.
Zum Beispiel, für das in der Frage angegebene Beispiel, unter Verwendung von mehrwertigen INSERT
, um die temp-Tabelle zu füllen: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;
MERGE
im PostgreSQL-WikiMERGE
?Der SQL-Standard MERGE
hat eine schlecht definierte Gleichzeitigkeitssemantik und eignet sich nicht für Upserting ohne vorheriges Sperren einer Tabelle.
Es ist eine wirklich nützliche OLAP-Anweisung für die Zusammenführung von Daten, aber es ist nicht wirklich eine nützliche Lösung für ein gleichzeitiges sicheres Upsert. Es gibt viele Ratschläge für Leute, die andere DBMS benutzen, MERGE
für Upserts zu benutzen, aber das ist eigentlich falsch.
INSERT ... ON DUPLICATE KEY UPDATE
in MySQLMERGE
von MS SQL Server (aber siehe oben über MERGE
Probleme)MERGE
von Oracle (aber siehe oben über MERGE
-Probleme)Ich versuche, mit einer anderen Lösung für das Problem des einzelnen Einfügens mit den PostgreSQL-Versionen vor 9.5 beizutragen. Die Idee ist einfach zu versuchen, zuerst die Einfügung durchzuführen, und falls der Datensatz bereits vorhanden ist, ihn zu aktualisieren:
do $$
begin
insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
update testtable set somedata = 'Joe' where id = 2;
end $$;
Beachten Sie, dass diese Lösung nur angewendet werden kann, wenn es keine Löschungen von Zeilen der Tabelle gibt.
Ich weiß nicht, wie effizient diese Lösung ist, aber sie scheint mir vernünftig genug.