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

one resource owner per tuples, when error happens, only release resource owner belong to error tuple.Why some memory structures should be in undefined state? Can you give some examples?

 From: Pavel StehuleDate: 2014-06-17 14:01To: xbzhangCC: Alvaro Herrera; pgsql-hackersSubject: Re: Re: [HACKERS] How to implement the skip errors for copy from ?

2014-06-17 7:30 GMT+02:00 xbzhang <xbzhang(at)kingbase(dot)com(dot)cn>:

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?

no, it should not work - after any exception some memory structures should be in undefined state. Errors in PostgreSQL are destructive and any error must be followed by ROLLBACK.

Subtransaction for any row is expensive, but subtransaction for some block is cheap

Regards

Pavel
 

张晓博   研发二部
北京人大金仓信息技术股份有限公司
地址：北京市海淀区上地西路八号院上地科技大厦4号楼501

邮编：100085
电话：(010) 5885 1118 - 8450
手机：15311394463
邮箱：xbzhang(at)kingbase(dot)com(dot)cn

From: Alvaro HerreraDate: 2014-06-17 02:37To: Pavel Stehule

CC: 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
 

在此邮件中未发现病毒。

检查工具：AVG - www.avg.com

版本：2013.0.3480 / 病毒数据库：3955/7689 - 发布日期：06/16/14