On Fri, Feb 16, 2024 at 8:17 PM torikoshia <torikos...@oss.nttdata.com> wrote:
>
> I may be wrong since I seldom do data loading tasks, but I greed with
> you.
>
> I also a little concerned about the case where there are many malformed
> data and it causes lots of messages, but the information is usually
> valuable and if users don't need it, they can suppress it by changing
> client_min_messages.
>
> Currently both summary of failures and individual information is logged
> in NOTICE level.
> If we should assume that there are cases where only summary information
> is required, it'd be useful to set lower log level, i.e. LOG to the
> individual information.
How about we emit the summary at INFO level and individual information
at NOTICE level? With this, the summary is given a different priority
than the individual info. With SET client_min_messages = WARNING; one
can still get the summary but not the individual info. Also, to get
all of these into server log, one can SET log_min_messages = INFO; or
SET log_min_messages = NOTICE;.
Thoughts?
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From 3cdb3512ec1cffbaeae00d0e3cc41c57021fd6ee Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 17 Feb 2024 05:43:19 +0000
Subject: [PATCH v2] Add detailed info when COPY skips soft errors
This commit emits individual info like line number and column name
when COPY skips soft errors. Because, the summary containing the
total rows skipped isn't enough for the users to know what exactly
are the malformed rows in the input data.
It emits individual info and summary at NOTICE and INFO level
respectively to let users switch of individual info by changing
client_min_messages to WARNING. Also, one can get all of these
information into server logs by changing log_min_messages.
---
src/backend/commands/copyfrom.c | 2 +-
src/backend/commands/copyfromparse.c | 12 +++++++++++-
src/test/regress/expected/copy2.out | 6 +++++-
3 files changed, 17 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 1fe70b9133..e11c2d1cff 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1314,7 +1314,7 @@ CopyFrom(CopyFromState cstate)
if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
cstate->num_errors > 0)
- ereport(NOTICE,
+ ereport(INFO,
errmsg_plural("%llu row was skipped due to data type incompatibility",
"%llu rows were skipped due to data type incompatibility",
(unsigned long long) cstate->num_errors,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 7cacd0b752..747e173d9c 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -969,7 +969,17 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
&values[m]))
{
cstate->num_errors++;
- return true;
+
+ if (cstate->opts.on_error != COPY_ON_ERROR_STOP)
+ {
+ ereport(NOTICE,
+ errmsg("detected data type incompatibility at line number %llu for column %s; COPY %s",
+ (unsigned long long) cstate->cur_lineno,
+ cstate->cur_attname,
+ cstate->cur_relname));
+
+ return true;
+ }
}
cstate->cur_attname = NULL;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 25c401ce34..15a1da2eac 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -730,7 +730,11 @@ COPY check_ign_err FROM STDIN WITH (on_error stop);
ERROR: invalid input syntax for type integer: "a"
CONTEXT: COPY check_ign_err, line 2, column n: "a"
COPY check_ign_err FROM STDIN WITH (on_error ignore);
-NOTICE: 4 rows were skipped due to data type incompatibility
+NOTICE: detected data type incompatibility at line number 2 for column n; COPY check_ign_err
+NOTICE: detected data type incompatibility at line number 3 for column k; COPY check_ign_err
+NOTICE: detected data type incompatibility at line number 4 for column m; COPY check_ign_err
+NOTICE: detected data type incompatibility at line number 5 for column n; COPY check_ign_err
+INFO: 4 rows were skipped due to data type incompatibility
SELECT * FROM check_ign_err;
n | m | k
---+-----+---
--
2.34.1
