MIME-Version: 1.0
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
 <CACJufxFFU92-H7G8tmpxt9oTSfL062OA7n5rPx-YbOAtDUUzGw@mail.gmail.com>
 <04bf425ad1b15a4daefe96c478a5253b@oss.nttdata.com> <CACJufxGnc+=No=Ua6NFT2ADt0vRL=m1QsuCOM=9aKPKWh9_L6Q@mail.gmail.com>
 <20240206191937.72eaf0ccc20cfea37944b422@sraoss.co.jp> <CACJufxFT9j8o5kEC8dPCQqLomWjeJm9V9m8eZjj2Gvc_F5ha=g@mail.gmail.com>
 <76da9fcc-93c5-4053-872e-12932a95356d@uni-muenster.de> <CAKFQuwaYNw8U-9JkFdyOX4i4Y3J1sp6+dk-sh8YmZGCq8gMeVQ@mail.gmail.com>
 <6eac5b45-7f45-4c7a-aae1-e90db8be2e08@uni-muenster.de> <CACJufxFFdtPKk4B5rSVNEk6yCH2Amvi_8w3Gaz5wg9M_t9c5Rw@mail.gmail.com>
 <3d6b5885-16a1-475d-b56f-41701c48d9d4@uni-muenster.de> <CACJufxEgiysa2SMJPGp0aN476Ojm636MfJK88DZC7TVYsXYBBQ@mail.gmail.com>
 <CALdSSPhgjCbyb=ZRgr4LaCFJV2-F9_CxMeX6poHuGCt_f9GYAw@mail.gmail.com>
 <63595e8f-a245-4335-aa22-7e449a70e210@oss.nttdata.com> <CALdSSPi1JE9xc31W6DPAdk-bQHeo3HNAYB-10Biruu-w4GJN0Q@mail.gmail.com>
 <07587c36-18b3-4ccb-b5fb-579bcb04ed37@oss.nttdata.com> <CALdSSPjYw5g7_sc++bRcxOnC7jW6O2qiSkgdKRUYFXZZv3-Ktw@mail.gmail.com>
 <CACJufxGsGqU0BnHw9+Vk3KhBSA3951fZs+JQnM387CiJD3Qn+g@mail.gmail.com>
 <CALdSSPh13qiKmhqwj=bR_3seZJVkP9E6BDbLfHXQ_DbaxQL4FA@mail.gmail.com> <CACJufxEq9aQX6ddkHeEX-RqjTZfDF02BLWGVS28ixRQQxoz0LA@mail.gmail.com>
In-Reply-To: <CACJufxEq9aQX6ddkHeEX-RqjTZfDF02BLWGVS28ixRQQxoz0LA@mail.gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Wed, 20 Nov 2024 01:02:49 +0500
Message-ID: <CALdSSPgLVBHNr97pYgmVfi+YdH4bqwk9YVvbuvhJ4sCr7eEVOA@mail.gmail.com>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
To: jian he <jian.universality@gmail.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>, Jim Jones <jim.jones@uni-muenster.de>, 
	"David G. Johnston" <david.g.johnston@gmail.com>, Yugo NAGATA <nagata@sraoss.co.jp>, 
	torikoshia <torikoshia@oss.nttdata.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CALdSSPgLVBHNr97pYgmVfi%2BYdH4bqwk9YVvbuvhJ4sCr7eEVOA%40mail.gmail.com>
Precedence: bulk

On Tue, 19 Nov 2024, 13:52 jian he, <jian.universality@gmail.com> wrote:
>
> On Sat, Nov 16, 2024 at 5:55=E2=80=AFPM Kirill Reshke <reshkekirill@gmail=
.com> wrote:
> >
> > I am attaching my v8 for reference.
> >
>
> in your v8.
>
>    <varlistentry>
>     <term><literal>REJECT_LIMIT</literal></term>
>     <listitem>
>      <para>
>       Specifies the maximum number of errors tolerated while converting a
>       column's input value to its data type, when <literal>ON_ERROR</lite=
ral> is
>       set to <literal>ignore</literal>.
>       If the input contains more erroneous rows than the specified
> value, the <command>COPY</command>
>       command fails, even with <literal>ON_ERROR</literal> set to
> <literal>ignore</literal>.
>      </para>
>     </listitem>
>    </varlistentry>
>
> then above description not meet with following example, (i think)
>
> create table t(a int not null);
> COPY t FROM STDIN WITH (on_error set_to_null, reject_limit 2);
> 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.
> >> a
> >> \.
> ERROR:  null value in column "a" of relation "t" violates not-null constr=
aint
> DETAIL:  Failing row contains (null).
> CONTEXT:  COPY t, line 1, column a: "a"


Sure, my v8 does not helps with column level NOT NULL constraint (or
other constraint)

> Overall, I think
> making the domain not-null align with column level not-null would be a
> good thing.

While this looks sane, it's actually a separate topic. Even on current
HEAD we have domain not-null vs column level not-null unalignment.

consider this example


```
reshke=3D# create table ftt2 (i int not null);
CREATE TABLE
reshke=3D# copy ftt2 from stdin with (reject_limit 1000, on_error ignore);
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.
>> \N
>> \.
ERROR:  null value in column "i" of relation "ftt2" violates not-null const=
raint
DETAIL:  Failing row contains (null).
CONTEXT:  COPY ftt2, line 1: "\N"
reshke=3D# create domain dd as int not null ;
CREATE DOMAIN
reshke=3D# create table ftt3(i dd);
CREATE TABLE
reshke=3D# copy ftt3 from stdin with (reject_limit 1000, on_error ignore);
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.
>> \N
>> \.
NOTICE:  1 row was skipped due to data type incompatibility
COPY 0
reshke=3D#
```
So, if we want this, we need to start another thread and deal with
REJECT_LIMIT + on_error ignore first.

The ExecConstraints function is the source of the error in scenario 1.
Therefore, we require something like "ExecConstraintsSafe" to
accommodate the aforementioned. That is a significant change. Not sure
it will be accepted by the community.

>
>      <para>
>       Specifies how to behave when encountering an error converting a col=
umn's
>       input value into its data type.
>       An <replaceable class=3D"parameter">error_action</replaceable> valu=
e of
>       <literal>stop</literal> means fail the command,
>       <literal>ignore</literal> means discard the input row and
> continue with the next one, and
>       <literal>set_to_null</literal> means replace columns containing
> erroneous input values with <literal>null</literal> and move to the
> next row.
>
> "and move to the next row" is wrong?
> I think it should be " and move to the next field".

Yes, "and move to the next field" is correct.