Re: Conflict handling for COPY FROM

Hi Takanori Asaba,

>
>
> Although it is a small point, it may be better like this:
> +70005 27 36 46 56 -> 70005 27 37 47 57
>
>
done

> 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.
>
>
Thank you for the detailed example

> 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?
>
>
An error that can be surly handled without transaction rollback can
be included in error handling but i will like to proceed without binary file
errors handling for the time being

regards
Surafel