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