在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不是原子。在自动提交模式下，必须在事务中使用显式锁定：

BEGIN; LOCK TABLE r_u IN SHARE ROW EXCLUSIVE MODE; 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 ) COMMIT;

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