The 目前接受的答案对于单个冲突目标、很少的冲突、小元组和没有触发器来说似乎没问题。它避免了并发问题1(见下文)用蛮力。简单的解决方案有其吸引力,副作用可能不太重要。
但对于所有其他情况,请执行以下操作:not无需更新相同的行。即使表面上看不出任何区别,各种副作用:
-
它可能会触发不应该触发的触发器。
-
它写入锁定“无辜”行,可能会产生并发事务的成本。
-
它可能会使该行看起来很新,尽管它很旧(事务时间戳)。
-
最重要的是, with PostgreSQL 的 MVCC 模型 UPDATE
为每个目标行写入新的行版本,无论行数据是否更改。这会导致 UPSERT 本身的性能损失,表膨胀,索引膨胀,表上后续操作的性能损失,VACUUM
成本。对少数重复项影响较小,但是massive对于大多数骗子来说。
Plus,有时不实用甚至不可能使用ON CONFLICT DO UPDATE
. 手册:
For ON CONFLICT DO UPDATE
, a conflict_target
必须提供。
A single如果涉及多个索引/约束,则不可能出现“冲突目标”。但这里有一个针对多个部分索引的相关解决方案:
- 基于具有 NULL 值的 UNIQUE 约束的 UPSERT
回到主题,您可以实现(几乎)相同的效果,而无需空更新和副作用。以下一些解决方案也适用于ON CONFLICT DO NOTHING
(没有“冲突目标”),抓住all可能出现的冲突——这可能是可取的,也可能是不可取的。
无并发写入负载
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
The source
列是一个可选的附加项,用于演示其工作原理。您实际上可能需要它来区分两种情况之间的差异(相对于空写入的另一个优势)。
决赛JOIN chats
之所以有效,是因为从附加的新插入的行数据修改CTE在基础表中尚不可见。 (同一 SQL 语句的所有部分都会看到基础表的相同快照。)
自从VALUES
表达式是独立的(不直接附加到INSERT
) Postgres 无法从目标列派生数据类型,您可能必须添加显式类型转换。手册:
When VALUES
用于INSERT
,这些值都是自动的
强制为相应目标列的数据类型。什么时候
它在其他上下文中使用,可能需要指定
正确的数据类型。如果条目都是带引号的文字常量,
强制第一个足以确定所有的假设类型。
查询本身(不包括副作用)可能会更昂贵一些few欺骗,由于 CTE 的开销和额外的SELECT
(这应该很便宜,因为根据定义,完美索引就存在 - 唯一约束是通过索引实现的)。
可能(快得多)更快many重复。额外写入的有效成本取决于许多因素。
但是这里有更少的副作用和隐藏成本任何状况之下。总体来说很可能更便宜。
附加序列仍然是高级的,因为填充了默认值before测试冲突。
关于 CTE:
- SELECT 类型查询是唯一可以嵌套的类型吗?
- 删除关系划分中重复的 SELECT 语句
具有并发写入负载
假设默认READ COMMITTED事务隔离。有关的:
防御竞争条件的最佳策略取决于确切的要求、表和 UPSERT 中的行数和大小、并发事务的数量、冲突的可能性、可用资源和其他因素......
并发问题1
如果并发事务已写入您的事务现在尝试 UPSERT 的行,则您的事务必须等待另一事务完成。
如果另一笔交易以ROLLBACK
(或任何错误,即自动ROLLBACK
),您的交易可以正常进行。可能存在的较小副作用:序列号中的间隙。但没有丢失行。
如果其他事务正常结束(隐式或显式)COMMIT
), your INSERT
将检测到冲突(UNIQUE
索引/约束是绝对的)和DO NOTHING
,因此也不返回该行。 (也无法锁定该行,如并发问题2下面,因为它是不可见.) The SELECT
从查询开始时看到相同的快照,也无法返回尚不可见的行。
结果集中缺少任何此类行(即使它们存在于基础表中)!
This 可能就这样。特别是如果您没有像示例中那样返回行并且满意地知道该行在那里。如果这还不够好,还有多种解决方法。
您可以检查输出的行数,如果与输入的行数不匹配,则重复该语句。对于罕见的情况可能已经足够了。重点是启动一个新查询(可以在同一事务中),然后它将看到新提交的行。
Or检查缺失的结果行within相同的查询和覆盖那些具有暴力技巧的人在阿莱克斯托尼的回答.
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;
这与上面的查询类似,但我们用 CTE 添加了一个步骤ups
,在我们返回之前complete结果集。最后一个 CTE 在大多数情况下不会执行任何操作。仅当返回结果中缺少行时,我们才使用暴力破解。
还需要更多的开销。与预先存在的行的冲突越多,这种方法就越有可能优于简单方法。
一个副作用:第二个 UPSERT 乱序写入行,因此它重新引入了死锁的可能性(见下文),如果三个或更多写入相同行的事务重叠。如果这是一个问题,您需要一个不同的解决方案 - 例如重复上面提到的整个声明。
并发问题2
如果并发事务可以写入受影响行的相关列,并且您必须确保您找到的行在同一事务的稍后阶段仍然存在,那么您可以锁定现有行CTE 便宜ins
(否则会解锁):
...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE -- never executed, but still locks the row
...
并添加一个锁定子句SELECT以及,像FOR UPDATE.
这使得竞争的写操作等待直到事务结束,此时所有锁都被释放。所以要简短。
更多细节和解释:
- 如何在 RETURNING from INSERT ... ON CONFLICT 中包含排除的行
- 函数中的 SELECT 或 INSERT 是否容易出现竞争条件?
僵局?
抵御僵局通过插入行一致的顺序. See:
- 尽管发生冲突但不执行任何操作,多行插入仍会发生死锁
数据类型和转换
现有表作为数据类型的模板...
独立的第一行数据的显式类型转换VALUES
表达可能会不方便。有办法解决这个问题。您可以使用任何现有关系(表、视图...)作为行模板。目标表是用例的明显选择。输入数据会自动强制转换为适当的类型,例如VALUES
的条款INSERT
:
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')
)
...
这对于某些数据类型不起作用。看:
...和名字
这也适用于all数据类型。
在插入表的所有(前导)列时,您可以省略列名称。假设表chats
示例中仅包含 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
)
...
旁白:不要使用保留字 like "user"
作为标识符。那是一把上了膛的步枪。使用合法的、小写的、不带引号的标识符。我把它替换为usr
.