Re: Add new COPY option REJECT_LIMIT

On 2024-07-03 02:07, Fujii Masao wrote:
Thanks for your comments!

> On 2024/01/26 18:49, torikoshia wrote:
>> Hi,
>> 
>> 9e2d870 enabled the COPY command to skip soft error, and I think we 
>> can add another option which specifies the maximum tolerable number of 
>> soft errors.
>> 
>> I remember this was discussed in [1], and feel it would be useful when 
>> loading 'dirty' data but there is a limit to how dirty it can be.
>> 
>> Attached a patch for this.
>> 
>> What do you think?
> 
> The patch no longer applies cleanly to HEAD. Could you update it?

I'm going to update it after discussing the option format as described 
below.

> 
> I think the REJECT_LIMIT feature is useful. Allowing it to be set as
> either the absolute number of skipped rows or a percentage of the
> total input rows is a good idea.
> 
> However, if we support REJECT_LIMIT, I'm not sure if the ON_ERROR
> option is still necessary. REJECT_LIMIT seems to cover the same cases.
> For instance, REJECT_LIMIT=infinity can act like ON_ERROR=ignore, and
> REJECT_LIMIT=0 can act like ON_ERROR=stop.

I agree that it's possible to use only REJECT_LIMIT without ON_ERROR.
I also think it's easy to understand that REJECT_LIMIT=0 is 
ON_ERROR=stop.
However, expressing REJECT_LIMIT='infinity' needs some definition like 
"setting REJECT_LIMIT to -1 means 'infinity'", doesn't it? If so, I 
think this might not so intuitive.

Also, since it seems Snowflake and Redshift have both options equivalent 
to REJECT_LIMIT and ON_ERROR, having both of them in PostgreSQL COPY 
might not be surprising:
- Snowflake's ON_ERROR accepts "CONTINUE | SKIP_FILE | SKIP_FILE_num | 
'SKIP_FILE_num%' | ABORT_STATEMENT"[1]
- Redshift has MAXERROR and IGNOREALLERRORS options[2]

BTW after seeing Snowflake makes SKIP_FILE_num one of the options of 
ON_ERROR,  I'm a bit wondering whether REJECT_LIMIT also should be the 
same.


[1] 
https://docs.snowflake.com/en/sql-reference/sql/copy-into-table#copy-options-copyoptions
[2] 
https://docs.aws.amazon.com/en_en/redshift/latest/dg/copy-parameters-data-load.html

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation