wake-up-neo.com

Wie verwende ich RETURNING mit ON CONFLICT in PostgreSQL?

Ich habe den folgenden UPSERT in PostgreSQL 9.5:

INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

Wenn keine Konflikte vorliegen, wird Folgendes zurückgegeben:

----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

Bei Konflikten werden jedoch keine Zeilen zurückgegeben:

----------
    | id |
----------

Ich möchte die neuen Spalten id zurückgeben, wenn keine Konflikte vorliegen, oder die vorhandenen Spalten id der in Konflikt stehenden Spalten zurückgeben.
Kann das gemacht werden? Wenn ja, wie?

120
zola

Ich hatte genau das gleiche Problem und löste es mit 'do update' anstelle von 'do nothing', obwohl ich nichts zu aktualisieren hatte. In deinem Fall wäre es ungefähr so:

INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO UPDATE SET name=EXCLUDED.name RETURNING id;

Diese Abfrage gibt alle Zeilen zurück, unabhängig davon, ob sie gerade eingefügt wurden oder bereits vorhanden waren.

68
Alextoni

Die derzeit akzeptierte Antwort scheint für ein einzelnes Konfliktziel in Ordnung zu sein, wenige Konflikte, kleine Tupel und keine Auslöser. Und es vermeidet Parallelitätsproblem 1 (siehe unten) mit roher Gewalt. Die einfache Lösung hat ihren Reiz, die Nebenwirkungen können weniger wichtig sein.

In allen anderen Fällen sollten Sie jedoch not identische Zeilen ohne Notwendigkeit aktualisieren. Auch wenn Sie keinen Unterschied auf der Oberfläche sehen, gibt es verschiedene Nebenwirkungen :

  • Es kann Trigger auslösen, die nicht abgefeuert werden sollten.

  • Es sperrt "unschuldige" Zeilen und verursacht möglicherweise Kosten für gleichzeitige Transaktionen.

  • Möglicherweise wird die Zeile neu angezeigt, obwohl sie alt ist (Transaktionszeitstempel).

  • Am wichtigsten ist , dass mit PostgreSQLs MVCC-Modell eine neue Zeilenversion so oder so geschrieben wird, unabhängig davon, ob die Zeilendaten identisch sind. Dies führt zu einer Leistungsbeeinträchtigung für den UPSERT selbst, Aufblähen der Tabelle, Aufblähen des Index, Leistungsbeeinträchtigung für alle nachfolgenden Operationen an der Tabelle, VACUUM Kosten. Ein geringfügiger Effekt für wenige Duplikate, aber massiv für meistens Dupes.

Plus , manchmal ist es nicht praktisch oder sogar möglich, ON CONFLICT DO UPDATE. Das Handbuch:

Zum ON CONFLICT DO UPDATE, a conflict_target muss angegeben werden.

Sie können (fast) dasselbe ohne leere Updates und Nebenwirkungen erreichen. Einige der folgenden Lösungen funktionieren auch mit ON CONFLICT DO NOTHING (kein "Konfliktziel"), um alle mögliche Konflikte aufzufangen. (Kann oder kann nicht wünschenswert sein.)

Ohne gleichzeitige Schreiblast

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

Die Spalte source ist eine optionale Ergänzung, um zu veranschaulichen, wie dies funktioniert. Möglicherweise benötigen Sie es tatsächlich, um den Unterschied zwischen beiden Fällen festzustellen (ein weiterer Vorteil gegenüber leeren Schreibvorgängen).

Die endgültige JOIN chats funktioniert, weil neu eingefügte Zeilen aus einem angehängten datenmodifizierenden CTE in der zugrunde liegenden Tabelle noch nicht sichtbar sind. (Alle Teile derselben SQL-Anweisung sehen dieselben Momentaufnahmen der zugrunde liegenden Tabellen.)

