Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

On 2023-02-06 15:00, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
>> On February 5, 2023 9:12:17 PM PST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> wrote:
>>> Damir Belyalov <dam(dot)bel07(at)gmail(dot)com> writes:
>>>> InputFunctionCallSafe() is good for detecting errors from
>>>> input-functions
>>>> but there are such errors from NextCopyFrom () that can not be
>>>> detected
>>>> with InputFunctionCallSafe(), e.g. "wrong number of columns in
>>>> row''.
>
>>> If you want to deal with those, then there's more work to be done to
>>> make
>>> those bits non-error-throwing. But there's a very finite amount of
>>> code
>>> involved and no obvious reason why it couldn't be done.
>
>> I'm not even sure it makes sense to avoid that kind of error. And
>> invalid column count or such is something quite different than failing
>> some data type input routine, or falling a constraint.
>
> I think it could be reasonable to put COPY's overall-line-format
> requirements on the same level as datatype input format violations.
> I agree that trying to trap every kind of error is a bad idea,
> for largely the same reason that the soft-input-errors patches
> only trap certain kinds of errors: it's too hard to tell whether
> an error is an "internal" error that it's scary to continue past.

Is it a bad idea to limit the scope of allowing errors to 'soft' errors
in InputFunctionCallSafe()?

I think it could be still useful for some usecases.

diff --git a/src/test/regress/sql/copy2.sql
b/src/test/regress/sql/copy2.sql

+-- tests for IGNORE_DATATYPE_ERRORS option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH IGNORE_DATATYPE_ERRORS;
+1 {1} 1
+a {2} 2
+3 {3} 3333333333
+4 {a, 4} 4
+
+5 {5} 5
+\.
+SELECT * FROM check_ign_err;

diff --git a/src/test/regress/expected/copy2.out
b/src/test/regress/expected/copy2.out
index 090ef6c7a8..08e8056fc1 100644

+-- tests for IGNORE_DATATYPE_ERRORS option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH IGNORE_DATATYPE_ERRORS;
+WARNING: invalid input syntax for type integer: "a"
+WARNING: value "3333333333" is out of range for type integer
+WARNING: invalid input syntax for type integer: "a"
+WARNING: invalid input syntax for type integer: ""
+SELECT * FROM check_ign_err;
+ n | m | k
+---+-----+---
+ 1 | {1} | 1
+ 5 | {5} | 5
+(2 rows)

--
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION