9.5 and newer:
PostgreSQL 9.5及更新版本支持INSERT ... ON CONFLICT (key) DO UPDATE
(和ON CONFLICT (key) DO NOTHING
),即upsert.
Comparison with ON DUPLICATE KEY UPDATE
Quick explanation
有关用法,请参见the manual,特别是语法图中的conflict_action子句和the explanatory text.
与下面给出的9.4及更早版本的解决方案不同,此功能适用于多个冲突行,不需要独占锁定或重试循环.
The commit adding the feature is here和the discussion around its development is here.
If you're on 9.5 and don't need to be backward-compatible you can stop reading now
9.4 and older:
PostgreSQL没有任何内置的UPSERT
(或MERGE
)功能,在并发使用的情况下高效地实现这一功能非常困难.
This article discusses the problem in useful detail
通常,您必须在两个选项中进行 Select :
- 重试循环中的单个插入/更新操作;或
- 锁定表并执行批合并
单行重试循环
如果希望多个连接同时try 执行插入,那么在重试循环中使用单独的行向上插入是合理的 Select .
The PostgreSQL documentation contains a useful procedure that'll let you do this in a loop inside the database.与大多数天真的解决方案不同,它可以防止更新丢失和插入竞争.它只会在READ COMMITTED
模式下工作,而且只有当它是您在事务中唯一做的事情时才是安全的.如果触发器或辅助唯一键导致唯一冲突,该函数将无法正常工作.
这种策略效率很低.只要可行,你应该排队工作,并按照下面的描述进行批量升级.
许多试图解决此问题的方法未能考虑回滚,因此导致不完整的更新.两项交易相互竞争;其中一人成功完成了INSERT
秒;另一个得到一个重复的密钥错误,并执行UPDATE
.UPDATE
块等待INSERT
块回滚或提交.当它回滚时,UPDATE
条件重新判断与零行匹配,因此即使UPDATE
提交,它实际上也没有完成预期的upsert.您必须判断结果行计数,并在必要时重试.
一些未遂的解决方案也未能考虑 Select 种族.如果你try 简单明了的方法:
-- 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
测试,在before和INSERT
之间进行.都是零行,所以都是INSERT
行.其中一个失败,出现重复密钥错误.
这就是为什么需要一个重试循环.你可能认为你可以用聪明的SQL防止重复的密钥错误或丢失的更新,但你不能.你需要判断行数或处理重复的密钥错误(取决于 Select 的方法),然后重试.
请不要为此推出自己的解决方案.就像消息队列一样,这可能是错误的.
带锁的批量插入
有时,您需要执行批量升级,其中有一个新的数据集,您希望将其合并到一个旧的现有数据集中.这比单排向上的效率高vastly倍,在可行的情况下应优先考虑.
在这种情况下,您通常会遵循以下过程:
例如,对于问题中给出的示例,使用多值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;
Related reading
What about MERGE
?
SQL标准MERGE
实际上定义的并发语义很差,不适合在不先锁定表的情况下进行升级.
对于数据合并来说,它是一个非常有用的OLAP语句,但对于并发安全的upsert来说,它实际上并不是一个有用的解决方案.有很多建议建议使用其他DBMS的人使用MERGE
来升级,但实际上这是错误的.
Other DBs: