开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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


背景

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Unsafe query: ‘Update‘ statement without ‘where‘ updates all table rows at once
Unsafe query: ‘Update‘ statement without ‘where‘ updates all table rows at once
0 0
Database specific hint in One order search
Database specific hint in One order search
0 0
Syntax error, insert "Dimensions" to complete TypeArgument
版权声明:本文为 testcs_dn(微wx笑) 原创文章,非商用自由转载-保持署名-注明出处,谢谢。 https://blog.csdn.net/testcs_dn/article/details/78248391 ...
881 0
JPA异常:Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
JPA异常:Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
0 0
1120 11g select for update skip locked
[20171120]11g select for update skip locked.txt --//11G在select for update遇到阻塞时可以通过skipped locked跳过阻塞的记录,测试看看: 1.
830 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
Dynamic DDL Adding Structure t
立即下载
Dynamic DDL Adding Structure to Streaming Data on the Fly
立即下载
Lambda Processing for Near Time Search Indexing
立即下载