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

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
bb1898
Neuer Benutzer
Beiträge: 22
Registriert: Di 18. Aug 2009, 17:56

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

Beitrag von bb1898 » Di 16. Apr 2019, 22:00

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.

castorp
Erfahrener Benutzer
Beiträge: 1126
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

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

Beitrag von castorp » Mi 17. Apr 2019, 07:52

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

z.B. hier: https://stackoverflow.com/questions/40878027
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

bb1898
Neuer Benutzer
Beiträge: 22
Registriert: Di 18. Aug 2009, 17:56

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

Beitrag von bb1898 » Do 18. Apr 2019, 20:16

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.

castorp
Erfahrener Benutzer
Beiträge: 1126
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

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

Beitrag von castorp » Do 18. Apr 2019, 21:22

bb1898 hat geschrieben:
Do 18. Apr 2019, 20:16
So richtig gut gefallen mir die beschriebenen Lösungen allerdings nicht.
Warum nicht? Sie funktioniert und ist zuverlässig.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

pogomips
Erfahrener Benutzer
Beiträge: 100
Registriert: Fr 18. Nov 2016, 09:55

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

Beitrag von pogomips » Fr 19. Apr 2019, 08:58

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.

castorp
Erfahrener Benutzer
Beiträge: 1126
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

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

Beitrag von castorp » Fr 19. Apr 2019, 10:52

Das ist nicht undokumentiert https://www.postgresql.org/docs/current ... lumns.html

Laurenz Albe hat das auch nochmal im Detail erklärt: https://stackoverflow.com/questions/39058213
pogomips hat geschrieben:
Fr 19. Apr 2019, 08:58
Es gibt doch in pg Synchronisatios Lösungen via Trigger, spätestens damit muss die Info doch greifbar sein.
Für das Feuern des Trigger ist es unerheblich was der auslösende Befehl ist. Ein UPDATE Trigger wird sowohl von einem normalen UPDATE als auch von einem INSERT ON CONFLICT DO UPDATE ausgelöst.

Wenn ein INSERT ON CONFLICT DO UPDATE zwei Datensätze einfügt und drei aktualisiert (UPDATE), dann wird ein entsprechender Insert (Row level) Trigger zweimal gefeuert, und ein entsprechender Update (row level) Trigger dreimal.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

pogomips
Erfahrener Benutzer
Beiträge: 100
Registriert: Fr 18. Nov 2016, 09:55

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

Beitrag von pogomips » Fr 19. Apr 2019, 18:22

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.

bb1898
Neuer Benutzer
Beiträge: 22
Registriert: Di 18. Aug 2009, 17:56

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

Beitrag von bb1898 » So 21. Apr 2019, 12:16

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.

pogomips
Erfahrener Benutzer
Beiträge: 100
Registriert: Fr 18. Nov 2016, 09:55

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

Beitrag von pogomips » Mi 24. Apr 2019, 20:18

Widersprichst Du Dir jetzt?
bb1898 hat geschrieben:
Di 16. Apr 2019, 22:00
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?
Neu oder vorhanden ist für mich Insert oder Update.
Insert oder Update sollte per Trigger funktionieren.
Was das ausgelöst hat, ist eine andere Frage.

Auch das könnte per Trigger ermittelt werden, glaub ich. Bin mir grad nicht sicher, ob ich da schon das generierte Statement für die Replikation vor Augen habe oder ob es das Original Statement ist.

Antworten