Add new error_action COPY ON_ERROR "log"

torikoshia <torikoshia@oss.nttdata.com>

From: torikoshia <torikoshia@oss.nttdata.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2024-01-25T16:42:14Z
Lists: pgsql-hackers

Attachments

Hi,

As described in 9e2d870119, COPY ON_EEOR is expected to have more 
"error_action".
(Note that option name was changed by b725b7eec)

I'd like to have a new option "log", which skips soft errors and logs 
information that should have resulted in errors to PostgreSQL log.

I think this option has some advantages like below:

1) We can know which number of line input data was not loaded and 
reason.

   Example:

   =# copy t1 from stdin with (on_error log);
   Enter data to be copied followed by a newline.
   End with a backslash and a period on a line by itself, or an EOF 
signal.
   >> 1
   >> 2
   >> 3
   >> z
   >> \.
   LOG:  invalid input syntax for type integer: "z"
   NOTICE:  1 row was skipped due to data type incompatibility
   COPY 3

   =# \! tail data/log/postgresql*.log
   LOG:  22P02: invalid input syntax for type integer: "z"
   CONTEXT:  COPY t1, line 4, column i: "z"
   LOCATION:  pg_strtoint32_safe, numutils.c:620
   STATEMENT:  copy t1 from stdin with (on_error log);


2) Easier maintenance than storing error information in tables or 
proprietary log files.
For example, in case a large number of soft errors occur, some 
mechanisms are needed to prevent an infinite increase in the size of the 
destination data, but we can left it to PostgreSQL's log rotation.


Attached a patch.
This basically comes from previous discussion[1] which did both "ignore" 
and "log" soft error.

As shown in the example above, the log output to the client does not 
contain CONTEXT, so I'm a little concerned that client cannot see what 
line of the input data had a problem without looking at the server log.


What do you think?

[1] 
https://www.postgresql.org/message-id/c0fb57b82b150953f26a5c7e340412e8%40oss.nttdata.com

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation

Commits

  1. Add new COPY option LOG_VERBOSITY.

  2. Add tests for more row patterns with COPY FROM .. (ON_ERROR ignore)

  3. Support writing "CREATE/ALTER TABLE ... SET STORAGE DEFAULT".