Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Alena Rybakina <[email protected]>
To: jian he <[email protected]>
Cc: Damir Belyalov <[email protected]>
Cc: [email protected]
Cc: Tom Lane <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: torikoshia <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: Andrei Lepikhov <[email protected]>
Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)
Date: Thu, 14 Dec 2023 17:48:41 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxEFXzxjD9oOq3LoVQAy0KH0TJsDS3UnTtecxx-4J0+2NA@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CALH1Lgtg39crESw644KDH+ejBWuW3Vospe67F-ZWXNeBMEE=PA@mail.gmail.com>
	<[email protected]!>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CALH1LgueaSXWJ=eZ-0-LYCm8tF45SoOM0QFc55C-xpbGfNkFyg@mail.gmail.com>
	<[email protected]>
	<CACJufxGBb-WE7R1fM4kJxvmsgsyKL_4vce3s_KxLbo_ZEnFnWw@mail.gmail.com>
	<[email protected]>
	<CACJufxH3KGOGhnS-J2bYnA-mpABg4HG9Xddfj9zBBgoK+iGSsQ@mail.gmail.com>
	<[email protected]>
	<CACJufxHjfcetqip_3RLyvMQSB-SkvxwXkCP3VUN8QsKMKfjoUA@mail.gmail.com>
	<[email protected]>
	<CACJufxEFXzxjD9oOq3LoVQAy0KH0TJsDS3UnTtecxx-4J0+2NA@mail.gmail.com>

On 12.12.2023 16:04, jian he wrote:
> On Mon, Dec 11, 2023 at 10:05 PM Alena Rybakina
> <[email protected]>  wrote:
>> Hi! Thank you for your work. Your patch looks better!
>> Yes, thank you! It works fine, and I see that the regression tests have been passed. 🙂
>> However, when I ran 'copy from with save_error' operation with simple csv files (copy_test.csv, copy_test1.csv) for tables test, test1 (how I created it, I described below):
>>
>> postgres=# create table test (x int primary key, y int not null);
>> postgres=# create table test1 (x int, z int, CONSTRAINT fk_x
>>        FOREIGN KEY(x)
>>            REFERENCES test(x));
>>
>> I did not find a table with saved errors after operation, although I received a log about it:
>>
>> postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV save_error
>> NOTICE:  2 rows were skipped because of error. skipped row saved to table public.test_error
>> ERROR:  duplicate key value violates unique constraint "test_pkey"
>> DETAIL:  Key (x)=(2) already exists.
>> CONTEXT:  COPY test, line 3
>>
>> postgres=# select * from public.test_error;
>> ERROR:  relation "public.test_error" does not exist
>> LINE 1: select * from public.test_error;
>>
>> postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' CSV save_error
>> NOTICE:  2 rows were skipped because of error. skipped row saved to table public.test1_error
>> ERROR:  insert or update on table "test1" violates foreign key constraint "fk_x"
>> DETAIL:  Key (x)=(2) is not present in table "test".
>>
>> postgres=# select * from public.test1_error;
>> ERROR:  relation "public.test1_error" does not exist
>> LINE 1: select * from public.test1_error;
>>
>> Two lines were written correctly in the csv files, therefore they should have been added to the tables, but they were not added to the tables test and test1.
>>
>> If I leave only the correct rows, everything works fine and the rows are added to the tables.
>>
>> in copy_test.csv:
>>
>> 2,0
>>
>> 1,1
>>
>> in copy_test1.csv:
>>
>> 2,0
>>
>> 2,1
>>
>> 1,1
>>
>> postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV
>> COPY 2
>> postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' CSV save_error
>> NOTICE:  No error happened.Error holding table public.test1_error will be droped
>> COPY 3
>>
>> Maybe I'm launching it the wrong way. If so, let me know about it.
> looks like the above is about constraints violation while copying.
> constraints violation while copying not in the scope of this patch.
>
> Since COPY FROM is very like the INSERT command,
> you do want all the valid constraints to check all the copied rows?
No, I think it will be too much.
> but the notice raised by the patch is not right.
> So I place the drop error saving table or raise notice logic above
> `ExecResetTupleTable(estate->es_tupleTable, false)` in the function
> CopyFrom.
Yes, I see it and agree with you.
>> I also notice interesting behavior if the table was previously created by the user. When I was creating an error_table before the 'copy from' operation,
>> I received a message saying that it is impossible to create a table with the same name (it is shown below) during the 'copy from' operation.
>> I think you should add information about this in the documentation, since this seems to be normal behavior to me.
>>
> doc changed. you may check it.
Yes, I saw it. Thank you.

-- 
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

view thread (75+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox