PostgreSQL INSERT ON CONFLICT: Korrekte Upsert-Operationen mit eindeutigen Zielen, DO NOTHING vs DO UPDATE

Identifizieren Sie die tatsächliche Eindeutigkeitsregel

Eine ON CONFLICT-Klausel löst Verstöße gegen Arbiter-Einschränkungen oder -Indizes auf. Dies sind eindeutige Einschränkungen (PRIMARY KEY, UNIQUE) oder eindeutige Indizes. Die Dokumentation besagt, dass für ON CONFLICT DO UPDATE ein conflict_target angegeben werden muss. Dieses Ziel gibt an, welche Konflikte die alternative Aktion auslösen, indem Arbiter-Indizes ausgewählt werden. Die Regel wird auf Datenbankebene und nicht durch Anwendungslogik erzwungen. Für die Tabelle inventory(sku text PRIMARY KEY, qty integer NOT NULL) ist die Primärschlüsselbeschränkung auf sku der Arbiter; jede Einfügung eines doppelten sku-Werts ist ein Konflikt.

Wählen Sie das Konfliktziel

Das Konfliktziel kann eine eindeutige Index-Inferenz, die Nennung von Spalten/Ausdrücken oder die direkte Nennung einer Einschränkung mit ON CONFLICT ON CONSTRAINT verwenden. Die Inferenz ist oft vorzuziehen. Für die Tabelle inventory ist der Primärschlüssel auf sku durch ON CONFLICT (sku) abgeleitet. Die Dokumentation weist darauf hin, dass die Inferenz alle eindeutigen Indizes auswählt, die genau die angegebenen Spalten/Ausdrücke enthalten. Wenn Sie eine Einschränkung direkt benennen, verwendet sie den Index, der mit dieser Einschränkung verbunden ist. Für teilweise eindeutige Indizes müssen Sie eine WHERE-Klausel im Konfliktziel einfügen, um das Indexprädikat abzugleichen.

Verwenden Sie DO NOTHING bewusst

ON CONFLICT DO NOTHING verwirft Zeilen, die einen Konflikt mit einer beliebigen Arbiter-Einschränkung oder einem beliebigen eindeutigen Index verursachen würden, stillschweigend. Das Konfliktziel ist optional; wenn es weggelassen wird, werden Konflikte mit allen verwendbaren eindeutigen Einschränkungen behandelt. Verwenden Sie dies, wenn Sie nur neue Zeilen einfügen und Duplikate ignorieren möchten. Zum Beispiel INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT DO NOTHING; tut nichts, wenn SKU 'A' bereits existiert. Die Anweisung ist erfolgreich und die zurückgegebene Anzahl gibt die tatsächlich eingefügten Zeilen an (null in diesem Fall).

Aktualisieren Sie von ausgeschlossenen Werten

ON CONFLICT DO UPDATE modifiziert die vorhandene, in Konflikt stehende Zeile. Innerhalb der SET-Klausel stellt der spezielle Alias EXCLUDED die Werte bereit, die ursprünglich für die Einfügung vorgeschlagen wurden. Die Dokumentation gibt an, dass beim Verweisen auf eine Spalte der Name der Tabelle nicht angegeben werden darf. Für das Inventory-Beispiel INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty; ersetzt die vorhandene Menge durch 3. Es addiert die Werte nicht. Um eine additive Aktualisierung durchzuführen, müssen Sie explizit auf die vorhandene Zeile verweisen: SET qty = inventory.qty + EXCLUDED.qty.

Verfolgen Sie ein Beispiel mit zwei Zeilen

Betrachten Sie das Einfügen von zwei Zeilen, wobei eine in Konflikt steht und eine nicht. Mit inventory, das anfänglich ('A', 2) enthält, führen Sie aus: INSERT INTO inventory (sku, qty) VALUES ('A', 3), ('B', 5) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;. Die Zeile für SKU 'A' steht in Konflikt und löst eine Aktualisierung aus, die die Menge auf 3 setzt. Die Zeile für SKU 'B' steht nicht in Konflikt und wird eingefügt. Der Befehl gibt INSERT 0 2 zurück, was anzeigt, dass zwei Zeilen verarbeitet wurden (eine aktualisiert, eine eingefügt). Der endgültige Zustand ist ('A', 3), ('B', 5).

Behandeln Sie doppelte Zeilen in einer Anweisung

PostgreSQL dokumentiert ON CONFLICT DO UPDATE als deterministisch: ein Befehl darf nicht mehr als einmal dieselbe vorhandene Zeile beeinflussen. Bei wiederholten vorgeschlagenen Schlüsseln, wie z. B. VALUES ('A', 3), ('A', 4), kann eine Kardinalitätsverletzung auftreten. Dies wird nicht als ein gewöhnlicher doppelter Schlüssel-Fehler beschrieben, der auftritt, bevor ON CONFLICT die Möglichkeit hat, zu handeln. Verlassen Sie sich nicht auf den ersten oder letzten vorgeschlagenen Wert, der gewinnt.

Deduplizieren Sie die Eingabe oder aggregieren Sie wiederholte Schlüssel gemäß einer expliziten Geschäftsregel, bevor Sie die Anweisung einreichen. Bei Ersetzungs-Mengen entscheiden Sie, welche Beobachtung gewinnen soll; bei additiven Mengen entscheiden Sie, ob Summieren angemessen ist. Bei einem Anweisungsfehler werden seine Änderungen nicht zu einer teilweise erfolgreichen Upsert-Operation. In einer expliziten Transaktion erholen Sie sich mit der Rollback- oder Savepoint-Richtlinie der Anwendung.

Verstehen Sie das konkurrierende Verhalten

ON CONFLICT DO UPDATE garantiert ein atomares INSERT- oder UPDATE-Ergebnis für jede Zeile, selbst unter hoher Konkurrenz. Die Dokumentation warnt jedoch, dass während CREATE INDEX CONCURRENTLY oder REINDEX CONCURRENTLY auf einem eindeutigen Index ausgeführt wird, INSERT ... ON CONFLICT auf derselben Tabelle unerwartet mit einer eindeutigen Verletzung fehlschlagen kann. Die Anweisung nimmt eine Sperre auf der in Konflikt stehenden Zeile. Wenn zwei konkurrierende Transaktionen versuchen, denselben Schlüssel einzufügen, wird eine erfolgreich mit einer Einfügung sein, und die andere wird in Konflikt geraten und die DO UPDATE-Aktion auf der nun vorhandenen Zeile ausführen.

Überprüfen Sie Einschränkungen über die Anweisung hinaus

Atomare Upsert garantiert keine genau-einmalige externe Verarbeitung. Das Wiederholen einer additiven Aktualisierung kann eine Menge erneut inkrementieren, es sei denn, die Anwendung dedupliziert die logische Operation. Andere Einschränkungen, Berechtigungen und Trigger können die Anweisung weiterhin ablehnen. Standardmäßig behandeln nullable eindeutige Spalten Nullwerte als unterschiedlich, wodurch mehrere Nullwerte zulässig sind, es sei denn, NULLS NOT DISTINCT ist angegeben. Teilweise eindeutige Indizes gelten nur für ihr Prädikat; die Konfliktziel-Inferenz muss einen geeigneten Arbiter auswählen.