Re: Add new error_action COPY ON_ERROR "log"

On Wed, Mar 13, 2024 at 11:09 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> Hmm.  This NOTICE is really bugging me.  It is true that the clients
> would get more information, but the information is duplicated on the
> server side because the error context provides the same information as
> the NOTICE itself:
> NOTICE:  data type incompatibility at line 1 for column "a"
> CONTEXT:  COPY aa, line 1, column a: "a"
> STATEMENT:  copy aa from stdin with (on_error ignore, log_verbosity verbose);

Yes, if wanted, clients can also get the CONTEXT - for instance, using
'\set SHOW_CONTEXT always' in psql.

I think we can enhance the NOTICE message to include the column value
(just like CONTEXT message is showing) and leverage relname_only to
emit only the relation name in the CONTEXT message.

        /*
         * We suppress error context information other than the relation name,
         * if one of the operations below fails.
         */
        Assert(!cstate->relname_only);
        cstate->relname_only = true;

I'm attaching the v8 patch set implementing the above idea. With this,
[1] is sent to the client, [2] is sent to the server log. This
approach not only reduces the duplicate info in the NOTICE and CONTEXT
messages, but also makes it easy for users to get all the necessary
info in the NOTICE message without having to set extra parameters to
get CONTEXT message.

Another idea is to move even the table name to NOTICE message and hide
the context with errhidecontext when we emit the new NOTICE messages.

Thoughts?

[1]
NOTICE:  data type incompatibility at line 2 for column n: "a"
NOTICE:  data type incompatibility at line 3 for column k: "3333333333"
NOTICE:  data type incompatibility at line 4 for column m: "{a, 4}"
NOTICE:  data type incompatibility at line 5 for column n: ""
NOTICE:  data type incompatibility at line 7 for column m: "a"
NOTICE:  data type incompatibility at line 8 for column k: "a"
NOTICE:  6 rows were skipped due to data type incompatibility
COPY 3

[2]
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 2 for column n: "a"
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 3 for column k: "3333333333"
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 4 for column m: "{a, 4}"
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 5 for column n: ""
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 7 for column m: "a"
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  data type
incompatibility at line 8 for column k: "a"
2024-03-13 13:49:14.138 UTC [1330270] CONTEXT:  COPY check_ign_err
2024-03-13 13:49:14.138 UTC [1330270] NOTICE:  6 rows were skipped due
to data type incompatibility

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com