Message-ID: <07587c36-18b3-4ccb-b5fb-579bcb04ed37@oss.nttdata.com>
Date: Fri, 8 Nov 2024 03:00:54 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: jian he <jian.universality@gmail.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>
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>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <CALdSSPi1JE9xc31W6DPAdk-bQHeo3HNAYB-10Biruu-w4GJN0Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/07587c36-18b3-4ccb-b5fb-579bcb04ed37%40oss.nttdata.com>
Precedence: bulk


On 2024/10/26 6:03, Kirill Reshke wrote:
> when the REJECT LIMIT is set to some non-zero number and the number of
> row NULL replacements exceeds the limit, is it OK to fail. Because
> there WAS errors, and we should not tolerate more than $limit errors .
> I do find this behavior to be consistent.

+1


> But what if we don't set a REJECT LIMIT, it is sane to do all
> replacements, as if REJECT LIMIT is inf.

+1


> But our REJECT LIMIT is zero
> (not set).
> So, we ignore zero REJECT LIMIT if set_to_null is set.

REJECT_LIMIT currently has to be greater than zero, so it won’t ever be zero.


> But while I was trying to implement that, I realized that I don't
> understand v4 of this patch. My misunderstanding is about
> `t_on_error_null` tests. We are allowed to insert a NULL value for the
> first column of t_on_error_null using COPY ON_ERROR SET_TO_NULL. Why
> do we do that? My thought is we should try to execute
> InputFunctionCallSafe with NULL value  (i mean, here [1]) for the
> column after we failed to insert the input value. And, if this second
> call is successful, we do replacement, otherwise we count the row as
> erroneous.

Your concern is valid. Allowing NULL to be stored in a column with a NOT NULL
constraint via COPY ON_ERROR=SET_TO_NULL does seem unexpected. As you suggested,
NULL values set by SET_TO_NULL should probably be re-evaluated.


> Hm, good catch. Applied almost as you suggested. I did tweak this
> "replace columns with invalid input values with " into "replace
> columns containing erroneous input values with". Is that OK?

Yes, sounds good.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION