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

From badf71c273c4a496bcf14701d973f768f24fa7fd Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 13 Mar 2024 02:30:46 +0000
Subject: [PATCH v7 2/2] 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
---
 doc/src/sgml/ref/copy.sgml           | 12 ++++++++++--
 src/backend/commands/copyfromparse.c |  9 +++++++++
 src/test/regress/expected/copy2.out  | 14 +++++++++++---
 src/test/regress/sql/copy2.sql       |  7 ++++++-
 4 files changed, 36 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index eba9b8f64e..bdd6580721 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 of the input file and the column name whose input
+      conversion has failed is emitted for each discarded row.
      </para>
     </listitem>
    </varlistentry>
@@ -426,6 +430,10 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       <literal>verbose</literal> can be used to emit more informative messages.
       <literal>default</literal> will not log any additional messages.
      </para>
+     <para>
+      This is currently used in <command>COPY FROM</command> command when
+      <literal>ON_ERROR</literal> 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..e4a89eef13 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -967,7 +967,16 @@ 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("data type incompatibility at line %llu for column \"%s\"",
+								   (unsigned long long) cstate->cur_lineno,
+								   cstate->cur_attname));
+
 				return true;
 			}
 
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 62406ef827..af669fedbe 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -737,14 +737,22 @@ 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);
-NOTICE:  4 rows were skipped due to data type incompatibility
+-- tests for options on_error and log_verbosity
+COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity 'verbose');
+NOTICE:  data type incompatibility at line 2 for column "n"
+NOTICE:  data type incompatibility at line 3 for column "k"
+NOTICE:  data type incompatibility at line 4 for column "m"
+NOTICE:  data type incompatibility at line 5 for column "n"
+NOTICE:  data type incompatibility at line 7 for column "m"
+NOTICE:  data type incompatibility at line 8 for column "k"
+NOTICE:  6 rows were skipped due to data type incompatibility
 SELECT * FROM check_ign_err;
  n |  m  | k 
 ---+-----+---
  1 | {1} | 1
  5 | {5} | 5
-(2 rows)
+ 8 | {8} | 8
+(3 rows)
 
 -- test datatype error that can't be handled as soft: should fail
 CREATE TABLE hard_err(foo widget);
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 5116157cc9..4fb736535d 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -510,13 +510,18 @@ 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
 4	{a, 4}	4
 
 5	{5}	5
+6	a
+7	{7}	a
+8	{8}	8
 \.
 SELECT * FROM check_ign_err;
 
-- 
2.34.1