如何在PostgreSQL中使用RETURNING和ON CONFLICT？

id如果没有冲突,我想返回新列,或者返回id冲突列的现有列.
可以这样做吗？如果是这样,怎么样？

在目前接受的答案似乎确定了一些冲突,小元组和没有触发器.并且它通过暴力避免并发问题1(见下文).简单的解决方案有其吸引力,副作用可能不那么重要.

但是,对于所有其他情况,请勿在不需要的情况下更新相同的行.即使您在表面上看不到任何差异,也会产生各种副作用:

它可能触发不应该触发的触发器.

它写入锁定"无辜"行,可能会产生并发事务的成本.

它可能会使行看起来很新,虽然它很旧(事务时间戳).

最重要的是,使用PostgreSQL的MVCC模型,无论行数据是否相同,都会以任一方式编写新的行版本.这会导致UPSERT本身的性能损失,表膨胀,索引膨胀,表上所有后续操作的性能损失,VACUUM成本.对于一些重复轻微的影响,但大规模的大部分受骗者.

没有空的更新和副作用,您可以(几乎)实现相同的效果.

没有并发写入负载

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

该ON CONFLICT DO UPDATE列是一个可选的附加内容,用于演示其工作原理.实际上,您可能需要它来区分两种情况(另一种​​优于空写的优势).

最终的ON CONFLICT DO UPDATE工作原理是因为附加的数据修改CTE中新插入的行在基础表中尚不可见.(同一SQL语句的所有部分都会看到基础表的相同快照.)

由于conflict_target表达式是独立的(不直接附加到ON CONFLICT DO NOTHING),Postgres无法从目标列派生数据类型,因此您可能必须添加显式类型转换.手册:

当source使用时JOIN chats,值全部自动强制转换为相应目标列的数据类型.当它在其他上下文中使用时,可能需要指定正确的数据类型.如果条目都是引用的文字常量,则强制第一个足以确定所有的假定类型.

由于CTE和附加的开销(由于完美索引在定义中存在 - 使用索引实现了唯一约束),查询本身对于少数欺骗可能有点贵VALUES.

许多重复可能(更快).额外写入的有效成本取决于许多因素.

但无论如何,副作用和隐藏成本都会减少.它总体上可能更便宜.

(附加序列仍然是高级的,因为在测试冲突之前会填写默认值.)

关于CTE:

SELECT类型查询是唯一可以嵌套的类型吗？

在关系分区中重复数据删除SELECT语句

具有并发写入负载

假设默认INSERT事务隔离.

关于dba.SE的相关答案,详细解释如下:

并发事务导致竞争条件,插入时具有唯一约束

防范竞争条件的最佳策略取决于确切的要求,表格和UPSERT中行的数量和大小,并发交易的数量,冲突的可能性,可用资源和其他因素......

并发问题1

如果并发事务已写入您的事务现在尝试UPSERT的行,则您的事务必须等待另一个事务完成.

如果另一个交易以VALUES(或任何错误,即自动INSERT)结束,您的交易可以正常进行.副作用小:序号中的间隙.但没有丢失的行.

如果另一个事务正常结束(隐式或显式SELECT),您READ COMMITTED将检测到冲突(ROLLBACK索引/约束是绝对的)ROLLBACK,因此也不会返回该行.(也无法锁定行,如下面的并发问题2所示,因为它不可见.)COMMIT从查询的开头看到相同的快照,也无法返回尚不可见的行.

结果集中缺少任何此类行(即使它们存在于基础表中)!

这可能是好的.特别是如果你没有像示例那样返回行,并且知道行存在就感到满意.如果这还不够好,可以采取各种方法.

您可以检查输出的行计数,如果它与输入的行计数不匹配,则重复该语句.对于罕见的情况可能足够好.关键是要启动一个新查询(可以在同一个事务中),然后查看新提交的行.

或者检查同一查询中是否缺少结果行,并用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;

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

还有更多的开销.与预先存在的行冲突越多,它就越有可能胜过简单的方法.

一个副作用:第二个UPSERT不按顺序写行,所以如果写入相同行的三个或更多事务重叠,它会重新引入死锁的可能性(见下文).如果这是一个问题,您需要一个不同的解决方案.

并发问题2

如果并发事务可以写入受影响行的相关列,并且您必须确保在同一事务的稍后阶段仍然存在您找到的行,则可以使用以下方法以低成本方式锁定行:

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

并添加一个锁定子句UNIQUE,如DO NOTHING.

这使得竞争写入操作等到事务结束时,所有锁定都被释放.所以要简短一点.

更多细节和解释:

如何在INSERT ... ON CONFLICT中包含RETURNING中的排除行

SELECT或INSERT是否容易出现竞争条件？

死锁？

通过以一致的顺序插入行来防御死锁.看到:

尽管存在冲突,但仍存在多行INSERT的死锁

数据类型和强制转换

现有表格作为数据类型的模板......

对于独立SELECT表达式中的第一行数据的显式类型转换可能是不方便的.有办法解决它.您可以使用任何现有关系(表,视图,...)作为行模板.目标表是用例的明显选择.输入数据被自动强制转换为适当的类型,如在一个ups的条款ins:

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

这对某些数据类型不起作用(在底部的链接答案中有解释).下一个技巧适用于所有数据类型:

......和名字

如果插入整行(表的所有列 - 或至少一组前导列),也可以省略列名.假设SELECT示例中的表只使用了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
   )
   ...

详细解释和更多替代方案:

更新多行时转换NULL类型

旁白:不要使用像FOR UPDATE标识符这样的保留字.那是一个装满脚的枪.使用合法的,小写的,不带引号的标识符.我换了它VALUES.

我有完全相同的问题,我使用'do update'而不是'什么都不做'来解决它,即使我没有任何更新.在你的情况下,它将是这样的:

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;

此查询将返回所有行,无论它们刚刚插入还是之前存在.

作为INSERT查询的扩展,Upsert 可以在约束冲突的情况下使用两种不同的行为来定义:DO NOTHING或DO UPDATE.

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

请注意,RETURNING什么都不返回,因为没有插入元组.现在DO UPDATE,有可能对元组执行操作存在冲突.首先请注意,定义一个约束将非常重要,该约束将用于定义存在冲突.

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)