Use subtransaction , the tuples that had inserted into heap must be inserted again when some exception is raised,it is too expensive.My solution is :1. delete the tuple that caused the error tuple;2. release all the resources when inserting the tuple;3. continue insert next tupleIs it feasible? Anybody give me some suggestion?
张晓博 研发二部 北京人大金仓信息技术股份有限公司 地址：北京市海淀区上地西路八号院上地科技大厦4号楼501 邮编：100085 电话：(010) 5885 1118 - 8450 手机：15311394463 邮箱：xbzh...@kingbase.com.cn From: Alvaro HerreraDate: 2014-06-17 02:37To: Pavel StehuleCC: xbzhang; pgsql-hackersSubject: Re: [HACKERS] How to implement the skip errors for copy from ?Pavel Stehule wrote: > 2014-06-16 11:46 GMT+02:00 xbzhang <xbzh...@kingbase.com.cn>: > > > > > I want to implement the skip errors for copy from,lik as : > > create table A (c int primary key); > > copy A from stdin; > > 1 > > 1 > > 2 > > \. > > > > copy will failed: > > ERROR: duplicate key violates primary key constraint "CC_PKEY" > > CONTEXT: COPY CC, line 2: "1" > > > > I want skip the error, and continue to copy the reset of tuple. The result > > will be that there are two rows in table A: 1 and 2. > > > > how to implement that ? Anybody give me some suggestion? > > you should to reimplement a copy procedure to use a subtransactions. Using > subtransaction for any row is too expensive, but you can do subtransaction > per 1000 rows, and when some exception is raised, then store data per one > row/one subtransaction. See http://pgloader.io/ for a ready-made solution. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services ----- ??????????? ????:AVG - www.avg.com ??:2013.0.3480 / ?????:3955/7685 - ????:06/16/14
