Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDn1B-0039FG-A6 for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Dec 2023 14:48:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rDn19-0093C7-MK for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Dec 2023 14:48:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDn19-0093AY-63 for pgsql-hackers@lists.postgresql.org; Thu, 14 Dec 2023 14:48:51 +0000 Received: from forward500c.mail.yandex.net ([178.154.239.208]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDn15-00BymA-MK for pgsql-hackers@postgresql.org; Thu, 14 Dec 2023 14:48:50 +0000 Received: from mail-nwsmtp-smtp-production-main-92.myt.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-92.myt.yp-c.yandex.net [IPv6:2a02:6b8:c12:2a1b:0:640:ba0b:0]) by forward500c.mail.yandex.net (Yandex) with ESMTP id DA8B761397; Thu, 14 Dec 2023 17:48:45 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-92.myt.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id fmlgr31Oqa60-FtorNj6M; Thu, 14 Dec 2023 17:48:45 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1702565325; bh=eBgGAjekwD4KkGM36Ly1866weVnayaIXNISb7g0iaKE=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=mHjDEuGI/ekiftBDMHwQT3jE6oUGtht/BcFPkn9+YM7kwCKVMCGVgpxRCYicf114R vQH4lldlB3I+S0IenJ4L0hKg5ZkNtZQAZOhfcxAWHFSldLHMSxnbTEC9luZ4lRmEGc kgip8w5qNIV0B+27JmVfXR/ZE2OPywUyliaqsb7s= Authentication-Results: mail-nwsmtp-smtp-production-main-92.myt.yp-c.yandex.net; dkim=pass header.i=@yandex.ru Content-Type: multipart/alternative; boundary="------------FlQ176IKEs3OqEalOTgHbimm" Message-ID: <5b690f4b-1ffd-4672-80c3-09e7bbce449a@yandex.ru> Date: Thu, 14 Dec 2023 17:48:41 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features) To: jian he Cc: Damir Belyalov , zhihuifan1213@163.com, Tom Lane , Daniel Gustafsson , torikoshia , PostgreSQL Hackers , Andres Freund , anisimow.d@gmail.com, HukuToc@gmail.com, pgsql-hackers@lists.postgresql.org, Andrei Lepikhov References: <483E1B59-4A80-450B-8685-7CC12762FD5A@anarazel.de> <8de9d5e1-2f81-ee2e-ece4-b1a6ede1f285@yandex.ru> <58fa3b63e88ffd0a09273e38aec0661e@oss.nttdat!> <752672.1699474336@sss.pgh.pa.us> <878r77mtlt.fsf@163.com> <760ee97a-e336-4d7b-9477-9d9e99370097@postgrespro.ru> <23a89ba0-b5d5-419c-b798-cd2c59490148@yandex.ru> Content-Language: en-US From: Alena Rybakina In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------FlQ176IKEs3OqEalOTgHbimm Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 12.12.2023 16:04, jian he wrote: > On Mon, Dec 11, 2023 at 10:05 PM Alena Rybakina > 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 --------------FlQ176IKEs3OqEalOTgHbimm Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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
--------------FlQ176IKEs3OqEalOTgHbimm--