v5-0002-Add-detailed-info-when-COPY-skips-soft-errors.patch

From e72035d692fb092376f6a2aaa047a94e129fcbfc Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 8 Mar 2024 09:48:36 +0000
Subject: [PATCH v5 2/2] Add detailed info when COPY skips soft errors

---
 doc/src/sgml/ref/copy.sgml           | 12 +++++++++---
 src/backend/commands/copyfromparse.c | 10 ++++++++++
 src/test/regress/expected/copy2.out  |  7 ++++++-
 src/test/regress/sql/copy2.sql       |  4 +++-
 4 files changed, 28 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 67ba6212fe..ef5a12e0d3 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 for each discarded row is
+      emitted.
      </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>
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
-- 
2.34.1