Postgresql 9.5 和更新的支持INSERT ... ON CONFLICT (key) DO UPDATE
(和ON CONFLICT (key) DO NOTHING
),即 upsert。
有关用法,请参阅手册- 特别是语法图中的conflict_action子句和解释性文本。
与下面给出的 9.4 及更早版本的解决方案不同,此功能适用于多个冲突行,并且不需要排他锁定或重试循环。
如果您使用的是 9.5 并且不需要向后兼容,您现在可以停止阅读。
Postgresql 没有任何内置UPSERT
(或MERGE
)工具,在并发使用的情况下高效地做到这一点是非常困难的。
通常,您必须在两个选项之间进行选择:
如果您希望多个连接同时尝试执行插入,则在重试循环中使用单个行更新插入是合理的选择。
Postgresql 文档包含一个有用的过程,可让您在数据库内的循环中执行此操作。与大多数幼稚的解决方案不同,它可以防止丢失更新和插入竞争。不过,它只能在READ COMMITTED
模式下工作,并且只有当它是您在交易中唯一要做的事情时才是安全的。如果触发器或辅助唯一键导致唯一违规,该功能将无法正常工作。
这种策略非常低效。只要可行,您应该将工作排队并进行如下所述的批量 upsert。
许多尝试解决此问题的方法都没有考虑回滚,因此会导致更新不完整。两笔交易相互竞争;其中一个成功了INSERT
;另一个收到重复的密钥错误并UPDATE
改为执行。UPDATE
等待INSERT
回滚或提交的块。当它回滚时,UPDATE
条件重新检查匹配零行,因此即使UPDATE
提交它实际上并没有完成您期望的更新插入。您必须检查结果行计数并在必要时重试。
一些尝试的解决方案也没有考虑 SELECT 比赛。如果您尝试显而易见且简单的方法:
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.
BEGIN;
UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;
-- Remember, this is WRONG. Do NOT COPY IT.
INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);
COMMIT;
那么当两个同时运行时,会出现多种故障模式。一个是已经讨论过的更新重新检查问题。另一个是两者UPDATE
同时匹配零行并继续。然后,他们都做EXISTS
测试,这恰好之前的INSERT
。两者都得到零行,因此两者都执行INSERT
. 一个因重复密钥错误而失败。
这就是您需要重试循环的原因。您可能认为使用巧妙的 sql 可以防止重复键错误或丢失更新,但您不能。您需要检查行数或处理重复的键错误(取决于选择的方法)并重试。
请不要为此推出您自己的解决方案。就像消息队列一样,它可能是错误的。
有时您想要进行批量 upsert,其中您有一个新数据集,您希望将其合并到旧的现有数据集中。这大大超过各行upserts更高效,更应是首选,只要实用。
在这种情况下,您通常遵循以下过程:
例如,对于问题中给出的示例,使用多值INSERT
填充临时表:
BEGIN;
CREATE TEMPORARY TABLE newvals(id integer, somedata text);
INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
LOCK TABLE testtable IN EXCLUSIVE MODE;
UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;
INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;
COMMIT;