Horton

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

torikoshia <torikoshia@oss.nttdata.com>

View thread
From: torikoshia <torikoshia@oss.nttdata.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>, Damir Belyalov <dam.bel07@gmail.com>, Danil Anisimow <anisimow.d@gmail.com>, HukuToc@gmail.com, a.lepikhov@postgrespro.ru, tgl@sss.pgh.pa.us
Date: 2023-02-27T05:58:29Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: Fix COPY ON_ERROR option syntax synopsis.

  2. Disallow specifying ON_ERROR option without value.

  3. Rename COPY option from SAVE_ERROR_TO to ON_ERROR

  4. Fix spelling in notice

  5. Add new COPY option SAVE_ERROR_TO

Attachments

On 2023-02-06 15:00, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
>> On February 5, 2023 9:12:17 PM PST, Tom Lane <tgl@sss.pgh.pa.us> 
>> wrote:
>>> Damir Belyalov <dam.bel07@gmail.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