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

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

> 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?
>

there can be raised any exception -- any non fatal exception. I remember,
when I wrote some similar without exception, then it was very unstable.

Pavel

>
>
> *From:* Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> *Date:* 2014-06-17 14:01
> *To:* xbzhang <xbzhang(at)kingbase(dot)com(dot)cn>
> *CC:* Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>; pgsql-hackers
> <pgsql-hackers(at)postgresql(dot)org>
> *Subject:* 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 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
>>
>>
>>
> 在此邮件中未发现病毒。
> 检查工具：AVG - www.avg.com
> 版本：2013.0.3480 / 病毒数据库：3955/7689 - 发布日期：06/16/14
>
>