INSERT ... ON CONFLICT: War es ein INSERT oder ein UPDATE?

Ich mache gerade die ersten Experimente mit INSERT und einer ON CONFLICT-Klausel. Klappt im Prinzip, nur: ich hätte gerne die Information, was denn nun passiert ist: ist ein neuer Satz entstanden oder ein vorhandener geändert worden? Das bekomme ich nicht hin und finde nichts dazu (Dokumentation, StackOverflow, dieses Forum, Suchmaschine mit “UPSERT” gefüttert).

Mein erster Gedanke war: RETURNING ‘INSERTED’ am Ende des INSERT-Befehls, vor der ON CONFLICT-Klausel, und RETURNING ‘UPDATED’ am Ende. Das ist syntaktisch falsch. Zweite Idee: RETURNING EXCLUDED.feldname am Ende, in der Hoffnung, dass das für UPDATE ein reguläres Ergebnis und für INSERT entweder NULL oder einen Fehler gibt. Gibt nur leider auch für UPDATE einen Fehler.

Ich bin überzeugt, dass die Information irgendwo in den Eingeweiden von PostgreSQL steckt und ich nur die richtige Stelle in der Dokumentation finden müsste - aber eben das gelingt mir nicht.

Der Vollständigkeit halber: PostgreSQL 11.2, Windows 10.

So ganz trivial bzw. offensichtlich ist das nicht, aber das Problem hatten schon andere.

z.B. hier: https://stackoverflow.com/questions/40878027

Vielen Dank, das hat mir weitergeholfen. Meine eigene Suche in StackOverflow war bei weitem nicht gründlich genug. So richtig gut gefallen mir die beschriebenen Lösungen allerdings nicht. Wahrscheinlich passt im konkreten Fall der alte MySQL-Spruch “Das macht die Anwendung”, weil es um Datensätze geht, die einzeln eingegeben bzw. bearbeitet werden. Da kann ich genau so gut und besser getrennte INSERT- und UPDATE-Befehle nehmen.

Bei einem undokumentierten Feature wäre ich auch skeptisch.
Was mich etwas irritiert: Es gibt doch in pg Synchronisatios Lösungen via Trigger, spätestens damit muss die Info doch greifbar sein.

Mein Ansatz wäre dann eher eine Ableitung aus den Daten, je nach Verfügbarkeit. Insert date - change date oder primary key - current sequence usw.
Außerdem kann man sich gerade in PG auch eine SP hernehmen, die alles macht, wie man möchte. An der Stelle wäre dann auch der Verdacht:
Wenn ich unbedingt wissen möchte, ob es insert oder update war, dann vielleicht, weil beim insert z.B. auch untergeordnete Datensätze anzulegen wären?
Auch das könnte ich in eine smarten SP alles mundgerecht erledigen.

Undokumentiert stand in einem der verlinkten Beiträge auf SO. War dann vielleicht zum Beitragszeitpunkt undokumentiert. Hab das nicht weiterverfolgt.

Zu Tigger: Ich meinte folgendes, wenn die Sync Trigger sauber mitschreiben können, welche Statements es waren, Insert oder Update und darauf basierend DDL Statements für eine Slave Tabelle produzieren, muss die notwendige Info zur Differenzierung von Insert oder Update durch den Trigger greifbar sein.

Die Bemerkung vom “undokumentierten Feature” stammt aus eben dem Thread, den castorp verlinkt hat und der die nähere Erklärung für die xmax-Abfrage enthält. Ich habe jetzt allerdings auch nicht in die PostgreSQL-Dokumentation von damals geschaut. Von mir aus hätte ich aus dem Dokumentationstext für PostgreSQL 11 aber eher nicht gefolgert, dass die aktuelle Version eines Datensatzes nach UPDATE xmax > 0 hat - und zwar zuverlässig.

Im Trigger (AFTER INSERT OR UPDATE) weiß TG_OP, ob es ein UPDATE oder ein INSERT war; ob das UPDATE aber durch eine ON CONFLICT-Klausel oder durch einen normalen UPDATE-Befehl ausgelöst wurde, weiß der Trigger nicht.

Da kommt’s dann halt wohl wirklich darauf an, wofür die Information gebraucht wird. Ich habe jetzt und hier die Beispielanwendung in einem Lehrbuch, dessen eigentliches Thema eine Python-GUI-Bibliothek ist. Da soll dann die Datenhaltung von CSV-Dateien auf eine PostgreSQL-Datenbank umgestellt werden, und mir missfällt die Definition dieser Datenbank (zusammengesetzte Primärschlüssel, die dann auch noch als Fremdschlüssel dienen). Beim Anpassen der Anwendung dachte ich, INSERT … ON CONFLICT sei eine gute Idee. Ob ein Satz neu oder bearbeitet ist, soll im GUI angezeigt werden - und da finde ich dann doch, davon muss die Datenbank nichts wissen, das kann und muss in der Anwendung geregelt werden. Das spricht m.E. für den gegebenen Fall gegen einen Trigger und gegen eine gespeicherte Prozedur, aber nicht gegen “RETURNING (xmax = 0)” in der Abfrage.

Jedenfalls habe ich was Neues gelernt.