Re: How to implement the skip errors for copy from ?

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
邮箱：xbzhang(at)kingbase(dot)com(dot)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 <xbzhang(at)kingbase(dot)com(dot)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