Da der Ausdruck VALUES frei steht (nicht direkt an einen Ausdruck INSERT angehängt), kann Postgres keine Datentypen aus den Zielspalten ableiten, und Sie müssen möglicherweise explizite Typumwandlungen hinzufügen. Das Handbuch:

Wenn VALUES in INSERT verwendet wird, werden alle Werte automatisch auf den Datentyp der entsprechenden Zielspalte berechnet. Wenn es in anderen Kontexten verwendet wird, muss möglicherweise der richtige Datentyp angegeben werden. Handelt es sich bei allen Einträgen um Literalkonstanten in Anführungszeichen, reicht das Erzwingen der ersten Konstante aus, um den angenommenen Typ für alle zu bestimmen.

Die Abfrage selbst kann für wenige Dupes etwas teurer sein, aufgrund des Overheads des CTE und des zusätzlichen SELECT (der billig sein sollte, da der perfekte Index vorhanden ist) definition - eine eindeutige Einschränkung wird mit einem Index implementiert).

Kann (viel) schneller sein für viele Duplikate. Die effektiven Kosten für zusätzliche Schreibvorgänge hängen von vielen Faktoren ab.

Es gibt aber auf jeden Fall weniger Nebenwirkungen und versteckte Kosten . Es ist wahrscheinlich insgesamt billiger.

(Angehängte Sequenzen sind immer noch fortgeschritten, da Standardwerte eingegeben werden before auf Konflikte prüfen.)

Über CTEs:

Mit gleichzeitiger Schreiblast

Angenommene Standardeinstellung READ COMMITTED Transaktionsisolation.

Verwandte Antwort auf dba.SE mit ausführlicher Erklärung:

Die beste Strategie zur Abwehr von Wettkampfbedingungen hängt von den genauen Anforderungen, der Anzahl und Größe der Zeilen in der Tabelle und in den UPSERTs, der Anzahl gleichzeitiger Transaktionen, der Wahrscheinlichkeit von Konflikten, verfügbaren Ressourcen und anderen Faktoren ab.

Parallelitätsproblem 1

Wenn eine gleichzeitige Transaktion in eine Zeile geschrieben wurde, die Ihre Transaktion jetzt an UPSERT sendet, muss Ihre Transaktion warten, bis die andere abgeschlossen ist.

Wenn die andere Transaktion mit ROLLBACK (oder einem Fehler, d. H. Automatisch ROLLBACK) endet, kann Ihre Transaktion normal fortgesetzt werden. Kleiner Nebeneffekt: Lücken in den fortlaufenden Nummern. Aber keine fehlenden Zeilen.

Wenn die andere Transaktion normal endet (implizit oder explizit COMMIT), erkennt Ihr INSERT einen Konflikt (der Index/die Einschränkung UNIQUE ist absolut) und DO NOTHING, daher auch nicht die Zeile zurück. (Kann die Zeile auch nicht sperren, wie in Parallelitätsproblem 2 unten gezeigt, da es nicht sichtbar ist.) Das SELECT sieht den gleichen Schnappschuss von der Beginn der Abfrage und kann auch nicht die noch unsichtbare Zeile zurückgeben.

Solche Zeilen fehlen in der Ergebnismenge (obwohl sie in der zugrunde liegenden Tabelle vorhanden sind) !

Dies kann in Ordnung sein wie es ist . Vor allem, wenn Sie nicht wie im Beispiel Zeilen zurückgeben und zufrieden sind, dass die Zeile vorhanden ist. Wenn das nicht gut genug ist, gibt es verschiedene Möglichkeiten, es zu umgehen.

Sie können die Zeilenanzahl der Ausgabe überprüfen und die Anweisung wiederholen, wenn sie nicht mit der Zeilenanzahl der Eingabe übereinstimmt. Kann für den seltenen Fall gut genug sein. Der Punkt ist, eine neue Abfrage zu starten (kann sich in derselben Transaktion befinden), in der dann die neu festgeschriebenen Zeilen angezeigt werden.

