Re: 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 tuple
> Is 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 Herrera <alvherre(at)2ndquadrant(dot)com>
> *Date:* 2014-06-17 02:37
> *To:* Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> *CC:* xbzhang <xbzhang(at)kingbase(dot)com(dot)cn>; pgsql-hackers
> <pgsql-hackers(at)postgresql(dot)org>
> *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(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
>
>
>