Wie gehe ich mit fehlenden Zeilen in PostgreSQL-UPSERTs bei gleichzeitigen Transaktionen um?

Behandlung fehlender Ergebniszeilen im PostgreSQL-UPSERT-Vorgang

In PostgreSQL 9.5 führte die gemeinsame Verwendung von RETURNING und ON CONFLICT manchmal zu fehlenden Zeilen, wenn gleichzeitige Transaktionen bestimmte widersprüchliche Ziele aktualisierten.

Nachteile der derzeit akzeptierten Antwort

Die derzeit akzeptierte Antwort ist anfällig für Probleme, wenn gleichzeitige Transaktionen mehrere Zeilen in der Zieltabelle aktualisieren. Es verhindert zwar das Fehlen von Zeilen, führt jedoch zu anderen Nebenwirkungen und Leistungseinbußen im Zusammenhang mit leeren Aktualisierungen.

Alternativen

Keine gleichzeitige Schreiblast:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- 第一行中的类型转换
    , ('foo2', 'bar2', 'bob2')
    -- 更多？
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- 返回更多列？
   )
SELECT 'i' AS source                           -- 'i' 代表'插入'
     , id  --, usr, contact                    -- 返回更多列？
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' 代表'选择'
     , c.id  --, usr, contact                  -- 返回更多列？
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- 唯一索引的列

Mit gleichzeitiger Schreiblast

So lösen Sie Race-Bedingungen für mehrere gleichzeitige Transaktionen auf:

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 NOTHING
   RETURNING id, usr, contact                   -- 我们需要唯一的列来进行后续连接
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' 代表'插入'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' 代表'选择'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- 罕见的极端情况
   INSERT INTO chats AS c (usr, contact, name)  -- 另一个UPSERT，不仅仅是UPDATE
   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'::"char" AS source              -- 'u' 代表更新
           , id  --, usr, contact               -- 返回更多列？
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

Wichtige Punkte:

• sel CTE gibt eingefügte und ausgewählte Zeilen zurück und stellt den vollständigen Ausgabeergebnissatz bereit.
• ups CTE behandelt fehlende Zeilen und aktualisiert sie mit vorhandenen Werten.
• Der Ausgabeergebnissatz schließt alle Zeilen aus, die durch gleichzeitige Transaktionen aktualisiert wurden, es sei denn, sie werden ups verarbeitet.

Das obige ist der detaillierte Inhalt vonWie gehe ich mit fehlenden Zeilen in PostgreSQL-UPSERTs bei gleichzeitigen Transaktionen um?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!