postgresql - 使用 SELECT NOT EXISTS 在 INSERT 上出错重复键

Question

怎么会这样？

IntegrityError: (IntegrityError) duplicate key value violates unique constraint "r_u_pkey"  
DETAIL:  Key (r_id, u_id)=(2660, 10182) already exists.  
 'INSERT INTO r_u(r_id, u_id) SELECT %s, %s WHERE NOT EXISTS (
     SELECT 1 FROM r_u WHERE r_id = %s AND u_id = %s
 )' (2660, 10182, 2660, 10182)

(r_id, u_id) 上有主键:

CREATE TABLE r_u  
(  
  r_id integer NOT NULL,  
  u_id integer NOT NULL,  
  CONSTRAINT r_u_pkey PRIMARY KEY (r_id, u_id)  
)  

服务器是 Postgres 9.3.5 并且连接已自动提交。

Answer 1

好吧，我找到了原因和解决方案。 INSERT SELECT 不是原子的。在自动提交模式下必须在事务中使用显式锁定:

开始；
在共享行独占模式下锁定表 r_u；
插入 r_u(r_id, u_id) SELECT %s, %s WHERE NOT EXISTS (
SELECT 1 FROM r_u WHERE r_id = %s AND u_id = %s
)
提交；

来源:http://www.the-art-of-web.com/sql/upsert/