如何使用并发事务处理 PostgreSQL UPSERT 中的缺失行？

PostgreSQL UPSERT操作中处理缺失结果行

在PostgreSQL 9.5中，结合使用RETURNING和ON CONFLICT有时会在并发事务更新指定的冲突目标时导致行缺失。

当前已接受答案的缺点

当前已接受的答案在并发事务更新目标表多行时容易出现问题。虽然它可以防止行缺失，但它会引入其他副作用和与空更新相关的性能损失。

替代方案

无并发写入负载：

<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>

有并发写入负载

为了解决多个并发事务的竞争条件：

<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>

关键点：

• sel CTE 返回插入的行和选择的行，提供完整的输出结果集。
• ups CTE 处理缺失的行，用现有值更新它们。
• 输出结果集排除了并发事务更新的任何行，除非ups处理它们。

以上是如何使用并发事务处理 PostgreSQL UPSERT 中的缺失行？的详细内容。更多信息请关注PHP中文网其他相关文章！