RE: Conflict handling for COPY FROM - Mailing list pgsql-hackers

Hello Surafel,

From: Surafel Temesgen <surafel3000@gmail.com> 
>>On Fri, Mar 6, 2020 at 11:30 AM mailto:asaba.takanori@fujitsu.com <mailto:asaba.takanori@fujitsu.com> wrote:
>>I think we need regression test that constraint violating row is returned back to the caller.
>>How about this?
>
>okay attached is a rebased patch with it 
Thank you very much.
Although it is a small point, it may be better like this:
+70005  27      36      46      56  ->  70005  27      37      47      57

I want to discuss about copy from binary file.
It seems that this patch tries to avoid the error that number of field is different .

+               {
+                       if (cstate->error_limit > 0 || cstate->ignore_all_error)
+                       {
+                               ereport(WARNING,
+                                               (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+                                                errmsg("skipping \"%s\" --- row field count is %d, expected %d",
+                                                               cstate->line_buf.data, (int) fld_count, attr_count)));
+                               cstate->error_limit--;
+                               goto next_line;
+                       }
+                       else
+                               ereport(ERROR,
+                                               (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+                                                errmsg("row field count is %d, expected %d",
+                                                               (int) fld_count, attr_count)));
+
+               }

I checked like this:

postgres=# CREATE TABLE x (
postgres(# a serial UNIQUE,
postgres(# b int,
postgres(# c text not null default 'stuff',
postgres(# d text,
postgres(# e text
postgres(# );
CREATE TABLE
postgres=# COPY x from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 70004        25      35      45      55
>> 70005        26      36      46      56
>> \.
COPY 2
postgres=# SELECT * FROM x;
   a   | b  | c  | d  | e
-------+----+----+----+----
 70004 | 25 | 35 | 45 | 55
 70005 | 26 | 36 | 46 | 56
(2 rows)

postgres=# COPY x TO '/tmp/copyout' (FORMAT binary);
COPY 2
postgres=# CREATE TABLE y (
postgres(# a serial UNIQUE,
postgres(# b int,
postgres(# c text not null default 'stuff',
postgres(# d text
postgres(# );
CREATE TABLE
postgres=# COPY y FROM '/tmp/copyout' WITH (FORMAT binary,ERROR_LIMIT -1);
2020-03-12 16:55:55.457 JST [2319] WARNING:  skipping "" --- row field count is 5, expected 4
2020-03-12 16:55:55.457 JST [2319] CONTEXT:  COPY y, line 1
2020-03-12 16:55:55.457 JST [2319] WARNING:  skipping "" --- row field count is 0, expected 4
2020-03-12 16:55:55.457 JST [2319] CONTEXT:  COPY y, line 2
2020-03-12 16:55:55.457 JST [2319] ERROR:  unexpected EOF in COPY data
2020-03-12 16:55:55.457 JST [2319] CONTEXT:  COPY y, line 3, column a
2020-03-12 16:55:55.457 JST [2319] STATEMENT:  COPY y FROM '/tmp/copyout' WITH (FORMAT binary,ERROR_LIMIT -1);
WARNING:  skipping "" --- row field count is 5, expected 4
WARNING:  skipping "" --- row field count is 0, expected 4
ERROR:  unexpected EOF in COPY data
CONTEXT:  COPY y, line 3, column a

It seems that the error isn't handled.
'WARNING:  skipping "" --- row field count is 5, expected 4' is correct, 
but ' WARNING:  skipping "" --- row field count is 0, expected 4' is not correct.

Also, is it needed to skip the error that happens when input is binary file?
Is the case that each row has different number of field and only specific rows are copied occurred?


Regards,

--
Takanori Asaba