Die EXCLUDED-Pseudotabelle in PostgreSQL stellt die Zeile dar, die während einer ON CONFLICT-Operation zum Einfügen vorgeschlagen wird. Historisch gesehen nehmen Entwickler, die aus MySQL oder Oracle-Umgebungen migrieren, oft an, dass direkte Gleichheitsvergleiche (=) ausreichen, um Wertänderungen innerhalb von Upsert-Mustern zu erkennen. Die dreifwertige Logik des SQL-Standards legt jedoch fest, dass NULL einen unbekannten Zustand darstellt, was bedeutet, dass NULL = NULL zu NULL (unbekannt) und nicht zu TRUE ausgewertet wird.
Dies schafft ein kritisches Problem, wenn die Konfliktlösungsklausel versucht, Updates zu optimieren, indem sie eine WHERE-Klausel wie WHERE EXCLUDED.phone != users.phone hinzufügt. Wenn sowohl die vorhandene Zeile als auch die vorgeschlagene Zeile NULL für die Telefonspalte enthalten, gibt der Vergleich NULL zurück, was die WHERE-Prädikatsprüfung nicht besteht. Folglich überspringt die Datenbank das Update, auch wenn die Werte im Kontext der Geschäftlogik tatsächlich unterschiedlich sein könnten, oder sie nicht zwischen einem NULL in den neuen Daten und einem NULL in den alten Daten unterscheiden kann.
Die Lösung besteht darin, den IS DISTINCT FROM-Operator zu verwenden, der NULL als vergleichbaren Wert behandelt. Indem die Update-Klausel mit WHERE EXCLUDED.column IS DISTINCT FROM table.column strukturiert wird, gibt der Vergleich FALSE zurück, wenn beide Werte NULL sind (was keine Änderung anzeigt) und TRUE, wenn einer NULL und der andere nicht ist. Dies gewährleistet deterministisches Verhalten und verhindert unnötige Schreibvorgänge.
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
Ein Krankenhausnetzwerk musste täglich Patientendaten von 50 externen Kliniken in ein zentrales PostgreSQL-Datenlager synchronisieren. Jede Klinik exportierte CSV-Dateien, in denen fehlende Telefonnummern der Patienten als leere Zeichenfolgen erschienen, die der COPY-Befehl während der Eingabe in NULL konvertierte. Das vorhandene Python-ETL-Skript verwendete SQLAlchemy, um Bulk-Upserts mit ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone auszuführen.
Das Problem trat auf, als Klinikmitarbeiter berichteten, dass gültige Telefonnummern, die direkt ins zentrale System eingegeben wurden, nach der nächtlichen Synchronisation verschwanden. Ermittlungen ergaben, dass, als der externe Feed NULL (was eine unbekannte Telefonnummer anzeigt) sendete, bestehende gültige Nummern überschrieben wurden, da die SET-Klausel bedingungslos ausgeführt wurde. Das Hinzufügen eines naiven Filters WHERE EXCLUDED.phone != patient_records.phone schlug fehl, weil der Vergleich, wenn beide NULL waren, NULL (unbekannt) zurückgab, was zur fehlerhaften Überspringung des Updates führte. Als der neue Wert NULL und der alte nicht war, verhielt sich die Logik inkonsistent über verschiedene PostgreSQL-Minor-Versionen hinweg.
Drei Lösungen wurden bewertet.
Der erste Ansatz verwendete COALESCE ausschließlich in der SET-Klausel: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Dies verhinderte, dass mit NULL überschrieben wurde, zwang jedoch ein Update bei jedem Konflikt, was kostspielige B-Tree-Index-Neubauten für die Telefonspalte auslöste und Audit-Triggers auslöste, die "no-op"-Änderungen als legitime Modifikationen protokollierten. Dies erhöhte den WAL (Write-Ahead Log)-Verkehr um 300%, was zu einer Bedrohung für die Replikationsverzögerung führte und die Festplatten-I/O auslastete.
Die zweite Lösung versuchte explizite boolesche Logik zur Behandlung von NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Obwohl logisch korrekt, erforderte dieses ausführliche Muster eine sorgfältige Pflege über 15 nullable Spalten und verwirrte den Abfrageoptimierer. Der Planer gab die Indexscans zugunsten von sequenziellen Scans auf der 20-Millionen-Zeilen-Tabelle auf, was dazu führte, dass der ETL-Job sein sechs Stunden langes Wartungsfenster überschritt.
Die dritte Lösung implementierte IS DISTINCT FROM für alle nullable Spalten in der WHERE-Klausel. Dies lieferte ein prägnantes, sargables Prädikat, das echte Datenänderungen einschließlich NULL-Übergängen korrekt identifizierte. Es erlaubte Updates nur, wenn dies notwendig war, wodurch überflüssige Trigger-Ausführungen und WAL-Generierung vermieden wurden, während konsistente Abfragepläne beibehalten wurden.
Das Team wählte die dritte Lösung für kritische Kontaktfelder und die erste Lösung für nicht kritische Metadaten, bei denen der Schutz vor Überschreibung wichtiger war als die Leistung. Das Ergebnis war dramatisch: Die Dauer des Synchronisierungsjobs wurde von 45 Minuten auf 12 Minuten verkürzt, die Replikationsverzögerung stabilisierte sich unter fünf Sekunden, und die Vorfälle von "verschwundenen Telefonnummern" hörten in der ersten Woche nach der Bereitstellung vollständig auf.
Warum überspringt WHERE EXCLUDED.column != table.column Zeilen, wenn beide Werte NULL sind, und wie interagiert dies mit dem Aktualisierungsmechanismus von PostgreSQL?
Viele Kandidaten nehmen an, dass, wenn zwei NULL nicht gleich sind, der Vergleich TRUE zurückgeben sollte und das Update zulassen sollte. SQL verwendet jedoch dreifwertige Logik: NULL repräsentiert einen unbekannten Wert. Jeder Vergleich mit NULL (einschließlich NULL = NULL oder NULL != NULL) ergibt NULL (unbekannt) und nicht einen booleschen TRUE oder FALSE. In der WHERE-Klausel von PostgreSQL gehen nur Zeilen weiter, die zu TRUE ausgewertet werden; NULL wird als FALSE behandelt. Daher ist das Ergebnis des Vergleichs zweier NULL-Telefonnummern NULL, das Update wird übersprungen, und das System geht fälschlicherweise davon aus, dass keine Änderung erforderlich ist. IS DISTINCT FROM gibt für NULL vs NULL FALSE zurück und zeigt korrekt an, dass sie identisch sind, und überspringt das Update nur dann, wenn es angemessen ist, während es TRUE zurückgibt, wenn einer der Werte NULL und der andere nicht ist.
Wie beeinflusst die Reihenfolge der Spalten in einer mehrspaltigen einzigartigen Einschränkung die Leistung der ON CONFLICT-Auflösung, und was passiert, wenn das Konfliktziel nicht exakt mit der Indexdefinition übereinstimmt?
Kandidaten übersehen häufig, dass PostgreSQL erfordert, dass das Konfliktziel (die in ON CONFLICT (...) aufgeführten Spalten) genau mit der Definition des einzigartigen Index übereinstimmt, einschließlich der Spaltenreihenfolge und aller funktionalen Ausdrücke. Wenn ein eindeutiger Index auf (clinic_id, external_id) existiert, aber die Abfrage ON CONFLICT (external_id, clinic_id) angibt, kann der Planer möglicherweise den Index nicht ableiten und wirft einen Fehler "es gibt kein einzigartiges oder Ausschlusskriterium, das der ON CONFLICT-Spezifikation entspricht". Selbst wenn er erfolgreich ist, kann eine fehlerhafte Reihenfolge der Spalten verhindern, dass der Optimierer einen Index-nur-Scan verwendet, um das widersprüchliche Tupel zu finden, was zu einem Heap-Fetch führt und die I/O-Kosten erheblich erhöht.
Was ist der Unterschied zwischen der Verwendung von COALESCE(EXCLUDED.column, table.column) in der SET-Klausel gegenüber der Verwendung von WHERE EXCLUDED.column IS DISTINCT FROM table.column, insbesondere bezüglich der Triggerausführung und der Zeilenversionierung?
Die Verwendung von COALESCE in der SET-Klausel schreibt bedingungslos einen Wert in die Zeile (entweder die neuen Daten oder die beibehaltenen alten Daten). Dieser Vorgang erzeugt eine neue Zeilenversion (CTID), schreibt in den WAL und löst alle BEFORE- und AFTER-Trigger aus, die mit der Tabelle verbunden sind, selbst wenn der endgültige Wert identisch mit dem vorherigen Zustand bleibt. Dies erzeugt "Geräusch" in Audit-Tabellen und erhöht die Replikationslast. Im Gegensatz dazu verhindert die WHERE-Klausel mit IS DISTINCT FROM, dass die Zeilenänderung vollständig erfolgt, wenn keine tatsächliche Änderung eingetreten ist. Es wird keine neue Tupelversion erstellt, Trigger werden nicht ausgelöst, und die WAL-Generierung wird vermieden. Dieser Unterschied ist entscheidend für hochgradig skalierbare Systeme mit Audit-Logging oder Foreign-Key-Kaskaden, bei denen "no-op"-Updates erhebliche Überlastungen verursachen.