如何在PostgreSQL中使用返回与ON CONFLICT？

Question

我在PostgreSQL 9.5中有以下UPSERT：

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

如果没有冲突，它会返回如下内容：

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

但如果存在冲突，则不会返回任何行：

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

如果没有冲突，我想返回新的id列，或者返回冲突列的现有id列<这能做到吗？如果是，怎么做？

Answer 1

WITH e AS(
    INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
)
SELECT * FROM e
UNION
    SELECT id FROM chats WHERE user=$1, contact=$2;

在冲突上使用的主要目的是避免抛出错误，但它不会导致行返回。因此，我们需要另一个选择来获取现有id。

在这个SQL中，如果冲突失败，它将不返回任何内容，然后第二个SELECT将获取现有行；如果插入成功，那么将有两条相同的记录，然后我们需要UNION来合并结果。

Answer 2

我遇到了完全相同的问题，我用“做更新”而不是“什么都不做”来解决它，尽管我没有什么要更新的。在你的情况下，会是这样的：

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;

此查询将返回所有行，无论它们刚刚插入还是以前存在。

Answer 3

目前接受的答案似乎可以用于单个冲突目标、很少冲突、小元组和没有触发器。它用暴力避免了并发问题1（见下文）。简单的解决方案有它的吸引力，副作用可能不那么重要。

但是，对于所有其他情况，不要无需更新相同的行。即使你看不到表面上的差异，也有各种各样的副作用：

>

它可能会触发不应该触发的触发器。

它写锁“无害”行，可能会导致并发事务的成本。

这可能会使该行看起来很新，尽管它很旧（事务时间戳）。

最重要的是，使用PostgreSQL的MVCC模型，无论行数据是否更改，每次更新都会编写一个新的行版本。这会导致UPSERT本身的性能损失、表膨胀、索引膨胀、表上后续操作的性能损失、VACUUMcost。对少数复制品的影响较小，但对大多数复制品的影响较大。

此外，有时在冲突中使用并不实用，甚至不可能更新。手册：

对于冲突DO UPDATE上的，必须提供冲突_目标。

如果涉及多个索引/约束，单个“冲突目标”是不可能的。但是这里有一个针对多个部分索引的相关解决方案：

• 基于具有NULL值的唯一约束的UPSERT

回到主题，您可以在没有空更新和副作用的情况下实现（几乎）相同的目标。以下一些解决方案也适用于ON CONFLICT DO NOTH（没有冲突目标），以捕获所有可能出现的冲突——这可能是可取的，也可能不是可取的。

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

source列是一个可选的添加项，用于演示其工作原理。实际上，您可能需要它来区分这两种情况（相对于空写的另一个优势）。

最后的JOIN chats有效，因为从附加的数据修改CTE中新插入的行在基础表中还不可见。（同一SQL语句的所有部分都可以看到基础表的相同快照。）

因为VALUES表达式是独立的（不是直接附加到INSERT）Postgres不能从目标列派生数据类型，您可能必须添加显式类型转换。手册：

当在插入中使用值时，所有值都会自动强制为相应目标列的数据类型。在其他上下文中使用时，可能需要指定正确的数据类型。如果条目都是带引号的文字常量，强制第一个就足以确定所有条目的假定类型。

由于CTE的开销和额外的SELECT（这应该是便宜的，因为根据定义，完美的索引已经存在了——一个唯一的约束是通过索引实现的），对于少数重复，查询本身（不计算副作用）可能会稍微贵一些。

对于许多复制品来说，速度可能（快得多）。额外写入的有效成本取决于许多因素。

但是在任何情况下都有更少的副作用和隐藏成本。总的来说，它很可能更便宜。

附加的序列仍然是高级的，因为默认值是在测试冲突之前填写的。

关于CTE：

• 选择类型查询是唯一可以嵌套的类型吗
• 关系分割中的重复数据消除SELECT语句

假设默认的读取提交的事务隔离。相关的：

• 并发事务会导致在插入时具有唯一约束的竞争条件

防御竞争条件的最佳策略取决于确切的需求、表和UPSERT中的行数和大小、并发事务数、冲突的可能性、可用资源和其他因素...

如果一个并发事务已写入一行，而您的事务现在尝试向上插入该行，则您的事务必须等待另一个事务完成。

如果另一个事务以回滚（或任何错误，即自动回滚）结束，则您的事务可以正常进行。可能的副作用很小：序列号中的间隔。但没有遗漏行。

如果其他事务正常结束（隐式或显式COMMIT），则您的INSERT将检测到冲突（UniQUE索引/约束是绝对的），并且DO NO，因此也不会返回行。（也不能锁定行，如下面的并发问题2中所示，因为它不可见。）SELECT从查询开始就看到相同的快照，并且也不能返回尚未可见的行。

结果集中缺少任何这样的行（即使它们存在于基础表中）！

这可能还可以。尤其是如果您没有像示例中那样返回行，并且知道该行在那里时感到满意。如果这还不够好，有多种方法可以解决。

您可以检查输出的行计数，如果与输入的行计数不匹配，可以重复语句。对于罕见的情况来说，这可能已经足够好了。重点是启动一个新的查询（可以在同一个事务中），然后它将看到新提交的行。

或者检查同一查询中缺少的结果行，并使用Alextoni的答案中演示的暴力技巧覆盖这些结果行。

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;

就像上面的查询一样，但是在返回完整的结果集之前，我们用CTEup添加了一个步骤。最后一个CTE大部分时间都不会做任何事情。只有当返回的结果中缺少行时，我们才使用暴力。

更多的开销。与预先存在的行的冲突越多，其性能就越有可能优于简单方法。

一个副作用是：第二次UPSERT会无序写入行，因此如果写入同一行的三个或更多事务重叠，它会重新引入死锁的可能性（见下文）。如果这是一个问题，你需要一个不同的解决方案——比如重复上面提到的整个陈述。

如果并发事务可以写入受影响行的相关列，并且您必须确保在同一事务的稍后阶段找到的行仍然存在，则可以在CTEins（否则将解锁）中廉价地锁定现有行：

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

并将一个锁定子句添加到选择中，如更新。

这使得相互竞争的写操作等待到事务结束时，此时所有锁都被释放。所以要简短。

更多细节和解释：

• 如何在从插入返回时包含排除的行。。。关于冲突

通过以一致的顺序插入行来防止死锁。请参见：

• 带多行插入的死锁，尽管存在冲突，但什么也不做

为独立的VALUES表达式中的第一行数据进行显式类型转换可能不方便。有一些方法可以绕过它。您可以使用任何现有的关系（表、视图、...）作为行模板。目标表是用例的明显选择。输入数据自动强制为适当的类型，如INSERT的VALUES子句中所示：

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 here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

这不适用于某些数据类型。见：

• 更新多行时转换为NULL类型

这也适用于所有数据类型。

在插入表的所有（前导）列时，可以省略列名。假设示例中的tablechats仅由UPSERT中使用的3列组成：

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

旁白：不要使用像“user”这样的保留字作为标识符。那是一把上膛的手枪。使用合法、小写、不带引号的标识符。我将其替换为usr。