Re: Add new error_action COPY ON_ERROR "log"

On Fri, Jan 26, 2024 at 10:44 PM jian he <jian.universality@gmail.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@oss.nttdata.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