Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 21 Jan 2026 15:47:51 -0300
Message-Id: <DFUHPCSP86G0.11VU7FGWRRXM2@gmail.com>
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "jian he" <jian.universality@gmail.com>
Cc: "torikoshia" <torikoshia@oss.nttdata.com>, "Masahiko Sawada"
 <sawada.mshk@gmail.com>, "vignesh C" <vignesh21@gmail.com>, "Jim Jones"
 <jim.jones@uni-muenster.de>, "Kirill Reshke" <reshkekirill@gmail.com>,
 "Fujii Masao" <masao.fujii@oss.nttdata.com>, "David G. Johnston"
 <david.g.johnston@gmail.com>, "Yugo NAGATA" <nagata@sraoss.co.jp>,
 "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com> <CACJufxHj0PejJC8BMq1cCiFC5uFdnazqrHh_VEm=s65-cNSnwQ@mail.gmail.com> <CALDaNm0Q3hA9w76gvB=goOY4ktKb-wjrbZbOcrqSwvxdm0yrcw@mail.gmail.com> <CACJufxEueK_MFebYqp73e+P+ykbD4+QbONptMg=iaaxUEGU7EQ@mail.gmail.com> <CALDaNm0FUtPjH9wz5dgwOBNtsAXEZj=0-TYGsyQXchQy-hXGrw@mail.gmail.com> <CACJufxFpm2Gzx4AD9qKtiQSiiiunX02wNTxu0JoFm7nEKF2KUw@mail.gmail.com> <CAD21AoBjmEYjZT9A6P-vAuwboiiYtXaMc12pX2ySmh3RXi=v8w@mail.gmail.com> <CACJufxF6_YwAboiCaVYLRtNpO4kGbXqkXzH_7W=pUvrNXK8WuQ@mail.gmail.com> <CAD21AoBWwEzpp3Z6tp-O-AQGftK-kuj6vRva2L5daWoWBtbnRg@mail.gmail.com> <CACJufxGEHmijmP-QqvrmqU6cxmhgpdjY7ewQBQ=E9NmdyEcqmw@mail.gmail.com> <f5ebfa4b318e97c41c0919304b94c77f@oss.nttdata.com> <CACJufxG=em0PHZvy1EAZ+vxPZ8UA68MfQ-Hji+h+WgnWNpqmVQ@mail.gmail.com> <CACJufxF0c3k5O8up9NOY-m02nyJ0f6N1tKxZwjCewTqvvFmbLw@mail.gmail.com> <CACJufxHr-LBV2pB9m64mA=1pgVMM4LvUn+-MzpeViWV=Ks_cyg@mail.gmail.com>
 <901967e5-e5dc-42c6-b2bf-fb3a49d7e787@gmail.com>
 <CACJufxEs_VBV39gYHnpLFOMcUaUD-ADAst_ePTCgmoDR8O=ekg@mail.gmail.com>
In-Reply-To: <CACJufxEs_VBV39gYHnpLFOMcUaUD-ADAst_ePTCgmoDR8O=ekg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/DFUHPCSP86G0.11VU7FGWRRXM2%40gmail.com>
Precedence: bulk

On Wed Jan 21, 2026 at 4:37 AM -03, jian he wrote:
> On Wed, Jan 21, 2026 at 3:55=E2=80=AFAM Matheus Alcantara
> <matheusssilv97@gmail.com> wrote:
>> Hi,
>>
>> The patch needs a new rebase, could you please send a new version?
>
> sure. please check the attached.

Thanks for the new version. I have some comments on this first round of
review:

+ errmsg_plural("invalid values in %" PRIu64 " row was replaced with null d=
ue to data type incompatibility",
+   "invalid values in %" PRIu64 " rows were replaced with null due to data=
 type incompatibility",

I think that we could remove the "invalid values in" to make it
consistency with the COPY_ON_ERROR_IGNORE NOTICE

----------

+		cstate->domain_with_constraint =3D (bool *) palloc0(attr_count * sizeof(=
bool));

I think that we can use palloc_array?

----------

Should FORCE_NOT_NULL be allowed to be used with ON_ERROR set_null? It
seems to me that ON_ERROR set_null overwrite the FORCE_NOT_NULL
behaviour:

postgres=3D# create table t4(a int, b varchar(5));
CREATE TABLE

postgres=3D# copy t4 from 'data.csv' with (FORCE_NOT_NULL(b), format csv, d=
elimiter ',', NULL 'NULL', ON_ERROR set_null);
NOTICE:  invalid values in 2 rows were replaced with null due to data type =
incompatibility
COPY 5

postgres=3D# \pset null 'NULL'
Null display is "NULL".
postgres=3D# select * from t4;
 a |  b
---+------
 1 | aaaa
 2 | bbbb
 2 | NULL
 2 | NULL
 5 | NULL
(5 rows)

Note that only the ccccc rows on .csv file was inserted with a NULL
value on b column. The 5,NULL row was inserted with a "NULL" string as a
value:

postgres=3D# select * from t4 where b is null;
 a |  b
---+------
 2 | NULL
 2 | NULL
(2 rows)

The contents of data.csv:
    1,aaaa
    2,bbbb
    2,ccccc
    2,ccccc
    5,NULL

Perhaps we should block the usage of FORCE_NOT_NULL with ON_ERROR
SET_NULL?

----------

On monitoring.sgml we have the following for pg_stat_progress_copy
tuples_skipped:
       Number of tuples skipped because they contain malformed data.
       This counter only advances when a value other than
       <literal>stop</literal> is specified to the <literal>ON_ERROR</liter=
al>

IIUC we are not updating this view if we set a column to NULL due to an
error, perhaps this documentation should be updated to mention that it
will not be updated with ON_ERROR set_null?

----------

I may have missing something, but we are still considering implementing
the REJECT_LIMIT + ON_ERROR set_null?

--
Matheus Alcantara
EDB: https://www.enterprisedb.com