Re: [HACKERS] How to implement the skip errors for copy from ?
Use subtraction is very inefficient, a project called pg_bulkload support the?skip errors ,and it does not useing subtraction. It?performance is very good.??So I want to imitate?pg_bulkload?to?implementation?skip errors of copy.if i do the following thing to copy :1. disable all of trigger of table;2. only skip the following errors:? ? ?* format error of tuple;? ? ?* check constraint?violation;? ? ?* unique or primary key?constraint?violation;? ? other errors will be abort current transcation except above three errors.3. ?bad tuple will be deleted and the?resource owner per tuples is reseased?? ? when?a unique key?violation, ?xmax of the tuple will?be marked to current? ? transaction id as it?not be seen as good, and all of index?entries?of the bad? ? tuple will?be real delete at next VACUUM.Is it right to?skip errors for copy from? From:?Tom LaneDate:?2014-06-17?21:40To:?xbzhangCC:?Amit Kapila; Pavel Stehule; Alvaro Herrera; pgsql-hackersSubject:?Re: [HACKERS] How to implement the skip errors for copy from ?xbzhang 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 ?
Re: [HACKERS] How to implement the skip errors for copy from ?
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? 张晓博 研发二部 北京人大金仓信息技术股份有限公司 地址：北京市海淀区上地西路八号院上地科技大厦4号楼501 邮编：100085 电话：(010) 5885 1118 - 8450 手机：15311394463 邮箱：xbzh...@kingbase.com.cn From: Amit KapilaDate: 2014-06-17 17:10To: xbzhangCC: Pavel Stehule; Alvaro Herrera; pgsql-hackersSubject: Re: [HACKERS] How to implement the skip errors for copy from ?On Tue, Jun 17, 2014 at 12:16 PM, xbzhang 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 takenbefore error and you won't know which one to free. Another is that postgres uses memory context to allocate/free memoryin most places, so there can be allocated memory which needsto 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
Re: [HACKERS] 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 : 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 邮箱：xbzh...@kingbase.com.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 : > > > > > 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
Re: [HACKERS] 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 tupleIs it feasible? Anybody give me some suggestion? 张晓博 研发二部 北京人大金仓信息技术股份有限公司 地址：北京市海淀区上地西路八号院上地科技大厦4号楼501 邮编：100085 电话：(010) 5885 1118 - 8450 手机：15311394463 邮箱：xbzh...@kingbase.com.cn From: Alvaro HerreraDate: 2014-06-17 02:37To: Pavel StehuleCC: 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 : > > > > > 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
[HACKERS] How to implement the skip errors for copy from ?
I want to?implement the skip errors for copy from,lik as :create table A (c int primary key);copy A from stdin;112\. 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 resultwill be that there are two rows in table A: 1 and 2. how to?implement that ? Anybody give me some?suggestion? 张晓博?? 研发二部 北京人大金仓信息技术股份有限公司 地址：北京市海淀区上地西路八号院上地科技大厦4号楼501 邮编：100085 电话：(010) 5885 1118 - 8450 手机：15311394463 邮箱：xbzh...@kingbase.com.cn
