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

 

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

 

 

From: Amit Kapila

Date: 2014-06-17 17:10

To: xbzhang

CC: Pavel Stehule; Alvaro Herrera; pgsql-hackers

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

On Tue, Jun 17, 2014 at 12:16 PM, xbzhang <xbzhang@kingbase.com.cn> wrote:
>
> 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 might be some LWlocks which might have been taken

before error and you won't know which one to free.  Another

is that postgres uses memory context to allocate/free memory

in most places, so there can be allocated memory which needs

to be released, transaction/sub-transaction abort takes care of all

such and many more similar things.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

在此邮件中未发现病毒。
检查工具：AVG - www.avg.com
版本：2013.0.3480 / 病毒数据库：3955/7689 - 发布日期：06/16/14

xbzhang <xbzhang@kingbase.com.cn> writes:
> LWlocks can record in resource owner per tuples, so they can be released at rigth way, but the memory allocated on
memorycontext is one problem.Are there any others problems?
 

See AbortSubTransaction(), CleanupSubTransaction(), and the rather large
number of subroutines they call.  Almost everything that code does is
connected to cleaning up something that might have been left unfinished
after an elog(ERROR) took control away in the middle of some code
sequence.

In addition, you can't just wave your hands and presto the bad tuple is
not there anymore.  For example, the failure might have been a unique key
violation in some index or other.  Not only is the bad tuple already on
disk, but possibly so are index entries for it in other indexes.  In
general the only way to get rid of those index entries is a VACUUM.
So you really have to have a subtransaction whose XID is what you mark
the new tuple with, and then rolling back the subtransaction is what
causes the new tuple to not be seen as good.  (Actually getting rid of
it will be left for the next VACUUM.)
        regards, tom lane

From: Tom Lane

Date: 2014-06-17 21:40

To: xbzhang

CC: Amit Kapila; Pavel Stehule; Alvaro Herrera; pgsql-hackers

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

xbzhang <xbzhang@kingbase.com.cn> writes:

> LWlocks can record in resource owner per tuples, so they can be released��at rigth way, but the memory allocated on memory context��is one problem.Are there any others problems?

 

See AbortSubTransaction(), CleanupSubTransaction(), and the rather large

number of subroutines they call.  Almost everything that code does is

connected to cleaning up something that might have been left unfinished

after an elog(ERROR) took control away in the middle of some code

sequence.

 

In addition, you can't just wave your hands and presto the bad tuple is

not there anymore.  For example, the failure might have been a unique key

violation in some index or other.  Not only is the bad tuple already on

disk, but possibly so are index entries for it in other indexes.  In

general the only way to get rid of those index entries is a VACUUM.

So you really have to have a subtransaction whose XID is what you mark

the new tuple with, and then rolling back the subtransaction is what

causes the new tuple to not be seen as good.  (Actually getting rid of

it will be left for the next VACUUM.)

 

regards, tom lane

 

 

 

 

-----

???????????

????:AVG - www.avg.com

??:2013.0.3480 / ?????:3955/7691 - ????:06/17/14