Bagaimana untuk Mengendalikan Baris yang Hilang dalam UPSERT PostgreSQL dengan Transaksi Serentak?

Mengendalikan baris hasil yang hilang dalam operasi UPSERT PostgreSQL

Dalam PostgreSQL 9.5, menggunakan RETURNING dan ON CONFLICT bersama kadangkala mengakibatkan baris hilang apabila transaksi serentak mengemas kini sasaran bercanggah yang ditentukan.

Kelemahan jawapan yang diterima sekarang

Jawapan yang diterima pada masa ini terdedah kepada masalah apabila transaksi serentak mengemas kini berbilang baris dalam jadual sasaran. Walaupun ia menghalang baris yang hilang, ia memperkenalkan kesan sampingan lain dan penalti prestasi yang dikaitkan dengan kemas kini kosong.

Alternatif

Tiada beban tulis serentak:

<code class="language-sql">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);           -- 唯一索引的列</code>

Mempunyai beban tulis serentak

Untuk menyelesaikan syarat perlumbaan untuk berbilang transaksi serentak:

<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 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;</code>

Isi penting:

• sel CTE mengembalikan baris yang dimasukkan dan baris yang dipilih, memberikan set hasil output lengkap.
• ups CTE mengendalikan baris yang hilang, mengemas kininya dengan nilai sedia ada.
• Set hasil output tidak termasuk mana-mana baris yang dikemas kini oleh transaksi serentak melainkan mereka ups diproses.

Atas ialah kandungan terperinci Bagaimana untuk Mengendalikan Baris yang Hilang dalam UPSERT PostgreSQL dengan Transaksi Serentak?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!