剪报:PostgreSQL 15 MERGE 与 INSERT ... ON CONFLICT 的并发语义差异
PG 15 终于加上了标准 SQL 的 MERGE 语句,第一反应是以后是不是不用写 ON CONFLICT DO UPDATE 了。读完 Cybertec 的测试文章才发现两者的并发保证根本不是一回事:
MERGE does not guarantee atomicity against concurrent INSERTs under READ COMMITTED: if two sessions run MERGE for the same missing key simultaneously, both see NOT MATCHED and one can still fail with unique_violation!
简单记结论:
- 单条或小批量高频并发幂等写入(“有则更新,无则插入”),老老实实继续用
INSERT ... ON CONFLICT,它有底层 speculative insertion 锁兜底,绝不会抛唯一键冲突。 - 跑批同步整张临时表(既有更新、又有新增、还要顺带把源表消失的行
DELETE掉),这时候才是MERGE的主场。