RE: Conflict handling for COPY FROM

Hello Surafel,

From: Surafel Temesgen <surafel3000(at)gmail(dot)com>
>>On Fri, Mar 6, 2020 at 11:30 AM mailto:asaba(dot)takanori(at)fujitsu(dot)com <mailto:asaba(dot)takanori(at)fujitsu(dot)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