Re: Add new error_action COPY ON_ERROR "log"

From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, torikoshia <torikoshia(at)oss(dot)nttdata(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, jian(dot)universality(at)gmail(dot)com, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <aekorotkov(at)gmail(dot)com>
Subject: Re: Add new error_action COPY ON_ERROR "log"
Date: 2024-03-25 11:21:39
Message-ID: CALj2ACVhub6gkOux+VAkMya6XzsauUBR-BrRGrNH9U9Wot5pCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Mar 25, 2024 at 10:42 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)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

Attachment Content-Type Size
v9-0001-Add-LOG_VERBOSITY-option-to-COPY-command.patch application/x-patch 9.5 KB
v9-0002-Add-detailed-info-when-COPY-skips-soft-errors.patch application/x-patch 8.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2024-03-25 11:33:59 Re: Introduce XID age and inactive timeout based replication slot invalidation
Previous Message Amit Kapila 2024-03-25 11:11:27 pgsql: Track last_inactive_time in pg_replication_slots.