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

On 12.12.2023 16:04, jian he wrote:
> On Mon, Dec 11, 2023 at 10:05 PM Alena Rybakina
> <lena.ribackina@yandex.ru>  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