PostgreSQL merge insert(insert into on conflict) ERRCODE_CARDINALITY_VIOLATION （Ensure that no rows proposed for insertion within the same command have duplicate constrained values）

背景

使用insert into on conflict 合并插入，如果一条SQL语句中，对一个KEY（冲突键，或冲突约束）多次发生冲突时，会报错。

原因：

                         * It is the user's responsibility to prevent this situation from  
                         * occurring.  These problems are why SQL-2003 similarly specifies  
                         * that for SQL MERGE, an exception must be raised in the event of  
                         * an attempt to update the same row twice.  

因为在SQL标准中，sql merge也有同样的问题，因为一次请求中对行的处理，顺序是不固定的。数据库不知道应该以哪条为最后需要保留的。

例子

postgres=# \set VERBOSITY verbose  
  
postgres=# insert into t_conf select * from (values (1,'test'), (1,'test1')) t(id,info) on conflict(id) do update set info=excluded.info;  
ERROR:  21000: ON CONFLICT DO UPDATE command cannot affect row a second time  
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.  
LOCATION:  ExecOnConflictUpdate, nodeModifyTable.c:1259  
  
postgres=# insert into t_conf values (1,'test'), (1,'test1') on conflict(id) do update set info=excluded.info;  
ERROR:  21000: ON CONFLICT DO UPDATE command cannot affect row a second time  
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.  
LOCATION:  ExecOnConflictUpdate, nodeModifyTable.c:1259  

报错代码

src/backend/executor/nodeModifyTable.c

/*  
 * ExecOnConflictUpdate --- execute UPDATE of INSERT ON CONFLICT DO UPDATE  
 *  
 * Try to lock tuple for update as part of speculative insertion.  If  
 * a qual originating from ON CONFLICT DO UPDATE is satisfied, update  
 * (but still lock row, even though it may not satisfy estate's  
 * snapshot).  
 *  
 * Returns true if if we're done (with or without an update), or false if  
 * the caller must retry the INSERT from scratch.  
 */  
static bool  
ExecOnConflictUpdate(ModifyTableState *mtstate,  
                                         ResultRelInfo *resultRelInfo,  
                                         ItemPointer conflictTid,  
                                         TupleTableSlot *planSlot,  
                                         TupleTableSlot *excludedSlot,  
                                         EState *estate,  
                                         bool canSetTag,  
                                         TupleTableSlot **returning)  
{  
.....................  
                case HeapTupleInvisible:  
  
                        /*  
                         * This can occur when a just inserted tuple is updated again in  
                         * the same command. E.g. because multiple rows with the same  
                         * conflicting key values are inserted.  
                         *  
                         * This is somewhat similar to the ExecUpdate()  
                         * HeapTupleSelfUpdated case.  We do not want to proceed because  
                         * it would lead to the same row being updated a second time in  
                         * some unspecified order, and in contrast to plain UPDATEs  
                         * there's no historical behavior to break.  
                         *  
                         * It is the user's responsibility to prevent this situation from  
                         * occurring.  These problems are why SQL-2003 similarly specifies  
                         * that for SQL MERGE, an exception must be raised in the event of  
                         * an attempt to update the same row twice.  
                         */  
                        if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(tuple.t_data)))  
                                ereport(ERROR,  
                                                (errcode(ERRCODE_CARDINALITY_VIOLATION),  
                                                 errmsg("ON CONFLICT DO UPDATE command cannot affect row a second time"),  
                                                 errhint("Ensure that no rows proposed for insertion within the same command have duplicate constrained values.")));  
  
                        /* This shouldn't happen */  
                        elog(ERROR, "attempted to lock invisible tuple");  

PostgreSQL 不处理这种错误，应该让用户自己来保障，不会在同一条SQL中出现多条同一个KEY的TUPLE。

digoal’s 大量PostgreSQL文章入口