Add new error_action COPY ON_ERROR "log"

From: torikoshia <torikoshia(at)oss(dot)nttdata(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add new error_action COPY ON_ERROR "log"
Date: 2024-01-25 16:42:14
Message-ID: 9e40f7a7864c0456ab9b8d3ff85b59d0@oss.nttdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-new-error_action-log-to-ON_ERROR.patch text/x-diff 6.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2024-01-25 16:47:06 Re: index prefetching
Previous Message Alvaro Herrera 2024-01-25 16:33:43 Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock