From 1f087fb0cd740e927c6a475996430e2bb12de845 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 11 Mar 2024 11:53:44 +0000
Subject: [PATCH v6 2/3] 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.

Author: Bharath Rupireddy
Reviewed-by: Michael Paquier, Masahiko Sawada
Reviewed-by: Atsushi Torikoshi
Discussion: https://www.postgresql.org/message-id/CALj2ACUk700cYhx1ATRQyRw-fBM%2BaRo6auRAitKGff7XNmYfqQ%40mail.gmail.com
---
 src/backend/commands/copyfromparse.c | 10 ++++++++++
 src/test/regress/expected/copy2.out  |  7 ++++++-
 src/test/regress/sql/copy2.sql       |  4 +++-
 doc/src/sgml/ref/copy.sgml           | 12 +++++++++---
 4 files changed, 28 insertions(+), 5 deletions(-)

diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 5682d5d054..a7ad6c17c8 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -967,7 +967,17 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 											(Node *) cstate->escontext,
 											&values[m]))
 			{
+				Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
+
 				cstate->num_errors++;
+
+				if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+					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;
 			}
 
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 62406ef827..c6655000e4 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -737,7 +737,12 @@ CREATE TABLE check_ign_err (n int, m int[], k int);
 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);
+-- tests for options on_error and log_verbosity
+COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity 'verbose');
+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
 NOTICE:  4 rows were skipped due to data type incompatibility
 SELECT * FROM check_ign_err;
  n |  m  | k 
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 5116157cc9..b637a5b3bb 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -510,7 +510,9 @@ a	{2}	2
 
 5	{5}	5
 \.
-COPY check_ign_err FROM STDIN WITH (on_error ignore);
+
+-- tests for options on_error and log_verbosity
+COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity 'verbose');
 1	{1}	1
 a	{2}	2
 3	{3}	3333333333
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 67ba6212fe..ed7fdc59fb 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -398,8 +398,12 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
      </para>
      <para>
-      A <literal>NOTICE</literal> message containing the ignored row count is emitted at the end
-      of the <command>COPY FROM</command> if at least one row was discarded.
+      A <literal>NOTICE</literal> message containing the ignored row count is
+      emitted at the end of the <command>COPY FROM</command> if at least one
+      row was discarded. When <literal>LOG_VERBOSITY</literal> option is set to
+      <literal>verbose</literal>, a <literal>NOTICE</literal> message
+      containing the line number and column name (whose input conversion has
+      failed) is emitted for each discarded row.
      </para>
     </listitem>
    </varlistentry>
@@ -424,7 +428,9 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       A <replaceable class="parameter">mode</replaceable> value of
       <literal>verbose</literal> can be used to emit more informative messages
       by the command, while value of <literal>default</literal> (which is the
-      default) can be used to not log any additional messages.
+      default) can be used to not log any additional messages. As an example,
+      see its usage for <command>COPY FROM</command> command when
+      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
      </para>
     </listitem>
    </varlistentry>
-- 
2.43.0