Oder nach fehlenden Ergebniszeilen suchen innerhalb der gleichen Abfrage und überschreiben derjenigen mit dem in gezeigten Brute-Force-Trick Alextonis Antwort .

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

Es ist wie in der obigen Abfrage, aber wir fügen einen weiteren Schritt mit dem CTE ups hinzu, bevor wir die Ergebnismenge complete zurückgeben . Dieser letzte CTE wird die meiste Zeit nichts ausrichten. Nur wenn Zeilen aus dem zurückgegebenen Ergebnis fehlen, wenden wir rohe Gewalt an.

Noch mehr Aufwand. Je mehr Konflikte mit vorhandenen Zeilen auftreten, desto wahrscheinlicher wird dies den einfachen Ansatz übertreffen.

Ein Nebeneffekt: Der 2. UPSERT schreibt die Zeilen in der falschen Reihenfolge, so dass die Möglichkeit von Deadlocks erneut eingeführt wird (siehe unten), wenn drei oder mehr Transaktionen, die in dieselben Zeilen schreiben, überlappen. Wenn das ein Problem ist, brauchen Sie eine andere Lösung.

Parallelitätsproblem 2

Wenn gleichzeitige Transaktionen in betroffene Spalten betroffener Zeilen schreiben können und Sie sicherstellen müssen, dass die gefundenen Zeilen zu einem späteren Zeitpunkt in derselben Transaktion noch vorhanden sind, können Sie Zeilen sperren günstig mit:

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

Fügen Sie dem SELECT auch eine Locking-Klausel hinzu, wie FOR UPDATE .

Dadurch warten konkurrierende Schreibvorgänge bis zum Ende der Transaktion, wenn alle Sperren freigegeben sind. Also sei kurz.

Weitere Details und Erläuterungen:

Deadlocks?

Verteidigen Sie sich gegen Deadlocks , indem Sie Zeilen in konsistenter Reihenfolge einfügen. Sehen:

Datentypen und Casts

Bestehende Tabelle als Vorlage für Datentypen ...

Explizite Typumwandlungen für die erste Datenzeile im freistehenden Ausdruck VALUES sind möglicherweise unpraktisch. Es gibt Möglichkeiten, das zu umgehen. Sie können jede vorhandene Beziehung (Tabelle, Ansicht, ...) als Zeilenvorlage verwenden. Die Zieltabelle ist die offensichtliche Wahl für den Anwendungsfall. Eingabedaten werden automatisch zu geeigneten Typen gezwungen, wie in einer VALUES -Klausel eines INSERT:

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
   )
   ...

Dies funktioniert bei einigen Datentypen nicht (Erklärung in der verknüpften Antwort unten). Der nächste Trick funktioniert für alle Datentypen:

... und Namen

Wenn Sie ganze Zeilen einfügen (alle Spalten der Tabelle - oder zumindest eine Reihe von führenden Spalten), können Sie auch Spaltennamen weglassen. Angenommen, in der Tabelle chats werden nur die 3 Spalten verwendet:

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...

Detaillierte Erklärung und weitere Alternativen:


Nebenbei: Verwenden Sie keine reservierten Wörter wie "user" als Bezeichner. Das ist eine geladene Fußwaffe. Verwenden Sie legale Bezeichner ohne Anführungszeichen in Kleinbuchstaben. Ich habe es durch usr ersetzt.

154

Upsert, eine Erweiterung der Abfrage INSERT, kann im Falle eines Einschränkungskonflikts mit zwei verschiedenen Verhalten definiert werden: DO NOTHING oder DO UPDATE.

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

Beachten Sie auch, dass RETURNING nichts zurückgibt, da keine Tupel eingefügt wurden. Jetzt mit DO UPDATE, es ist möglich, Operationen auf dem Tupel auszuführen, mit dem ein Konflikt besteht. Beachten Sie zunächst, dass es wichtig ist, eine Einschränkung zu definieren, mit der definiert wird, dass ein Konflikt vorliegt.

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)
13
Jaumzera