Re: Add new error_action COPY ON_ERROR "log"

On Fri, Jan 26, 2024 at 10:44 PM jian he <jian(dot)universality(at)gmail(dot)com>
wrote:

> I doubt the following part:
> If the <literal>log</literal> value is specified,
> <command>COPY</command> behaves the same as
> <literal>ignore</literal>, exept that
> it logs information that should have resulted in errors to
> PostgreSQL log at
> <literal>INFO</literal> level.
>
> I think it does something like:
> When an error happens, cstate->escontext->error_data->elevel will be
> ERROR
> you manually change the cstate->escontext->error_data->elevel to LOG,
> then you call ThrowErrorData.
>
> but it's not related to `<literal>INFO</literal> level`?
> my log_min_messages is default, warning.

Thanks!

Modified them to NOTICE in accordance with the following summary
message:
> NOTICE: x row was skipped due to data type incompatibility

On 2024-01-27 00:43, David G. Johnston wrote:
> On Thu, Jan 25, 2024 at 9:42 AM torikoshia
> <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
>
>> 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.
>
> Seems like an easy win but largely unhelpful in the typical case. I
> suppose ETL routines using this feature may be running on their
> machine under root or "postgres" but in a system where they are not
> this very useful information is inaccessible to them. I suppose the
> DBA could set up an extractor to send these specific log lines
> elsewhere but that seems like enough hassle to disfavor this approach
> and favor one that can place the soft error data and feedback into
> user-specified tables in the same database. Setting up temporary
> tables or unlogged tables probably is going to be a more acceptable
> methodology than trying to get to the log files.
>
> David J.

I agree that not a few people would prefer to store error information in
tables and there have already been suggestions[1].

OTOH not everyone thinks saving table information is the best idea[2].

I think it would be desirable for ON_ERROR to be in a form that allows
the user to choose where to store error information from among some
options, such as table, log and file.

"ON_ERROR log" would be useful at least in the case of 'running on their
machine under root or "postgres"' as you pointed out.

[1]
https://www.postgresql.org/message-id/CACJufxEkkqnozdnvNMGxVAA94KZaCPkYw_Cx4JKG9ueNaZma_A%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/20231109002600.fuihn34bjqqgmbjm@awork3.anarazel.de

--
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation