Wie verwende ich die RETURNING-Klausel von PostgreSQL zuverlässig mit ON CONFLICT DO NOTHING?

PostgreSQLs RETURNING-Klausel und ON CONFLICT DO NOTHING

Die RETURNING-Klausel von PostgreSQL ist für das Abrufen von Daten aus neu eingefügten Zeilen nach einem INSERT-Vorgang von unschätzbarem Wert. Dies ist besonders nützlich bei UPSERTs (Einfügen oder Aktualisieren) mit ON CONFLICT. Bei der Verwendung von ON CONFLICT DO NOTHING entsteht eine häufige Herausforderung: Wenn kein Konflikt besteht, liefert RETURNING keine Ergebnisse.

Auch wenn der Versuch, übereinstimmende Zeilen zu aktualisieren, um RETURNING auszulösen, wie eine Lösung erscheinen mag, wird dringend davon abgeraten. Zu den möglichen Nachteilen gehören:

• Unnötige Triggeraktivierungen
• Unbeabsichtigte Schreibsperre von Zeilen
• Vorhandene Zeilen werden fälschlicherweise als neu dargestellt
• Leistungsabfall aufgrund übermäßiger Zeilenversionierung

Es gibt effizientere und robustere Alternativen.

Umgang mit Szenarien ohne gleichzeitige Schreibvorgänge:

Für Umgebungen ohne nennenswerte gleichzeitige Schreibaktivität bietet ein CTE (Common Table Expression) in Kombination mit einem abschließenden JOIN eine elegante Lösung. Dadurch wird sichergestellt, dass sowohl eingefügte als auch vorhandene Zeilen in der Ausgabe enthalten sind:

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

Adressierung gleichzeitiger Schreiblasten:

Gleichzeitige Schreibvorgänge führen zu Komplexität. Wenn eine andere Transaktion eine Zeile ändert, bevor Ihr INSERT abgeschlossen ist, kann es zu Inkonsistenzen kommen.

Um dies zu mildern, sollten Sie erwägen, vorhandene Zeilen frühzeitig zu sperren. Ein zweiter UPSERT-Schritt kann alle fehlenden Zeilen behandeln:

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = name WHERE FALSE  -- Locks the row without updating
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats AS c (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;</code>

Diese Ansätze geben zuverlässig Daten sowohl für eingefügte als auch für vorhandene Zeilen zurück, selbst unter gleichzeitigem Schreibdruck.

Das obige ist der detaillierte Inhalt vonWie verwende ich die RETURNING-Klausel von PostgreSQL zuverlässig mit ON CONFLICT DO NOTHING?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!