Re: Add new error_action COPY ON_ERROR "log"

On Mon, Mar 25, 2024 at 10:42 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> The current approach, eliminating the duplicated information in
> CONTEXT, seems good to me.

Thanks for looking into it.

> One question about the latest (v8) patch:
>
> +                   else
> +                       ereport(NOTICE,
> +                               errmsg("data type incompatibility at
> line %llu for column %s: null input",
> +                                      (unsigned long long) cstate->cur_lineno,
> +                                      cstate->cur_attname));
> +
>
> How can we reach this path? It seems we don't cover this path by the tests.

Tests don't cover that part, but it can be hit with something like
[1]. I've added a test for this.

Note the use of domain to provide an indirect way of providing null
constraint check. Otherwise, COPY FROM fails early in
CopyFrom->ExecConstraints if the NOT NULL constraint is directly
provided next to the column in the table [2].

Please see the attached v9 patch set.

[1]
create domain dcheck_ign_err2    varchar(15) NOT NULL;
CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
1    {1}    1    'foo'
2    {2}    2    \N
\.

[2]
CREATE TABLE check_ign_err3 (n int, m int[], k int, l varchar(15) NOT NULL);
postgres=# COPY check_ign_err3 FROM STDIN WITH (on_error ignore,
log_verbosity verbose);
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.
>> 1    {1}     1       'foo'
2       {2}     2       \N>>
>> \.
ERROR:  null value in column "l" of relation "check_ign_err3" violates
not-null constraint
DETAIL:  Failing row contains (2, {2}, 2, null).
CONTEXT:  COPY check_ign_err3, line 2: "2       {2}     2       \N"

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com