PostgreSQL merge insert(insert into on conflict) ERRCODE_CARDINALITY_VIOLATION （Ensure that no rows proposed for insertion within the same command hav

标簽

PostgreSQL , insert into on conflict , merge insert

https://github.com/digoal/blog/blob/master/201807/20180718_01.md#%E8%83%8C%E6%99%AF 背景

使用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。