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 tuple

Is it feasible?  Anybody give me some suggestion?

---

张晓博   研发二部

北京人大金仓信息技术股份有限公司

地址：北京市海淀区上地西路八号院上地科技大厦4号楼501

邮编：100085

电话：(010) 5885 1118 - 8450

手机：15311394463

邮箱：xbzhang@kingbase.com.cn

 

From: Alvaro Herrera

Date: 2014-06-17 02:37

To: Pavel Stehule

CC: xbzhang; pgsql-hackers

Subject: Re: [HACKERS] How to implement the skip errors for copy from ?

Pavel Stehule wrote:

> 2014-06-16 11:46 GMT+02:00 xbzhang <xbzhang@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