Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

From: Damir Belyalov <[email-masked]>
Date: Fri, 15 Sep 2023 13:02:31 +0300

> Since v5 patch failed applying anymore, updated the patch.
Thank you for updating the patch . I made a little review on it where
corrected some formatting.

> - COPY with a datatype error that can't be handled as a soft error
>
> I didn't know proper way to test this, but I've found data type widget's
> input function widget_in() defined to occur hard-error in regress.c,
> attached patch added a test using it.
>

This test seems to be weird a bit, because of the "widget" type. The hard
error is thrown by the previous test with missing data. Also it'll be
interesting for me to list all cases when a hard error can be thrown.

Regards,
Damir Belyalov
Postgres Professional
From 0e1193e00bb5ee810a015a2baaf7c79e395a54c7 Mon Sep 17 00:00:00 2001
From: Damir Belyalov <[email-masked]>
Date: Fri, 15 Sep 2023 11:14:57 +0300
Subject: [PATCH] ignore errors

---
 doc/src/sgml/ref/copy.sgml               | 13 +++++++++
 src/backend/commands/copy.c              | 13 +++++++++
 src/backend/commands/copyfrom.c          | 37 ++++++++++++++++++++++++
 src/backend/commands/copyfromparse.c     | 20 ++++++++++---
 src/bin/psql/tab-complete.c              |  3 +-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  3 ++
 src/test/regress/expected/copy2.out      | 28 ++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 26 +++++++++++++++++
 9 files changed, 139 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 4d614a0225..d5cdbb4025 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
+    IGNORE_DATATYPE_ERRORS [ <replaceable class="parameter">boolean</replaceable> ]
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
 </synopsis>
  </refsynopsisdiv>
@@ -370,6 +371,18 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>IGNORE_DATATYPE_ERRORS</literal></term>
+    <listitem>
+     <para>
+      Drops rows that contain malformed data while copying. These are rows
+      with columns where the data type's input-function raises an error.
+      This option is not allowed when using binary format.  Note that this
+      is only supported in current <command>COPY</command> syntax.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>ENCODING</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index f14fae3308..beb73f5357 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -419,6 +419,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		format_specified = false;
 	bool		freeze_specified = false;
 	bool		header_specified = false;
+	bool		ignore_datatype_errors_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -458,6 +459,13 @@ ProcessCopyOptions(ParseState *pstate,
 			freeze_specified = true;
 			opts_out->freeze = defGetBoolean(defel);
 		}
+		else if (strcmp(defel->defname, "ignore_datatype_errors") == 0)
+		{
+			if (ignore_datatype_errors_specified)
+				errorConflictingDefElem(defel, pstate);
+			ignore_datatype_errors_specified = true;
+			opts_out->ignore_datatype_errors = defGetBoolean(defel);
+		}
 		else if (strcmp(defel->defname, "delimiter") == 0)
 		{
 			if (opts_out->delim)
@@ -594,6 +602,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify DEFAULT in BINARY mode")));
 
+	if (opts_out->binary && opts_out->ignore_datatype_errors)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify IGNORE_DATATYPE_ERRORS in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 70871ed819..b18aea6376 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -752,6 +752,14 @@ CopyFrom(CopyFromState cstate)
 		ti_options |= TABLE_INSERT_FROZEN;
 	}
 
+	/* Set up soft error handler for IGNORE_DATATYPE_ERRORS */
+	if (cstate->opts.ignore_datatype_errors)
+	{
+		ErrorSaveContext escontext = {T_ErrorSaveContext};
+		escontext.details_wanted = true;
+		cstate->escontext = escontext;
+	}
+
 	/*
 	 * We need a ResultRelInfo so we can use the regular executor's
 	 * index-entry-making machinery.  (There used to be a huge amount of code
@@ -987,7 +995,36 @@ CopyFrom(CopyFromState cstate)
 
 		/* Directly store the values/nulls array in the slot */
 		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
+		{
+			if (cstate->opts.ignore_datatype_errors &&
+				cstate->ignored_errors_count > 0)
+				ereport(WARNING,
+						errmsg("%zd rows were skipped due to data type incompatibility",
+							   cstate->ignored_errors_count));
 			break;
+		}
+
+		/* Soft error occured, skip this tuple and log the reason */
+		if (cstate->escontext.error_occurred)
+		{
+			ErrorSaveContext new_escontext = {T_ErrorSaveContext};
+
+			/* Adjust elevel so we don't jump out */
+			cstate->escontext.error_data->elevel = WARNING;
+
+			/*
+			 * Despite the name, this won't raise an error since elevel is
+			 * WARNING now.
+			 */
+			ThrowErrorData(cstate->escontext.error_data);
+
+			ExecClearTuple(myslot);
+
+			new_escontext.details_wanted = true;
+			cstate->escontext = new_escontext;
+
+			continue;
+		}
 
 		ExecStoreVirtualTuple(myslot);
 
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index f553734582..cf4dad1106 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -70,6 +70,7 @@
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -956,10 +957,21 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
 			}
 			else
-				values[m] = InputFunctionCall(&in_functions[m],
-											  string,
-											  typioparams[m],
-											  att->atttypmod);
+				/*
+				 * If IGNORE_DATATYPE_ERRORS is enabled, skip rows with
+				 * datatype errors.
+				 */
+				if (!InputFunctionCallSafe(&in_functions[m],
+										   string,
+										   typioparams[m],
+										   att->atttypmod,
+										   (Node *) &cstate->escontext,
+										   &values[m]))
+				{
+					cstate->ignored_errors_count++;
+
+					return true;
+				}
 
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 779fdc90cb..2fba51f648 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2869,7 +2869,8 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
+					  "IGNORE_DATATYPE_ERRORS");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 33175868f6..c2e55ac21f 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -42,6 +42,7 @@ typedef struct CopyFormatOptions
 								 * -1 if not specified */
 	bool		binary;			/* binary format? */
 	bool		freeze;			/* freeze rows on loading? */
+	bool		ignore_datatype_errors;  /* ignore rows with datatype errors */
 	bool		csv_mode;		/* Comma Separated Value format? */
 	CopyHeaderChoice header_line;	/* header line? */
 	char	   *null_print;		/* NULL marker string (server encoding!) */
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index ac2c16f8b8..e5bdae2d25 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@
 
 #include "commands/copy.h"
 #include "commands/trigger.h"
+#include "nodes/miscnodes.h"
 
 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,8 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	ErrorSaveContext escontext; /* soft error trapper during in_functions execution */
+	int64		ignored_errors_count; /* total number of ignored errors */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index faf1a4d1b0..ac9c99f083 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -82,6 +82,8 @@ COPY x to stdin (format BINARY, delimiter ',');
 ERROR:  cannot specify DELIMITER in BINARY mode
 COPY x to stdin (format BINARY, null 'x');
 ERROR:  cannot specify NULL in BINARY mode
+COPY x to stdin (format BINARY, ignore_datatype_errors);
+ERROR:  cannot specify IGNORE_DATATYPE_ERRORS in BINARY mode
 COPY x to stdin (format TEXT, force_quote(a));
 ERROR:  COPY force quote available only in CSV mode
 COPY x from stdin (format CSV, force_quote(a));
@@ -666,6 +668,30 @@ SELECT * FROM instead_of_insert_tbl;
 (2 rows)
 
 COMMIT;
+-- tests for IGNORE_DATATYPE_ERRORS option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+WARNING:  invalid input syntax for type integer: "a"
+WARNING:  value "3333333333" is out of range for type integer
+WARNING:  invalid input syntax for type integer: "a"
+WARNING:  invalid input syntax for type integer: ""
+WARNING:  4 rows were skipped due to data type incompatibility
+SELECT * FROM check_ign_err;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 5 | {5} | 5
+(2 rows)
+
+-- test datatype error that can't be handled as soft: should fail
+CREATE TABLE hard_err(foo widget);
+COPY hard_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+ERROR:  invalid input syntax for type widget: "1"
+CONTEXT:  COPY hard_err, line 1, column foo: "1"
+-- test missing data: should fail
+COPY check_ign_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+ERROR:  missing data for column "k"
+CONTEXT:  COPY check_ign_err, line 1: "1	{1}"
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -680,6 +706,8 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+DROP TABLE check_ign_err;
+DROP TABLE hard_err;
 --
 -- COPY FROM ... DEFAULT
 --
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index d759635068..e8c2c1aca3 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -70,6 +70,7 @@ COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
 -- incorrect options
 COPY x to stdin (format BINARY, delimiter ',');
 COPY x to stdin (format BINARY, null 'x');
+COPY x to stdin (format BINARY, ignore_datatype_errors);
 COPY x to stdin (format TEXT, force_quote(a));
 COPY x from stdin (format CSV, force_quote(a));
 COPY x to stdout (format TEXT, force_not_null(a));
@@ -464,6 +465,29 @@ test1
 SELECT * FROM instead_of_insert_tbl;
 COMMIT;
 
+-- tests for IGNORE_DATATYPE_ERRORS option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+1	{1}	1
+a	{2}	2
+3	{3}	3333333333
+4	{a, 4}	4
+
+5	{5}	5
+\.
+SELECT * FROM check_ign_err;
+
+-- test datatype error that can't be handled as soft: should fail
+CREATE TABLE hard_err(foo widget);
+COPY hard_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+1
+\.
+
+-- test missing data: should fail
+COPY check_ign_err FROM STDIN WITH (IGNORE_DATATYPE_ERRORS);
+1	{1}
+\.
+
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -478,6 +502,8 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+DROP TABLE check_ign_err;
+DROP TABLE hard_err;
 
 --
 -- COPY FROM ... DEFAULT
-- 
2.34.1

---

From: torikoshia <[email-masked]>
Date: Tue, 19 Sep 2023 23:00:32 +0900

> corrected some formatting.

Thanks for your review and update!
I don't have objections the modification of the codes and comments.
Although v7 patch doesn't have commit messages on the patch, I think 
leave commit message is good for reviewers.

> This test seems to be weird a bit, because of the "widget" type. The
> hard error is thrown by the previous test with missing data. Also
> it'll be interesting for me to list all cases when a hard error can be
> thrown.

Although missing data error is hard error, the suggestion from Andres 

> - COPY with a datatype error that can't be handled as a soft error

As described in widghet_in(), widget is intentionally left emitting hard 
error for testing purpose:

>   * Note: DON'T convert this error to "soft" style (errsave/ereturn).  
> We
>   * want this data type to stay permanently in the hard-error world so 
> that
>   * it can be used for testing that such cases still work reasonably.

 From this point of view, I think this is a supposed way of using widget.
OTOH widget is declared in create_type.sql and I'm not sure it's ok to 
use it in another test copy2.sql.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation

---

From: Damir <[email-masked]>
Date: Wed, 20 Sep 2023 19:15:15 +0300

> leave commit message is good for reviewers.

Sure, didn't notice it. Added the commit message to the updated patch.

>   * Note: DON'T convert this error to "soft" style (errsave/ereturn). We
>>   * want this data type to stay permanently in the hard-error world 
>> so that

I agree, it's a good approach for checking datatype errors, because 
that's what was intended.

> use it in another test copy2.sql.

I think that other regress tests with 'widget' type that will be created 
in the future can be not only in the create_type.sql. So it's not a 
problem that some type or function is taken from another regress test. 
For example, the table 'onek' is used in many regress tests.

Regards,

Damir Belyalov

Postgres Professional
From 0e1193e00bb5ee810a015a2baaf7c79e395a54c7 Mon Sep 17 00:00:00 2001
From: Damir Belyalov <[email-masked]>
Date: Fri, 15 Sep 2023 11:14:57 +0300
Subject: [PATCH v7] Add new COPY option IGNORE_DATATYPE_ERRORS

Currently entire COPY fails even when there is one unexpected data
regarding data type or range.
IGNORE_DATATYPE_ERRORS ignores these errors and skips them and COPY
data which don't contain problem.

This patch uses the soft error handling infrastructure, which is
introduced by d9f7f5d32f20.

Author: Damir Belyalov, Atsushi Torikoshi

---

+   <varlistentry>

 

 

 
 
 /*
 
 COMMIT;

 --
 -- COPY FROM ... DEFAULT
-- 
2.34.1

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Nov 2023 13:18:39 -0500

Damir <[email-masked]> writes:
> [ v7-0002-Add-new-COPY-option-IGNORE_DATATYPE_ERRORS.patch ]

Sorry for being so late to the party, but ... I don't think this
is a well-designed feature as it stands.  Simply dropping failed rows
seems like an unusable definition for any application that has
pretensions of robustness.  "But", you say, "we're emitting WARNING
messages about it".  That's *useless*.  For most applications WARNING
messages just go into the bit bucket, or worse they cause memory leaks
(because the app never reads them).  An app that tried to read them
would have to cope with all sorts of fun such as translated messages.
Furthermore, as best I can tell from the provided test cases, the
messages completely lack basic context such as which field or line
the problem occurred in.  An app trying to use this to understand
which input lines had failed would not get far.

I think an actually usable feature of this sort would involve
copying all the failed lines to some alternate output medium,
perhaps a second table with a TEXT column to receive the original
data line.  (Or maybe an array of text that could receive the
broken-down field values?)  Maybe we could dump the message info,
line number, field name etc into additional columns.

Also it'd be a good idea to have a vision of how the feature
could be extended to cope with lower-level errors, such as
lines that have the wrong number of columns or other problems
with line-level syntax.  I don't say we need to cope with that
immediately, but it's going to be something people will want
to add, I think.

			regards, tom lane

---

From: Daniel Gustafsson <[email-masked]>
Date: Wed, 8 Nov 2023 20:34:51 +0100

> On 8 Nov 2023, at 19:18, Tom Lane <[email-masked]> wrote:

> line number, field name etc into additional columns.

I agree that the errors should be easily visible to the user in some way.  The
feature is for sure interesting, especially in data warehouse type jobs where
dirty data is often ingested.

As a data point, Greenplum has this feature with additional SQL syntax to
control it:

	COPY .. LOG ERRORS SEGMENT REJECT LIMIT xyz ROWS;

LOG ERRORS instructs the database to log the faulty rows and SEGMENT REJECT
LIMIT xyz ROWS sets the limit of how many rows can be faulty before the
operation errors out.  I'm not at all advocating that we should mimic this,
just wanted to add a reference to postgres derivative where this has been
implemented.

--
Daniel Gustafsson

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Nov 2023 15:12:16 -0500

> dirty data is often ingested.

I agree it's interesting, but we need to get it right the first time.

Here is a very straw-man-level sketch of what I think might work.
The option to COPY FROM looks something like

	ERRORS TO other_table_name (item [, item [, ...]])

where the "items" are keywords identifying the information item
we will insert into each successive column of the target table.
This design allows the user to decide which items are of use
to them.  I envision items like

LINENO	bigint		COPY line number, counting from 1
LINE	text		raw text of line (after encoding conversion)
FIELDS	text[]		separated, de-escaped string fields (the data
			that was or would be fed to input functions)
FIELD	text		name of troublesome field, if field-specific
MESSAGE	text		error message text
DETAIL	text		error message detail, if any
SQLSTATE text		error SQLSTATE code

Some of these would have to be populated as NULL if we didn't get
that far in processing the line.  In the worst case, which is
encoding conversion failure, I think we couldn't populate any of
the data items except LINENO.

Not sure if we need to insist that the target table columns be
exactly the data types I show above.  It'd be nice to allow
the LINENO target to be plain int, perhaps.  OTOH, do we really
want to have to deal with issues like conversion failures while
trying to report an error?

> just wanted to add a reference to postgres derivative where this has been

Hm.  A "reject limit" might be a useful add-on, but I wouldn't advocate
including it in the initial patch.

			regards, tom lane

---

From: Andres Freund <[email-masked]>
Date: Wed, 8 Nov 2023 15:53:58 -0800

Hi,

On 2023-11-08 13:18:39 -0500, Tom Lane wrote:
> pretensions of robustness.

Not everything needs to be a robust application though. I've definitely cursed
at postgres for lacking this.

> line number, field name etc into additional columns.

If we go in that direction, we should make it possible to *not* use such a
table as well, for some uses it'd be pointless.

Another way of reporting errors could be for copy to return invalid input back
to the client, via the copy protocol. That would allow the client to handle
failing rows and also to abort if the number of errors or the type of errors
gets to be too big.

Greetings,

Andres Freund

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Nov 2023 19:00:01 -0500

Andres Freund <[email-masked]> writes:
>> data line.

> table as well, for some uses it'd be pointless.

Why?  You can always just drop the errors table if you don't want it.
But I fail to see the use-case for ignoring errors altogether.

> to the client, via the copy protocol.

Color me skeptical.  There are approximately zero clients in the
world today that could handle simultaneous return of data during
a COPY.  Certainly neither libpq nor psql are within hailing
distance of being able to support that.  Maybe in some far
future it could be made to work --- but if you want it in the v1
patch, you just moved the goalposts into the next county.

			regards, tom lane

---

From: Andres Freund <[email-masked]>
Date: Wed, 8 Nov 2023 16:26:00 -0800

Hi,

> Why?  You can always just drop the errors table if you don't want it.

I think it'll often just end up littering the database, particularly if the
callers don't care about a few errors.

> But I fail to see the use-case for ignoring errors altogether.

My experience is that there's often a few errors due to bad encoding, missing
escaping etc that you don't care sufficiently about when importing large
quantities of data.

Greetings,

Andres Freund

---

From: Damir Belyalov <[email-masked]>
Date: Thu, 9 Nov 2023 03:28:25 +0300

Hello everyone!
Thanks for turning back to this patch.

I had already thought about storing errors in the table / separate file /
logfile and it seems to me that the best way is to output errors in
logfile. As for user it is more convenient to look for errors in the place
where they are usually generated - in logfile and if he wants to intercept
them he could easily do that by few commands.

The analogues of this feature in other DBSM usually had additional files
for storing errors, but their features had too many options (see attached
files).
I also think that the best way is to simplify this feature for the first
version and don't use redundant adjustments such as additional files and
other options.
IMHO for more complicated operations with loading tables files pgloader
exists: https://github.com/dimitri/pgloader

Links of analogues of COPY IGNORE_DATATYPE_ERRORS
https://dev.mysql.com/doc/refman/8.0/en/load-data.html
https://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html

Regards,
Damir Belyalov
Postgres Professional

---

From: [email-masked]
Date: Thu, 09 Nov 2023 12:33:34 +0800

> to them.  I envision items like

While I'm pretty happy with the overall design, which is 'ERRORS to
other_table_name' specially. I'm a bit confused why do we need to
write the codes for (item [, item [, ...]]), not only because it
requires more coding but also requires user to make more decisions.
will it be anything wrong to make all of them as default? 

-- 
Best Regards
Andy Fan

---

From: Damir Belyalov <[email-masked]>
Date: Tue, 14 Nov 2023 13:10:18 +0300

>       ERRORS TO other_table_name (item [, item [, ...]])

I tried to implement the patch using a table and came across a number of
questions.

Which table should we implement for this feature: a system catalog table or
store this table as a file or create a new table?

In these cases, security and user rights management issues arise.
It is better for other users not to see error lines from another user. It
is also not clear how access rights to this table are inherited and be
given.

--
Regards,
Damir Belyalov
Postgres Professional

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 14 Nov 2023 13:16:35 +0300

Hi!

On 14.11.2023 13:10, Damir Belyalov wrote:
> I tried to implement the patch using a table and came across a number 
> of questions.
>
> Which table should we implement for this feature: a system catalog 
> table or store this table as a file or create a new table?
>
> It is better for other users not to see error lines from another user. 
> It is also not clear how access rights to this table are inherited and 
> be given.
>
>
Maybe we can add a guc or a parameter to output such errors during the 
execution of the copy function with errors and check whether the user 
has enough rights to set such a parameter?

That is, I propose to give the user a choice to run copy with and 
without saving errors and at the same time immediately check whether the 
option with error output is possible for him in principle?

-- 
Regards,
Alena Rybakina

---

From: [email-masked]
Date: Wed, 15 Nov 2023 09:23:27 +0800

Damir Belyalov <[email-masked]> writes:

> I tried to implement the patch using a table and came across a number of questions.
>
> Which table should we implement for this feature: a system catalog table or store this table as a file or create a new
> table?

I think system catalog should not be a option at the first place since
it requires more extra workload to do.  see the calls of
IsCatalogRelation in heapam.c.

I prefer to create a new normal heap relation rather than a file since
heap realtion probabaly have better APIs. 

> It is better for other users not to see error lines from another
> user. It is also not clear how access rights to this 
> table are inherited and be given.

How about creating the table just allowing the current user to
read/write or just same as the relation we are copying to? 

-- 
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Thu, 16 Nov 2023 08:00:00 +0800

> SQLSTATE text           error SQLSTATE code

just
SAVE ERRORS

automatically create a table to hold the error. (validate
auto-generated table name uniqueness, validate create privilege).
and the table will have the above related info. if no error then table
gets dropped.

---

From: Andrei Lepikhov <[email-masked]>
Date: Fri, 24 Nov 2023 10:52:25 +0700

On 14/11/2023 17:10, Damir Belyalov wrote:
> Which table should we implement for this feature: a system catalog table 
> or store this table as a file or create a new table?
> 

Previous reviews have given helpful ideas about storing errors in the 
new table.
It should be trivial code - use the current table name + 'err' + suffix 
as we already do in the case of conflicting auto-generated index names.
The 'errors table' must inherit any right policies from the table, to 
which we do the copy.

-- 
regards,
Andrei Lepikhov
Postgres Professional

---

From: jian he <[email-masked]>
Date: Mon, 4 Dec 2023 10:23:30 +0800

hi.
here is my implementation based on previous discussions

add a new COPY FROM flag save_error.
save_error only works with non-BINARY flags.
save_error is easier for me to implement, if using "save error" I
worry, 2 words, gram.y will not work.
save_error also works other flag like {csv mode, force_null, force_not_null}

overall logic is:
if  save_error is specified then
   if error_holding table not exists then create one
   if error_holding table exists set error_firsttime to false.
if  save_error is not specified then work as master branch.

if errors happen then insert error info to error_holding table.
if errors do not exist and error_firsttime is true then drop the table.
if errors do not exist and error_firsttime is false then raise a
notice: All the past error holding saved at %s.%s

error holding table:
schema will be the same as COPY destination table.
the table name will be: COPY destination name concatenate with "_error".

error_holding table definition:
CREATE TABLE err_nsp.error_rel (LINENO BIGINT, LINE TEXT,
FIELD TEXT, SOURCE TEXT, ERR_MESSAGE TEXT,
ERR_DETAIL TEXT, ERRORCODE TEXT);

the following field is not implemented.
FIELDS  text[], separated, de-escaped string fields (the data that was
or would be fed to input functions)

because imagine following case:
create type test as (a int, b text);
create table copy_comp (c1 int, c2 test default '(11,test)', c3 date);
copy copy_comp from stdin with (default '\D');
1 \D '2022-07-04'
\.
table copy_comp;

I feel it's hard from textual '\D'  to get text[] `(11,test)` via SPI.
--------------------------------------
demo:

create table copy_default_error_save (
id integer,
text_value text not null default 'test',
ts_value timestamp without time zone not null default '2022-07-05'
);
copy copy_default_error_save from stdin with (save_error, default '\D');
k value '2022-07-04'
z \D '2022-07-03ASKL'
s \D \D
\.

NOTICE:  3 rows were skipped because of error. skipped row saved to
table public.copy_default_error_save_error
select  * from copy_default_error_save_error;
 lineno |               line               |  field   |      source
  |                         err_message                         |
err_detail | errorcode
--------+----------------------------------+----------+------------------+-------------------------------------------------------------+------------+-----------
      1 | k       value   '2022-07-04'     | id       | k
  | invalid input syntax for type integer: "k"                  |
      | 22P02
      2 | z       \D      '2022-07-03ASKL' | id       | z
  | invalid input syntax for type integer: "z"                  |
      | 22P02
      2 | z       \D      '2022-07-03ASKL' | ts_value |
'2022-07-03ASKL' | invalid input syntax for type timestamp:
"'2022-07-03ASKL'" |            | 22007
      3 | s       \D      \D               | id       | s
  | invalid input syntax for type integer: "s"                  |
      | 22P02
(4 rows)

The doc is not so good.

COPY FROM (save_error),  it will not be as fast as COPY FROM (save_error false).
With save_error, we can only use InputFunctionCallSafe, which I
believe is not as fast as InputFunctionCall.
If any conversion error happens, we need to call the SPI interface,
that would add more overhead. also we can only insert error cases row
by row. (maybe we can insert to error_save values(error1), (error2).
(I will try later)...

The main code is about constructing SPI query, and test and test output.
From 7aeb55cb0c8b1b36fd5c468fee0b07d4c13d1a7d Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Sun, 3 Dec 2023 22:58:40 +0800
Subject: [PATCH v8 1/1] Add a new COPY option: SAVE_ERROR. Only works for COPY
 FROM, non-BINARY mode.

Currently NextCopyFrom can have 3 errors reported.
* extra data after last expected column
* missing data for column \"%s\"
* main function InputFunctionCall inside error.

Currently, we only deal with InputFunctionCall errors only.
instead of throw error while copying, save_error will save errors to a table automatically.
We check the table definition via column name and column data type.
if table already exists and meets the condition then errors will save to that table.
While copying, if error never happened, error save table will be dropped at the ending of COPY.

If the error saving table already exists,
meaning at least once COPY FROM errors had happened,
then all the future error will save to that table.
---
 contrib/file_fdw/file_fdw.c              |   4 +-
 doc/src/sgml/ref/copy.sgml               |  88 +++++++++++++
 src/backend/commands/copy.c              |  12 ++
 src/backend/commands/copyfrom.c          | 151 ++++++++++++++++++++++-
 src/backend/commands/copyfromparse.c     |  89 ++++++++++++-
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   3 +-
 src/include/commands/copyfrom_internal.h |   7 ++
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 132 ++++++++++++++++++++
 src/test/regress/sql/copy2.sql           |  99 +++++++++++++++
 12 files changed, 585 insertions(+), 12 deletions(-)

diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 2189be8a..2d3eb34f 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -751,7 +751,7 @@ fileIterateForeignScan(ForeignScanState *node)
 	 */
 	oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
 	found = NextCopyFrom(festate->cstate, econtext,
-						 slot->tts_values, slot->tts_isnull);
+						 slot->tts_values, slot->tts_isnull, NULL);
 	if (found)
 		ExecStoreVirtualTuple(slot);
 
@@ -1183,7 +1183,7 @@ file_acquire_sample_rows(Relation onerel, int elevel,
 		MemoryContextReset(tupcontext);
 		MemoryContextSwitchTo(tupcontext);
 
-		found = NextCopyFrom(cstate, NULL, values, nulls);
+		found = NextCopyFrom(cstate, NULL, values, nulls, NULL);
 
 		MemoryContextSwitchTo(oldcontext);
 
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 18ecc69c..06096fa6 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -44,6 +44,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    SAVE_ERROR [ <replaceable class="parameter">boolean</replaceable> ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -411,6 +412,17 @@ WHERE <replaceable class="parameter">condition</replaceable>
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>SAVE_ERROR</literal></term>
+    <listitem>
+     <para>
+      Specifies malformed data make data type conversion failure while copying will automatically report error information to a regualar table.
+      This option is not allowed when using <literal>binary</literal> format. Note that this
+      is only supported in current <command>COPY FROM</command> syntax.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
@@ -572,6 +584,13 @@ COPY <replaceable class="parameter">count</replaceable>
     null strings to null values and unquoted null strings to empty strings.
    </para>
 
+   <para>
+    if <literal>SAVE_ERROR</literal> spceicfied, error actually happened then
+     <productname>PostgreSQL</productname> will create one table for you, if no error happened
+     error_table not exist, nothing will happed.
+
+   </para>
+
  </refsect1>
 
  <refsect1>
@@ -962,6 +981,75 @@ versions of <productname>PostgreSQL</productname>.
      check against somehow getting out of sync with the data.
     </para>
    </refsect3>
+
+   <refsect3>
+    <title>Error Save Table </title>
+    <para>
+        If <literal>SAVE_ERROR</literal> spceicfied, all the data type conversion fail while copying will automatically saved in a regular table.
+        <xref linkend="copy-errorsave-table"/> shows the error save table name, data type, and description.
+    </para>
+
+   <table id="copy-errorsave-table">
+
+    <title>COPY ERROR SAVE TABLE </title>
+
+    <tgroup cols="2">
+     <thead>
+      <row>
+       <entry>Column name</entry>
+       <entry>Data type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+
+      <tbody>
+       <row>
+       <entry> <literal>lineno</literal> </entry>
+       <entry><type>bigint</type></entry>
+       <entry>Line number where error occurred, counting from 1</entry>
+       </row>
+
+       <row>
+       <entry> <literal>line</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry> Raw content of error occuring line</entry>
+       </row>
+
+       <row>
+       <entry> <literal>field</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry> Field name of the error occuring </entry>
+       </row>
+
+       <row>
+       <entry> <literal>source</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry> Raw content of the error occuring field </entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_message </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error message text </entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_detail</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry> Detailed error message </entry>
+       </row>
+
+       <row>
+       <entry> <literal>errorcode </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry> The error code for the copying error <literal>*</literal> </entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </table>
+   </refsect3>
+
   </refsect2>
  </refsect1>
 
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index cfad47b5..bc4af10a 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -419,6 +419,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		format_specified = false;
 	bool		freeze_specified = false;
 	bool		header_specified = false;
+	bool		save_error_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -458,6 +459,13 @@ ProcessCopyOptions(ParseState *pstate,
 			freeze_specified = true;
 			opts_out->freeze = defGetBoolean(defel);
 		}
+		else if (strcmp(defel->defname, "save_error") == 0)
+		{
+			if (save_error_specified)
+				errorConflictingDefElem(defel, pstate);
+			save_error_specified = true;
+			opts_out->save_error = defGetBoolean(defel);
+		}
 		else if (strcmp(defel->defname, "delimiter") == 0)
 		{
 			if (opts_out->delim)
@@ -598,6 +606,10 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify DEFAULT in BINARY mode")));
 
+	if (opts_out->binary && opts_out->save_error)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify SAVE_ERROR in BINARY mode")));
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index f4861652..acd5b623 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -38,6 +38,7 @@
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
 #include "executor/tuptable.h"
+#include "executor/spi.h"
 #include "foreign/fdwapi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
@@ -652,10 +653,12 @@ CopyFrom(CopyFromState cstate)
 	bool		has_before_insert_row_trig;
 	bool		has_instead_insert_row_trig;
 	bool		leafpart_use_multi_insert = false;
+	StringInfo	err_save_buf;
 
 	Assert(cstate->rel);
 	Assert(list_length(cstate->range_table) == 1);
-
+	if (cstate->opts.save_error)
+		Assert(cstate->escontext);
 	/*
 	 * The target must be a plain, foreign, or partitioned relation, or have
 	 * an INSTEAD OF INSERT row trigger.  (Currently, such triggers are only
@@ -952,6 +955,7 @@ CopyFrom(CopyFromState cstate)
 	errcallback.previous = error_context_stack;
 	error_context_stack = &errcallback;
 
+	err_save_buf = makeStringInfo();
 	for (;;)
 	{
 		TupleTableSlot *myslot;
@@ -989,8 +993,54 @@ CopyFrom(CopyFromState cstate)
 		ExecClearTuple(myslot);
 
 		/* Directly store the values/nulls array in the slot */
-		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
+		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull, err_save_buf))
+		{
+			if (cstate->opts.save_error)
+			{
+				Assert(cstate->error_nsp && cstate->error_rel);
+
+				if (cstate->error_rows_cnt > 0)
+				{
+					ereport(NOTICE,
+							errmsg("%ld rows were skipped because of error."
+									" skipped row saved to table %s.%s",
+									cstate->error_rows_cnt,
+									cstate->error_nsp, cstate->error_rel));
+				}
+				else
+				{
+					StringInfoData 	querybuf;
+					if (cstate->error_firsttime)
+					{
+						ereport(NOTICE,
+								errmsg("No error happened."
+										"Error holding table %s.%s will be droped",
+										cstate->error_nsp, cstate->error_rel));
+						initStringInfo(&querybuf);
+						appendStringInfo(&querybuf,
+										"DROP TABLE IF EXISTS %s.%s CASCADE ",
+										cstate->error_nsp, cstate->error_rel);
+
+						if (SPI_connect() != SPI_OK_CONNECT)
+							elog(ERROR, "SPI_connect failed");
+						if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+							elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+						if (SPI_finish() != SPI_OK_FINISH)
+							elog(ERROR, "SPI_finish failed");
+					}
+					else
+						ereport(NOTICE,
+								errmsg("No error happened. "
+										"All the past error holding saved at %s.%s ",
+										cstate->error_nsp, cstate->error_rel));
+				}
+			}
 			break;
+		}
+
+		/* Soft error occured, skip this tuple */
+		if (cstate->opts.save_error && cstate->line_error_occured)
+			continue;

@@ -1444,6 +1494,103 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+	/* Set up soft error handler for SAVE_ERROR */
+	if (cstate->opts.save_error)
+	{
+		char	*err_nsp;
+		char	error_rel[NAMEDATALEN];
+		StringInfoData 	querybuf;
+		bool		isnull;
+		bool		error_table_ok;
+
+		cstate->escontext = makeNode(ErrorSaveContext);
+		cstate->escontext->type = T_ErrorSaveContext;
+		cstate->escontext->details_wanted = true;
+		cstate->escontext->error_occurred = false;
+
+		snprintf(error_rel, sizeof(error_rel), "%s",
+							RelationGetRelationName(cstate->rel));
+		strlcat(error_rel,"_error", NAMEDATALEN);
+		err_nsp = get_namespace_name(RelationGetNamespace(cstate->rel));
+
+		initStringInfo(&querybuf);
+		/* The build query is used to validate:
+		* . err_nsp.error_rel table exists
+		* . column list(order by attnum, begin from ctid) =
+		*	{ctid, lineno,line,field,source,err_message,err_detail,errorcode}
+		* . data types (from attnum = -1) ={tid, int8,text,text,text,text,text,text}
+		* 	We need ctid system column when
+		*		save_error table already exists and have zero column.
+		*
+		*/
+		appendStringInfo(&querybuf,
+						"SELECT (array_agg(pa.attname ORDER BY pa.attnum) "
+							"= '{ctid,lineno,line,field,source,err_message,err_detail,errorcode}') AND "
+							"(array_agg(pt.typname ORDER BY pa.attnum) "
+							"= '{tid,int8,text,text,text,text,text,text}') "
+							"FROM pg_catalog.pg_attribute pa "
+							"JOIN pg_catalog.pg_class	pc ON pc.oid = pa.attrelid "
+							"JOIN pg_catalog.pg_type 	pt ON pt.oid = pa.atttypid "
+							"JOIN pg_catalog.pg_namespace pn "
+							"ON pn.oid = pc.relnamespace WHERE ");
+
+		appendStringInfo(&querybuf,
+							"relname = $$%s$$ AND pn.nspname = $$%s$$ "
+							" AND pa.attnum >= -1 AND NOT attisdropped ",
+							error_rel, err_nsp);
+
+		if (SPI_connect() != SPI_OK_CONNECT)
+			elog(ERROR, "SPI_connect failed");
+
+		if (SPI_execute(querybuf.data, false, 0) != SPI_OK_SELECT)
+			elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+		error_table_ok = DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0],
+									   SPI_tuptable->tupdesc,
+									   1, &isnull));
+
+		/* no err_nsp.error_rel table then crete one. for holding error. */
+		if (isnull)
+		{
+			resetStringInfo(&querybuf);
+			appendStringInfo(&querybuf,
+							"CREATE TABLE %s.%s (LINENO BIGINT, LINE TEXT, "
+							"FIELD TEXT, SOURCE TEXT, ERR_MESSAGE TEXT, "
+							"ERR_DETAIL TEXT, ERRORCODE TEXT)",
+							 err_nsp,error_rel);
+			if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+				elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+			cstate->error_firsttime = true;
+			elog(DEBUG1, "%s.%s created ", err_nsp, error_rel);
+		}
+		else if (error_table_ok)
+			/* error save table already exists. Set error_firsttime to false */
+			cstate->error_firsttime = false;
+		else if(!error_table_ok)
+			ereport(ERROR,
+					(errmsg("Error save table %s.%s already exists. "
+								 "Cannot use it for COPY FROM error saving",
+								 err_nsp, error_rel)));
+
+		if (SPI_finish() != SPI_OK_FINISH)
+			elog(ERROR, "SPI_finish failed");
+
+		/* these info need, no error will drop err_nsp.error_rel table */
+		cstate->error_rel = pstrdup(error_rel);
+		cstate->error_nsp = err_nsp;
+	}
+	else
+	{
+		/* set to NULL */
+		cstate->error_rel = NULL;
+		cstate->error_nsp = NULL;
+		cstate->escontext = NULL;
+	}
+
+	cstate->error_rows_cnt = 0;  		/* set the default to 0 */
+	cstate->line_error_occured = false;	/* default, assume conversion be ok. */
+
 	/* Convert convert_selectively name list to per-column flags */
 	if (cstate->opts.convert_selectively)
 	{
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index f5537345..5b5471af 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -66,10 +66,12 @@
 #include "commands/copyfrom_internal.h"
 #include "commands/progress.h"
 #include "executor/executor.h"
+#include "executor/spi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -852,7 +854,7 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
  */
 bool
 NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
-			 Datum *values, bool *nulls)
+			 Datum *values, bool *nulls, StringInfo err_save_buf)
 {
 	TupleDesc	tupDesc;
 	AttrNumber	num_phys_attrs,
@@ -885,6 +887,11 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
 			return false;
 
+		/* reset to false for next new line if SAVE_ERROR specified */
+		if (cstate->opts.save_error)
+		{
+			cstate->line_error_occured = false;
+		}
 		/* check for overflowing fields */
 		if (attr_count > 0 && fldct > attr_count)
 			ereport(ERROR,
@@ -956,15 +963,87 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
 			}
 			else
-				values[m] = InputFunctionCall(&in_functions[m],
-											  string,
-											  typioparams[m],
-											  att->atttypmod);
+			{
+				/*
+				*
+				* InputFunctionCall is more faster than InputFunctionCallSafe.
+				* So there is two function.
+				*/
+				if(!cstate->opts.save_error)
+				{
+					values[m] = InputFunctionCall(&in_functions[m],
+												string,
+												typioparams[m],
+												att->atttypmod);
+				}
+				else
+				{
+					if (!InputFunctionCallSafe(&in_functions[m],
+											string,
+											typioparams[m],
+											att->atttypmod,
+											(Node *) cstate->escontext,
+											&values[m]))
+					{
+						char	errcode[12];
+						char	*err_detail;
 
+						snprintf(errcode, sizeof(errcode),
+									"%s",
+									unpack_sql_state(cstate->escontext->error_data->sqlerrcode));
+
+						if (!cstate->escontext->error_data->detail)
+							err_detail = NULL;
+						else
+							err_detail = cstate->escontext->error_data->detail;
+
+						resetStringInfo(err_save_buf);
+						/* error table first column is bigint, reset is text.*/
+						appendStringInfo(err_save_buf,
+										"insert into %s.%s(lineno,line,field, "
+										"source, err_message, errorcode,err_detail) "
+										"select $$%ld$$::bigint, $$%s$$, $$%s$$, "
+										"$$%s$$, $$%s$$, $$%s$$, ",
+										cstate->error_nsp, cstate->error_rel,
+										cstate->cur_lineno, cstate->line_buf.data,
+										cstate->cur_attname, string,
+										cstate->escontext->error_data->message,
+										errcode);
+
+						if (!err_detail)
+							appendStringInfo(err_save_buf, "NULL::text");
+						else
+							appendStringInfo(err_save_buf,"$$%s$$", err_detail);
+
+						if (SPI_connect() != SPI_OK_CONNECT)
+							elog(ERROR, "SPI_connect failed");
+						if (SPI_execute(err_save_buf->data, false, 0) != SPI_OK_INSERT)
+							elog(ERROR, "SPI_exec failed: %s", err_save_buf->data);
+						if (SPI_processed != 1)
+							elog(FATAL, "not a singleton result");
+						if (SPI_finish() != SPI_OK_FINISH)
+							elog(ERROR, "SPI_finish failed");
+
+						/* line error occured, set it once per line */
+						if (!cstate->line_error_occured)
+							cstate->line_error_occured = true;
+
+						cstate->escontext->error_occurred = false;
+						cstate->escontext->details_wanted = true;
+						memset(cstate->escontext->error_data,0, sizeof(ErrorData));
+					}
+				}
+			}
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
 
+		/* record error rows count. */
+		if (cstate->line_error_occured)
+		{
+			cstate->error_rows_cnt++;
+			Assert(cstate->opts.save_error);
+		}
 		Assert(fieldno == attr_count);
 	}
 	else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d631ac89..747bd88a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -755,7 +755,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
 	ROUTINE ROUTINES ROW ROWS RULE
 
-	SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
+	SAVE_ERROR SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
 	SEQUENCE SEQUENCES
 	SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
 	SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
@@ -3448,6 +3448,10 @@ copy_opt_item:
 				{
 					$$ = makeDefElem("encoding", (Node *) makeString($2), @1);
 				}
+			| SAVE_ERROR
+				{
+					$$ = makeDefElem("save_error", (Node *) makeBoolean(true), @1);
+				}
 		;
 
 /* The following exist for backward compatibility with very old versions */
@@ -17328,6 +17332,7 @@ unreserved_keyword:
 			| ROUTINES
 			| ROWS
 			| RULE
+			| SAVE_ERROR
 			| SAVEPOINT
 			| SCALAR
 			| SCHEMA
@@ -17936,6 +17941,7 @@ bare_label_keyword:
 			| ROW
 			| ROWS
 			| RULE
+			| SAVE_ERROR
 			| SAVEPOINT
 			| SCALAR
 			| SCHEMA
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 04980118..e6a358e0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2890,7 +2890,8 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
+					  "SAVE_ERROR");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index f2cca0b9..cfed5d7f 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -43,6 +43,7 @@ typedef struct CopyFormatOptions
 	bool		binary;			/* binary format? */
 	bool		freeze;			/* freeze rows on loading? */
 	bool		csv_mode;		/* Comma Separated Value format? */
+	bool		save_error;		/*  save error to another table? */
 	CopyHeaderChoice header_line;	/* header line? */
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
@@ -82,7 +83,7 @@ extern CopyFromState BeginCopyFrom(ParseState *pstate, Relation rel, Node *where
 								   bool is_program, copy_data_source_cb data_source_cb, List *attnamelist, List *options);
 extern void EndCopyFrom(CopyFromState cstate);
 extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
-						 Datum *values, bool *nulls);
+						 Datum *values, bool *nulls, StringInfo err_save_buf);
 extern bool NextCopyFromRawFields(CopyFromState cstate,
 								  char ***fields, int *nfields);
 extern void CopyFromErrorCallback(void *arg);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 5ec41589..b1c02b2f 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@

 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,12 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	ErrorSaveContext *escontext; 	/* soft error trapper during in_functions execution */
+	int64		error_rows_cnt; /* total number of rows that have errors */
+	const char 	*error_rel;		/* the error row save table name */
+	const char 	*error_nsp;		/* the error row table's namespace */
+	bool	 	line_error_occured;	/* does this line conversion error happened */
+	bool	 	error_firsttime;	/* first time create error save table */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa..d0988a4c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -377,6 +377,7 @@ PG_KEYWORD("routines", ROUTINES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("row", ROW, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("save_error", SAVE_ERROR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("scalar", SCALAR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index c4178b9c..0906cc40 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -564,6 +564,113 @@ ERROR:  conflicting or redundant options
 LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
                                                              ^
 ROLLBACK;
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+--- copy success, error save table will be dropped automatically.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+NOTICE:  No error happened.Error holding table public.save_error_csv_error will be droped
+select count(*) as expected_zero from pg_class where relname = 'save_error_csv_error';
+ expected_zero 
+---------------
+             0
+(1 row)
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+ERROR:  cannot specify SAVE_ERROR in BINARY mode
+create table save_error_csv_error();
+--should fail. since error save table already exists.
+--error save table name = copy destination tablename + "_error"
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+ERROR:  Error save table public.save_error_csv_error already exists. Cannot use it for COPY FROM error saving
+DROP TABLE save_error_csv_error;
+BEGIN;
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+NOTICE:  2 rows were skipped because of error. skipped row saved to table public.save_error_csv_error
+SELECT *, b is null as b_null, b = '' as empty FROM save_error_csv;
+ a | b |  c   |  d   | b_null | empty 
+---+---+------+------+--------+-------
+ 2 |   | NULL | NULL | f      | t
+(1 row)
+
+SELECT count(*) as expect_one FROM pg_class WHERE relname = 'save_error_csv_error';
+ expect_one 
+------------
+          1
+(1 row)
+
+ROLLBACK;
+DROP TABLE save_error_csv;
+--error TABLE should already droppped.
+SELECT 1 as expect_zero FROM pg_class WHERE relname = 'save_error_csv_error';
+ expect_zero 
+-------------
+(0 rows)
+
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+NOTICE:  8 rows were skipped because of error. skipped row saved to table public.check_ign_err_error
+--special case. will work,but the error TABLE should not DROP.
+COPY check_ign_err FROM STDIN WITH (save_error, format csv, FORCE_NULL *);
+NOTICE:  No error happened. All the past error holding saved at public.check_ign_err_error 
+--expect error TABLE exists
+SELECT * FROM check_ign_err_error;
+ lineno |                    line                    | field |         source          |                           err_message                           |        err_detail         | errorcode 
+--------+--------------------------------------------+-------+-------------------------+-----------------------------------------------------------------+---------------------------+-----------
+      2 | \n      {1}     1       \-                 | n     |                        +| invalid input syntax for type integer: "                       +| NULL                      | 22P02
+        |                                            |       |                         | "                                                               |                           | 
+      3 | a       {2}     2       \r                 | n     | a                       | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+      4 | 3       {\3}    3333333333      \n         | m     | {\x03}                  | invalid input syntax for type integer: "\x03"                   | NULL                      | 22P02
+      5 | 0x11    {3,}    3333333333      \\.        | m     | {3,}                    | malformed array literal: "{3,}"                                 | Unexpected "}" character. | 22P02
+      6 | d       {3,1/}  3333333333      \\0        | n     | d                       | invalid input syntax for type integer: "d"                      | NULL                      | 22P02
+      6 | d       {3,1/}  3333333333      \\0        | m     | {3,1/}                  | invalid input syntax for type integer: "1/"                     | NULL                      | 22P02
+      7 | e       {3,\1}  -3323879289873933333333 \n | n     | e                       | invalid input syntax for type integer: "e"                      | NULL                      | 22P02
+      7 | e       {3,\1}  -3323879289873933333333 \n | m     | {3,\x01}                | invalid input syntax for type integer: "\x01"                   | NULL                      | 22P02
+      7 | e       {3,\1}  -3323879289873933333333 \n | k     | -3323879289873933333333 | value "-3323879289873933333333" is out of range for type bigint | NULL                      | 22003
+      8 | f       {3,1}   3323879289873933333333  \r | n     | f                       | invalid input syntax for type integer: "f"                      | NULL                      | 22P02
+      8 | f       {3,1}   3323879289873933333333  \r | k     | 3323879289873933333333  | value "3323879289873933333333" is out of range for type bigint  | NULL                      | 22003
+      9 | b       {a, 4}  1.1     h                  | n     | b                       | invalid input syntax for type integer: "b"                      | NULL                      | 22P02
+      9 | b       {a, 4}  1.1     h                  | m     | {a, 4}                  | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+      9 | b       {a, 4}  1.1     h                  | k     | 1.1                     | invalid input syntax for type bigint: "1.1"                     | NULL                      | 22P02
+(14 rows)
+
+-- redundant options not allowed.
+COPY check_ign_err FROM STDIN WITH (save_error, save_error off);
+ERROR:  conflicting or redundant options
+LINE 1: COPY check_ign_err FROM STDIN WITH (save_error, save_error o...
+                                                        ^
+DROP TABLE check_ign_err CASCADE;
+DROP TABLE IF EXISTS check_ign_err_error CASCADE;
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+CREATE TABLE textrange_input(a textrange, b textrange, c textrange);
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  4 rows were skipped because of error. skipped row saved to table public.textrange_input_error
+SELECT * FROM textrange_input_error;
+ lineno |            line            | field |  source  |                            err_message                            |                err_detail                | errorcode 
+--------+----------------------------+-------+----------+-------------------------------------------------------------------+------------------------------------------+-----------
+      1 | ,-[a\","z),[a","-inf)      | b     | -[a\,z)  | malformed range literal: "-[a\,z)"                                | Missing left parenthesis or bracket.     | 22P02
+      1 | ,-[a\","z),[a","-inf)      | c     | [a,-inf) | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+      2 | (",a),(",",a),()",a)       | a     | (,a),(   | malformed range literal: "(,a),("                                 | Junk after right parenthesis or bracket. | 22P02
+      2 | (",a),(",",a),()",a)       | b     | ,a),()   | malformed range literal: ",a),()"                                 | Missing left parenthesis or bracket.     | 22P02
+      2 | (",a),(",",a),()",a)       | c     | a)       | malformed range literal: "a)"                                     | Missing left parenthesis or bracket.     | 22P02
+      3 | (a",")),(]","a),(a","])    | a     | (a,))    | malformed range literal: "(a,))"                                  | Junk after right parenthesis or bracket. | 22P02
+      3 | (a",")),(]","a),(a","])    | b     | (],a)    | malformed range literal: "(],a)"                                  | Missing comma after lower bound.         | 22P02
+      3 | (a",")),(]","a),(a","])    | c     | (a,])    | malformed range literal: "(a,])"                                  | Junk after right parenthesis or bracket. | 22P02
+      4 | [z","a],[z","2],[(","",")] | a     | [z,a]    | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+      4 | [z","a],[z","2],[(","",")] | b     | [z,2]    | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+      4 | [z","a],[z","2],[(","",")] | c     | [(,",)]  | malformed range literal: "[(,",)]"                                | Unexpected end of input.                 | 22P02
+(11 rows)
+
+DROP TABLE textrange_input;
+DROP TABLE textrange_input_error;
 \pset null ''
 -- test case with whole-row Var in a check constraint
 create table check_con_tbl (f1 int);
@@ -822,3 +929,28 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT only available using COPY FROM
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+NOTICE:  3 rows were skipped because of error. skipped row saved to table public.copy_default_error_save_error
+select count(*) as expect_zero from copy_default_error_save;
+ expect_zero 
+-------------
+           0
+(1 row)
+
+select  * from copy_default_error_save_error;
+ lineno |               line               |  field   |      source      |                         err_message                         | err_detail | errorcode 
+--------+----------------------------------+----------+------------------+-------------------------------------------------------------+------------+-----------
+      1 | k       value   '2022-07-04'     | id       | k                | invalid input syntax for type integer: "k"                  |            | 22P02
+      2 | z       \D      '2022-07-03ASKL' | id       | z                | invalid input syntax for type integer: "z"                  |            | 22P02
+      2 | z       \D      '2022-07-03ASKL' | ts_value | '2022-07-03ASKL' | invalid input syntax for type timestamp: "'2022-07-03ASKL'" |            | 22007
+      3 | s       \D      \D               | id       | s                | invalid input syntax for type integer: "s"                  |            | 22P02
+(4 rows)
+
+drop table copy_default_error_save_error,copy_default_error_save;
+truncate copy_default;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index a5486f60..3f8137cf 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -374,6 +374,89 @@ BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
 ROLLBACK;
 
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+
+--- copy success, error save table will be dropped automatically.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+\.
+
+select count(*) as expected_zero from pg_class where relname = 'save_error_csv_error';
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+create table save_error_csv_error();
+--should fail. since error save table already exists.
+--error save table name = copy destination tablename + "_error"
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+
+DROP TABLE save_error_csv_error;
+
+BEGIN;
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+z,,""
+\0,,
+2,,
+\.
+
+SELECT *, b is null as b_null, b = '' as empty FROM save_error_csv;
+SELECT count(*) as expect_one FROM pg_class WHERE relname = 'save_error_csv_error';
+ROLLBACK;
+
+DROP TABLE save_error_csv;
+
+--error TABLE should already droppped.
+SELECT 1 as expect_zero FROM pg_class WHERE relname = 'save_error_csv_error';
+
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+1	{1}	1	1
+\n	{1}	1	\-
+a	{2}	2	\r
+3	{\3}	3333333333	\n
+0x11	{3,}	3333333333	\\.
+d	{3,1/}	3333333333	\\0
+e	{3,\1}	-3323879289873933333333	\n
+f	{3,1}	3323879289873933333333	\r
+b	{a, 4}	1.1	h
+5	{5}	5	\\
+\.
+
+--special case. will work,but the error TABLE should not DROP.
+COPY check_ign_err FROM STDIN WITH (save_error, format csv, FORCE_NULL *);
+,,,
+\.
+
+--expect error TABLE exists
+SELECT * FROM check_ign_err_error;
+
+-- redundant options not allowed.
+COPY check_ign_err FROM STDIN WITH (save_error, save_error off);
+
+DROP TABLE check_ign_err CASCADE;
+DROP TABLE IF EXISTS check_ign_err_error CASCADE;
+
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+CREATE TABLE textrange_input(a textrange, b textrange, c textrange);
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+,-[a\","z),[a","-inf)
+(",a),(",",a),()",a)
+(a",")),(]","a),(a","])
+[z","a],[z","2],[(","",")]
+\.
+
+SELECT * FROM textrange_input_error;
+DROP TABLE textrange_input;
+DROP TABLE textrange_input_error;
+
 \pset null ''
 
 -- test case with whole-row Var in a check constraint
@@ -609,3 +692,19 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+k	value	'2022-07-04'
+z	\D	'2022-07-03ASKL'
+s	\D	\D
+\.
+select count(*) as expect_zero from copy_default_error_save;
+select  * from copy_default_error_save_error;
+drop table copy_default_error_save_error,copy_default_error_save;
+truncate copy_default;
\ No newline at end of file
-- 
2.34.1

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 5 Dec 2023 13:07:24 +0300

Hi!

Thank you for your contribution to this thread.

> I feel it's hard from textual '\D'  to get text[] `(11,test)` via SPI.
I reviewed it and have a few questions.

1. I have seen that you delete a table before creating it, to which you 
want to add errors due to a failed "copy from" operation. I think this 
is wrong because this table can save useful data for the user.
At a minimum, we should warn the user about this, but I think we can 
just add some number at the end of the name, such as name_table1, 
name_table_2.

2. I noticed that you are forming a table name using the type of errors 
that prevent rows from being added during 'copy from' operation.
I think it would be better to use the name of the source file that was 
used while 'copy from' was running.
In addition, there may be several such files, it is also worth considering.

3. I found spelling:

/* no err_nsp.error_rel table then crete one. for holding error. */

4. Maybe rewrite this comment

these info need, no error will drop err_nsp.error_rel table
to:
this information is necessary, no error will lead to the deletion of the 
err_sp.error_rel table.

5. Is this part of the comment needed? I think it duplicates the 
information below when we form the query.

  * . column list(order by attnum, begin from ctid) =
  *    {ctid, lineno,line,field,source,err_message,err_detail,errorcode}
  * . data types (from attnum = -1) ={tid, 
int8,text,text,text,text,text,text}

I'm not sure if we need to order the rows by number. It might be easier 
to work with these lines in the order they appear.

-- 
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

---

From: jian he <[email-masked]>
Date: Wed, 6 Dec 2023 18:47:10 +0800

On Tue, Dec 5, 2023 at 6:07 PM Alena Rybakina <[email-masked]> wrote:
> 1. I have seen that you delete a table before creating it, to which you want to add errors due to a failed "copy from" operation. I think this is wrong because this table can save useful data for the user.
> At a minimum, we should warn the user about this, but I think we can just add some number at the end of the name, such as name_table1, name_table_2.

Sorry. I don't understand this part.
Currently, if the error table name already exists, then the copy will
fail, an error will be reported.
I try to first create a table, if no error then the error table will be dropped.
Can you demo the expected behavior?

> 2. I noticed that you are forming a table name using the type of errors that prevent rows from being added during 'copy from' operation.
> I think it would be better to use the name of the source file that was used while 'copy from' was running.

Another column added.
now it looks like:

SELECT * FROM save_error_csv_error;
 filename | lineno |                        line
 | field | source |                 err_message                 |
err_detail | errorcode
----------+--------+----------------------------------------------------+-------+--------+---------------------------------------------+------------+-----------
 STDIN    |      1 | 2002    232     40      50      60      70
80 | NULL  | NULL   | extra data after last expected column       |
NULL       | 22P04
 STDIN    |      1 | 2000    230     23
 | d     | NULL   | missing data for column "d"                 | NULL
      | 22P04
 STDIN    |      1 | z,,""
 | a     | z      | invalid input syntax for type integer: "z"  | NULL
      | 22P02
 STDIN    |      2 | \0,,
 | a     | \0     | invalid input syntax for type integer: "\0" | NULL
      | 22P02

> /* no err_nsp.error_rel table then crete one. for holding error. */

fixed.

> this information is necessary, no error will lead to the deletion of the err_sp.error_rel table.
>

fixed.

> 5. Is this part of the comment needed? I think it duplicates the information below when we form the query.
>
>  * . data types (from attnum = -1) ={tid, int8,text,text,text,text,text,text}
>
> I'm not sure if we need to order the rows by number. It might be easier to work with these lines in the order they appear.
>
Simplified the comment. "order by attnum" is to make sure that if
there is a table already existing, and the column name is like X and
the data type like Y, then we consider this table is good for holding
potential error info.

COPY FROM, main entry point is NextCopyFrom.
Now for non-binary mode, if you specified save_error then it will not
fail at NextCopyFrom.
all these three errors will be tolerated: extra data after last
expected column, missing data for column, data type conversion.
From 990e1e0f5130431cf32069963bb980bb0692ce0b Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Wed, 6 Dec 2023 18:26:32 +0800
Subject: [PATCH v9 1/1] Make COPY FROM more error tolerant

Currently COPY FROM has 3 types of error while processing the source file.
* extra data after last expected column
* missing data for column \"%s\"
* data type conversion error.

Instead of throwing errors while copying, save_error will save errors to a table automatically.
We check the table definition via column name and column data type.
if table already exists and meets the criteria then errors will save to that table.
if the table does not exist, then create one.

Only works for COPY FROM, non-BINARY mode.

While copying, if error never happened, error save table will be dropped at the ending of COPY FROM.
If the error saving table already exists, meaning at least once COPY FROM errors has happened,
then all the future errors will be saved to that table.
we save the error to error saving table using SPI, construct a query, then execute the query.
---
 contrib/file_fdw/file_fdw.c              |   4 +-
 doc/src/sgml/ref/copy.sgml               |  93 ++++++++++++
 src/backend/commands/copy.c              |  12 ++
 src/backend/commands/copyfrom.c          | 147 ++++++++++++++++++-
 src/backend/commands/copyfromparse.c     | 171 +++++++++++++++++++++--
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   3 +-
 src/include/commands/copyfrom_internal.h |   7 +
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 135 ++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 108 ++++++++++++++
 12 files changed, 673 insertions(+), 19 deletions(-)

 

index 18ecc69c..a6370c42 100644

+   <varlistentry>
+    <term><literal>SAVE_ERROR</literal></term>
+    <listitem>
+     <para>
+      Specifies that any data conversion failure while copying will automatically report error information to a regular table.
+      This option is not allowed when using <literal>binary</literal> format. Note that this
+      is only supported in current <command>COPY FROM</command> syntax.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
@@ -572,6 +584,12 @@ COPY <replaceable class="parameter">count</replaceable>
     null strings to null values and unquoted null strings to empty strings.
    </para>
 
+   <para>
+    If the <literal>SAVE_ERROR</literal> option is spceified and a conversion error occurs while copying, then
+     <productname>PostgreSQL</productname> will create a table to save all the conversion errors. Conversion error
+     include data type conversion failure, extra data or missing data in the source file.
+   </para>
+
  </refsect1>
 
  <refsect1>
@@ -962,6 +980,81 @@ versions of <productname>PostgreSQL</productname>.
      check against somehow getting out of sync with the data.
     </para>
    </refsect3>
+
+   <refsect3>
+    <title>Error Save Table </title>
+    <para>
+        If <literal>SAVE_ERROR</literal> spceicfied, all the data type conversion fail while copying will automatically saved in a regular table.
+        <xref linkend="copy-errorsave-table"/> shows the error save table name, data type, and description.
+    </para>
+
+   <table id="copy-errorsave-table">
+
+    <title>COPY ERROR SAVE TABLE </title>
+
+    <tgroup cols="3">
+     <thead>
+      <row>
+       <entry>Column name</entry>
+       <entry>Data type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+
+      <tbody>
+       <row>
+       <entry> <literal>filename</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The path name of the input file</entry>
+       </row>
+
+       <row>
+       <entry> <literal>lineno</literal> </entry>
+       <entry><type>bigint</type></entry>
+       <entry>Line number where error occurred, counting from 1</entry>
+       </row>
+
+       <row>
+       <entry> <literal>line</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of error occuring line</entry>
+       </row>
+
+       <row>
+       <entry> <literal>field</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Field name of the error occuring</entry>
+       </row>
+
+       <row>
+       <entry> <literal>source</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of the error occuring field</entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_message </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error message text </entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_detail</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Detailed error message </entry>
+       </row>
+
+       <row>
+       <entry> <literal>errorcode </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error code for the copying error</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </table>
+   </refsect3>
+
   </refsect2>
  </refsect1>

 
index f4861652..ee6f2664 100644
 
 	Assert(cstate->rel);

 
 
@@ -1444,6 +1494,99 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+		* Does err_nsp.error_rel table exist?
+		* if err_nsp.error_rel exists, does it meet our criteria?
+		* our criteria of error table is based on column name and data types.
+							"= '{ctid,filename,lineno,line,field,source,err_message,err_detail,errorcode}') AND "
+							"= '{tid,text,int8,text,text,text,text,text,text}') "
+		/* No err_nsp.error_rel table then create it for holding error. */
+							"CREATE TABLE %s.%s (FILENAME TEXT, LINENO BIGINT, LINE TEXT, "
+		/* thses information is necessary, no error then drop err_sp.error_rel table*/
index f5537345..d7ddf64c 100644
@@ -885,11 +887,48 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
+		/* reset to false for next new line if SAVE_ERROR specified */
+		if (cstate->line_error_occured)
+			cstate->line_error_occured = false;
+
 		/* check for overflowing fields */
 		if (attr_count > 0 && fldct > attr_count)
-			ereport(ERROR,
-					(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-					 errmsg("extra data after last expected column")));
+		{
+			if(cstate->opts.save_error)
+			{
+				char	*errmsg_extra = "extra data after last expected column";
+
+				resetStringInfo(err_save_buf);
+				/* add line buf, etc for line have extra data to error save table*/
+				appendStringInfo(err_save_buf,
+								"insert into %s.%s(filename, lineno,line, "
+								"err_message, errorcode) "
+								"select $$%s$$, $$%ld$$::bigint, $$%s$$, $$%s$$, "
+								"$$%s$$",
+								cstate->error_nsp, cstate->error_rel,
+								cstate->filename ? cstate->filename : "STDIN",
+								cstate->cur_lineno, cstate->line_buf.data,
+								errmsg_extra,
+								unpack_sql_state(ERRCODE_BAD_COPY_FILE_FORMAT));
+
+				if (SPI_connect() != SPI_OK_CONNECT)
+					elog(ERROR, "SPI_connect failed");
+
+				if (SPI_execute(err_save_buf->data, false, 0) != SPI_OK_INSERT)
+					elog(ERROR, "SPI_exec failed: %s", err_save_buf->data);
+
+				if (SPI_finish() != SPI_OK_FINISH)
+					elog(ERROR, "SPI_finish failed");
+
+				cstate->line_error_occured = true;
+				cstate->error_rows_cnt++;
+				return true;
+			}
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						errmsg("extra data after last expected column")));
+		}
 
 		fieldno = 0;
 
@@ -901,10 +940,46 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 			Form_pg_attribute att = TupleDescAttr(tupDesc, m);
 
 			if (fieldno >= fldct)
-				ereport(ERROR,
-						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-						 errmsg("missing data for column \"%s\"",
-								NameStr(att->attname))));
+			{
+				if(cstate->opts.save_error)
+				{
+					char	errmsg[128];
+					snprintf(errmsg, sizeof(errmsg),
+							"missing data for column \"%s\"",
+							NameStr(att->attname));
+
+					resetStringInfo(err_save_buf);
+					appendStringInfo(err_save_buf,
+									"insert into %s.%s(filename,lineno,line, field, "
+									"err_message, errorcode) "
+									"select $$%s$$, $$%ld$$::bigint, $$%s$$, $$%s$$, "
+									"$$%s$$, $$%s$$ ",
+									cstate->error_nsp, cstate->error_rel,
+									cstate->filename ? cstate->filename : "STDIN",
+									cstate->cur_lineno, cstate->line_buf.data,
+									NameStr(att->attname), errmsg,
+									unpack_sql_state(ERRCODE_BAD_COPY_FILE_FORMAT));
+
+					if (SPI_connect() != SPI_OK_CONNECT)
+						elog(ERROR, "SPI_connect failed");
+
+					if (SPI_execute(err_save_buf->data, false, 0) != SPI_OK_INSERT)
+						elog(ERROR, "SPI_exec failed: %s", err_save_buf->data);
+
+					if (SPI_finish() != SPI_OK_FINISH)
+						elog(ERROR, "SPI_finish failed");
+
+					cstate->line_error_occured = true;
+					cstate->error_rows_cnt++;
+					return true;
+				}
+				else
+					ereport(ERROR,
+							(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+							errmsg("missing data for column \"%s\"",
+									NameStr(att->attname))));
+			}
+
 			string = field_strings[fieldno++];
 
 			if (cstate->convert_select_flags &&
@@ -956,15 +1031,87 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
+										"insert into %s.%s(filename, lineno,line,field, "
+										"select $$%s$$, $$%ld$$::bigint, $$%s$$, $$%s$$, "
+										cstate->filename ? cstate->filename : "STDIN",

 
 
index f2cca0b9..de47791a 100644
+	bool		save_error;		/*  save error to a table? */

 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,12 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	ErrorSaveContext *escontext; 	/* soft error trapper during in_functions execution */
+	int64		error_rows_cnt; /* total number of rows that have errors */
+	const char 	*error_rel;		/* the error row save table name */
+	const char 	*error_nsp;		/* the error row table's namespace */
+	bool	 	line_error_occured;	/* does this line conversion error happened */
+	bool	 	error_firsttime;	/* first time create error save table */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa..d0988a4c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -377,6 +377,7 @@ PG_KEYWORD("routines", ROUTINES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("row", ROW, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("save_error", SAVE_ERROR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("scalar", SCALAR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index c4178b9c..bb86bb9f 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -564,6 +564,116 @@ ERROR:  conflicting or redundant options
 LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
                                                              ^
 ROLLBACK;
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+--- copy success, error save table will be dropped automatically.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+NOTICE:  No error happened.Error holding table public.save_error_csv_error will be droped
+--error TABLE should already droppped.
+select count(*) as expected_zero from pg_class where relname = 'save_error_csv_error';
+ expected_zero 
+---------------
+             0
+(1 row)
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+ERROR:  cannot specify SAVE_ERROR in BINARY mode
+create table save_error_csv_error();
+--should fail. since table save_error_csv_error) already exists.
+--error save table naming logic = copy destination tablename + "_error"
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+ERROR:  Error save table public.save_error_csv_error already exists. Cannot use it for COPY FROM error saving
+DROP TABLE save_error_csv_error;
+-- save error with extra data
+COPY save_error_csv from stdin(save_error);
+NOTICE:  1 rows were skipped because of error. skipped row saved to table public.save_error_csv_error
+-- save error with missing data for column
+COPY save_error_csv from stdin(save_error);
+NOTICE:  1 rows were skipped because of error. skipped row saved to table public.save_error_csv_error
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+NOTICE:  2 rows were skipped because of error. skipped row saved to table public.save_error_csv_error
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+ a | b |  c   |  d   | b_null | b_empty 
+---+---+------+------+--------+---------
+ 2 |   | NULL | NULL | f      | t
+(1 row)
+
+SELECT * FROM save_error_csv_error;
+ filename | lineno |                        line                        | field | source |                 err_message                 | err_detail | errorcode 
+----------+--------+----------------------------------------------------+-------+--------+---------------------------------------------+------------+-----------
+ STDIN    |      1 | 2002    232     40      50      60      70      80 | NULL  | NULL   | extra data after last expected column       | NULL       | 22P04
+ STDIN    |      1 | 2000    230     23                                 | d     | NULL   | missing data for column "d"                 | NULL       | 22P04
+ STDIN    |      1 | z,,""                                              | a     | z      | invalid input syntax for type integer: "z"  | NULL       | 22P02
+ STDIN    |      2 | \0,,                                               | a     | \0     | invalid input syntax for type integer: "\0" | NULL       | 22P02
+(4 rows)
+
+DROP TABLE save_error_csv, save_error_csv_error;
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+NOTICE:  8 rows were skipped because of error. skipped row saved to table public.check_ign_err_error
+--special case. will work,but the error TABLE should not DROP.
+COPY check_ign_err FROM STDIN WITH (save_error, format csv, FORCE_NULL *);
+NOTICE:  No error happened. All the past error holding saved at public.check_ign_err_error 
+--expect error TABLE exists
+SELECT * FROM check_ign_err_error;
+ filename | lineno |                    line                    | field |         source          |                           err_message                           |        err_detail         | errorcode 
+----------+--------+--------------------------------------------+-------+-------------------------+-----------------------------------------------------------------+---------------------------+-----------
+ STDIN    |      2 | \n      {1}     1       \-                 | n     |                        +| invalid input syntax for type integer: "                       +| NULL                      | 22P02
+          |        |                                            |       |                         | "                                                               |                           | 
+ STDIN    |      3 | a       {2}     2       \r                 | n     | a                       | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ STDIN    |      4 | 3       {\3}    3333333333      \n         | m     | {\x03}                  | invalid input syntax for type integer: "\x03"                   | NULL                      | 22P02
+ STDIN    |      5 | 0x11    {3,}    3333333333      \\.        | m     | {3,}                    | malformed array literal: "{3,}"                                 | Unexpected "}" character. | 22P02
+ STDIN    |      6 | d       {3,1/}  3333333333      \\0        | n     | d                       | invalid input syntax for type integer: "d"                      | NULL                      | 22P02
+ STDIN    |      6 | d       {3,1/}  3333333333      \\0        | m     | {3,1/}                  | invalid input syntax for type integer: "1/"                     | NULL                      | 22P02
+ STDIN    |      7 | e       {3,\1}  -3323879289873933333333 \n | n     | e                       | invalid input syntax for type integer: "e"                      | NULL                      | 22P02
+ STDIN    |      7 | e       {3,\1}  -3323879289873933333333 \n | m     | {3,\x01}                | invalid input syntax for type integer: "\x01"                   | NULL                      | 22P02
+ STDIN    |      7 | e       {3,\1}  -3323879289873933333333 \n | k     | -3323879289873933333333 | value "-3323879289873933333333" is out of range for type bigint | NULL                      | 22003
+ STDIN    |      8 | f       {3,1}   3323879289873933333333  \r | n     | f                       | invalid input syntax for type integer: "f"                      | NULL                      | 22P02
+ STDIN    |      8 | f       {3,1}   3323879289873933333333  \r | k     | 3323879289873933333333  | value "3323879289873933333333" is out of range for type bigint  | NULL                      | 22003
+ STDIN    |      9 | b       {a, 4}  1.1     h                  | n     | b                       | invalid input syntax for type integer: "b"                      | NULL                      | 22P02
+ STDIN    |      9 | b       {a, 4}  1.1     h                  | m     | {a, 4}                  | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ STDIN    |      9 | b       {a, 4}  1.1     h                  | k     | 1.1                     | invalid input syntax for type bigint: "1.1"                     | NULL                      | 22P02
+(14 rows)
+
+-- redundant options not allowed.
+COPY check_ign_err FROM STDIN WITH (save_error, save_error off);
+ERROR:  conflicting or redundant options
+LINE 1: COPY check_ign_err FROM STDIN WITH (save_error, save_error o...
+                                                        ^
+DROP TABLE check_ign_err CASCADE;
+DROP TABLE IF EXISTS check_ign_err_error CASCADE;
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+CREATE TABLE textrange_input(a textrange, b textrange, c textrange);
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  4 rows were skipped because of error. skipped row saved to table public.textrange_input_error
+SELECT * FROM textrange_input_error;
+ filename | lineno |            line            | field |  source  |                            err_message                            |                err_detail                | errorcode 
+----------+--------+----------------------------+-------+----------+-------------------------------------------------------------------+------------------------------------------+-----------
+ STDIN    |      1 | ,-[a\","z),[a","-inf)      | b     | -[a\,z)  | malformed range literal: "-[a\,z)"                                | Missing left parenthesis or bracket.     | 22P02
+ STDIN    |      1 | ,-[a\","z),[a","-inf)      | c     | [a,-inf) | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ STDIN    |      2 | (",a),(",",a),()",a)       | a     | (,a),(   | malformed range literal: "(,a),("                                 | Junk after right parenthesis or bracket. | 22P02
+ STDIN    |      2 | (",a),(",",a),()",a)       | b     | ,a),()   | malformed range literal: ",a),()"                                 | Missing left parenthesis or bracket.     | 22P02
+ STDIN    |      2 | (",a),(",",a),()",a)       | c     | a)       | malformed range literal: "a)"                                     | Missing left parenthesis or bracket.     | 22P02
+ STDIN    |      3 | (a",")),(]","a),(a","])    | a     | (a,))    | malformed range literal: "(a,))"                                  | Junk after right parenthesis or bracket. | 22P02
+ STDIN    |      3 | (a",")),(]","a),(a","])    | b     | (],a)    | malformed range literal: "(],a)"                                  | Missing comma after lower bound.         | 22P02
+ STDIN    |      3 | (a",")),(]","a),(a","])    | c     | (a,])    | malformed range literal: "(a,])"                                  | Junk after right parenthesis or bracket. | 22P02
+ STDIN    |      4 | [z","a],[z","2],[(","",")] | a     | [z,a]    | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ STDIN    |      4 | [z","a],[z","2],[(","",")] | b     | [z,2]    | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ STDIN    |      4 | [z","a],[z","2],[(","",")] | c     | [(,",)]  | malformed range literal: "[(,",)]"                                | Unexpected end of input.                 | 22P02
+(11 rows)
+
+DROP TABLE textrange_input;
+DROP TABLE textrange_input_error;
 \pset null ''
 -- test case with whole-row Var in a check constraint
 create table check_con_tbl (f1 int);
@@ -822,3 +932,28 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT only available using COPY FROM
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+NOTICE:  3 rows were skipped because of error. skipped row saved to table public.copy_default_error_save_error
+select count(*) as expect_zero from copy_default_error_save;
+ expect_zero 
+-------------
+           0
+(1 row)
+
+select  * from copy_default_error_save_error;
+ filename | lineno |               line               |  field   |      source      |                         err_message                         | err_detail | errorcode 
+----------+--------+----------------------------------+----------+------------------+-------------------------------------------------------------+------------+-----------
+ STDIN    |      1 | k       value   '2022-07-04'     | id       | k                | invalid input syntax for type integer: "k"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | id       | z                | invalid input syntax for type integer: "z"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | ts_value | '2022-07-03ASKL' | invalid input syntax for type timestamp: "'2022-07-03ASKL'" |            | 22007
+ STDIN    |      3 | s       \D      \D               | id       | s                | invalid input syntax for type integer: "s"                  |            | 22P02
+(4 rows)
+
+drop table copy_default_error_save_error,copy_default_error_save;
+truncate copy_default;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index a5486f60..8c8d8adb 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -374,6 +374,98 @@ BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
 ROLLBACK;
 
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+
+--- copy success, error save table will be dropped automatically.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+\.
+
+--error TABLE should already droppped.
+select count(*) as expected_zero from pg_class where relname = 'save_error_csv_error';
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+create table save_error_csv_error();
+--should fail. since table save_error_csv_error) already exists.
+--error save table naming logic = copy destination tablename + "_error"
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+
+DROP TABLE save_error_csv_error;
+
+-- save error with extra data
+COPY save_error_csv from stdin(save_error);
+2002	232	40	50	60	70	80
+\.
+
+-- save error with missing data for column
+COPY save_error_csv from stdin(save_error);
+2000	230	23
+\.
+
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+z,,""
+\0,,
+2,,
+\.
+
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+
+SELECT * FROM save_error_csv_error;
+
+DROP TABLE save_error_csv, save_error_csv_error;
+
+
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+1	{1}	1	1
+\n	{1}	1	\-
+a	{2}	2	\r
+3	{\3}	3333333333	\n
+0x11	{3,}	3333333333	\\.
+d	{3,1/}	3333333333	\\0
+e	{3,\1}	-3323879289873933333333	\n
+f	{3,1}	3323879289873933333333	\r
+b	{a, 4}	1.1	h
+5	{5}	5	\\
+\.
+
+--special case. will work,but the error TABLE should not DROP.
+COPY check_ign_err FROM STDIN WITH (save_error, format csv, FORCE_NULL *);
+,,,
+\.
+
+--expect error TABLE exists
+SELECT * FROM check_ign_err_error;
+
+-- redundant options not allowed.
+COPY check_ign_err FROM STDIN WITH (save_error, save_error off);
+
+DROP TABLE check_ign_err CASCADE;
+DROP TABLE IF EXISTS check_ign_err_error CASCADE;
+
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+CREATE TABLE textrange_input(a textrange, b textrange, c textrange);
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+,-[a\","z),[a","-inf)
+(",a),(",",a),()",a)
+(a",")),(]","a),(a","])
+[z","a],[z","2],[(","",")]
+\.
+
+SELECT * FROM textrange_input_error;
+DROP TABLE textrange_input;
+DROP TABLE textrange_input_error;
+
 \pset null ''
 
 -- test case with whole-row Var in a check constraint
@@ -609,3 +701,19 @@ truncate copy_default;

---

From: Alena Rybakina <[email-masked]>
Date: Fri, 8 Dec 2023 10:09:07 +0300

Thank you for your work. Unfortunately, your code contained errors 
during the make installation:

'SAVEPOINT' after 'SAVE_ERROR' in unreserved_keyword list is misplaced
'SAVEPOINT' after 'SAVE_ERROR' in bare_label_keyword list is misplaced
make[2]: *** [../../../src/Makefile.global:783: gram.c] Error 1
make[1]: *** [Makefile:131: parser/gram.h] Error 2
make[1]: *** Waiting for unfinished jobs....
make: *** [src/Makefile.global:383: submake-generated-headers] Error 2

I have ubuntu 22.04 operation system.

On 06.12.2023 13:47, jian he wrote:
> On Tue, Dec 5, 2023 at 6:07 PM Alena Rybakina<[email-masked]>  wrote:
>> Hi!
>>
To be honest, first of all, I misunderstood this part of the code. Now I 
see that it works the way you mentioned.

However, I didn't see if you dealt with cases where we already had a 
table with the same name as the table error.
I mean, when is he trying to create for the first time, or will we never 
be able to face such a problem?
> Can you demo the expected behavior?
Unfortunately, I was unable to launch it due to a build issue.
>   | field | source |                 err_message                 |
Yes, I see the "filename" column, and this will solve the problem, but 
"STDIN" is unclear to me.
>> this information is necessary, no error will lead to the deletion of the err_sp.error_rel table.
Thank you.
> expected column, missing data for column, data type conversion.
It looks clearer and better, thanks!

Comments in the format of questions are unusual for me, I perceive them 
to think about it, for example, as here (contrib/bloom/blinsert.c:312):

/*

  * Didn't find place to insert in notFullPage array.  Allocate new page.
  * (XXX is it good to do this while holding ex-lock on the metapage??)
  */

Maybe we can rewrite it like this:

/* Check, the err_nsp.error_rel table has already existed
* and if it is, check its column name and data types.

---

From: jian he <[email-masked]>
Date: Sun, 10 Dec 2023 18:32:12 +0800

On Fri, Dec 8, 2023 at 3:09 PM Alena Rybakina <[email-masked]> wrote:
>
> Thank you for your work. Unfortunately, your code contained errors during the make installation:
>
> To be honest, first of all, I misunderstood this part of the code. Now I see that it works the way you mentioned.
>
> However, I didn't see if you dealt with cases where we already had a table with the same name as the table error.
> I mean, when is he trying to create for the first time, or will we never be able to face such a problem?
>

Hopefully attached will work.

> Yes, I see the "filename" column, and this will solve the problem, but "STDIN" is unclear to me.

please see comment in struct CopyFromStateData:
char    *filename; /* filename, or NULL for STDIN */

> * and if it is, check its column name and data types.
refactored.

---

From: Alena Rybakina <[email-masked]>
Date: Mon, 11 Dec 2023 17:05:33 +0300

Hi! Thank you for your work. Your patch looks better!

On 10.12.2023 13:32, jian he wrote:
> On Fri, Dec 8, 2023 at 3:09 PM Alena Rybakina<[email-masked]>  wrote:

Yes, thank you! It works fine, and I see that the regression tests have 
been passed. 🙂

However, when I ran 'copy from with save_error' operation with simple 
csv files (copy_test.csv, copy_test1.csv) for tables test, test1 (how I 
created it, I described below):

postgres=# create table test (x int primary key, y int not null);
postgres=# create table test1 (x int, z int, CONSTRAINT fk_x
       FOREIGN KEY(x)
           REFERENCES test(x));

I did not find a table with saved errors after operation, although I 
received a log about it:

postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV 
save_error
NOTICE:  2 rows were skipped because of error. skipped row saved to 
table public.test_error
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (x)=(2) already exists.
CONTEXT:  COPY test, line 3

postgres=# select * from public.test_error;
ERROR:  relation "public.test_error" does not exist
LINE 1: select * from public.test_error;

postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' 
CSV save_error
NOTICE:  2 rows were skipped because of error. skipped row saved to 
table public.test1_error
ERROR:  insert or update on table "test1" violates foreign key 
constraint "fk_x"
DETAIL:  Key (x)=(2) is not present in table "test".

postgres=# select * from public.test1_error;
ERROR:  relation "public.test1_error" does not exist
LINE 1: select * from public.test1_error;

Two lines were written correctly in the csv files, therefore they should 
have been added to the tables, but they were not added to the tables 
test and test1.

If I leave only the correct rows, everything works fine and the rows are 
added to the tables.

in copy_test.csv:

2,0

1,1

in copy_test1.csv:

2,0

2,1

1,1

postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV
COPY 2
postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' 
CSV save_error
NOTICE:  No error happened.Error holding table public.test1_error will 
be droped
COPY 3

Maybe I'm launching it the wrong way. If so, let me know about it.

I also notice interesting behavior if the table was previously created 
by the user. When I was creating an error_table before the 'copy from' 
operation,
I received a message saying that it is impossible to create a table with 
the same name (it is shown below) during the 'copy from' operation.
I think you should add information about this in the documentation, 
since this seems to be normal behavior to me.

postgres=# CREATE TABLE test_error (LINENO BIGINT, LINE TEXT,
FIELD TEXT, SOURCE TEXT, ERR_MESSAGE TEXT,
ERR_DETAIL TEXT, ERRORCODE TEXT);
CREATE TABLE
postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV 
save_error
ERROR:  Error save table public.test_error already exists. Cannot use it 
for COPY FROM error saving
> char    *filename; /* filename, or NULL for STDIN */
Yes, I can see that.

I haven't figured out how to fix it yet either.

>> * and if it is, check its column name and data types.

Fine)

---

From: jian he <[email-masked]>
Date: Tue, 12 Dec 2023 21:04:29 +0800

On Mon, Dec 11, 2023 at 10:05 PM Alena Rybakina
<[email-masked]> wrote:
> Yes, thank you! It works fine, and I see that the regression tests have been passed. 🙂
> However, when I ran 'copy from with save_error' operation with simple csv files (copy_test.csv, copy_test1.csv) for tables test, test1 (how I created it, I described below):
>
> I did not find a table with saved errors after operation, although I received a log about it:
>
> postgres=# \copy test from '/home/alena/copy_test.csv' DELIMITER ',' CSV save_error
> NOTICE:  2 rows were skipped because of error. skipped row saved to table public.test_error
> postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' CSV save_error
> NOTICE:  2 rows were skipped because of error. skipped row saved to table public.test1_error
> ERROR:  insert or update on table "test1" violates foreign key constraint "fk_x"
> Two lines were written correctly in the csv files, therefore they should have been added to the tables, but they were not added to the tables test and test1.
>
> If I leave only the correct rows, everything works fine and the rows are added to the tables.
>
> in copy_test.csv:
>
> 2,0
>
> 1,1
>
> in copy_test1.csv:
>
> 2,0
>
> 2,1
>
> 1,1
>
> postgres=# \copy test1 from '/home/alena/copy_test1.csv' DELIMITER ',' CSV save_error
> NOTICE:  No error happened.Error holding table public.test1_error will be droped
> COPY 3
>

looks like the above is about constraints violation while copying.
constraints violation while copying not in the scope of this patch.

Since COPY FROM is very like the INSERT command,
you do want all the valid constraints to check all the copied rows?

but the notice raised by the patch is not right.
So I place the drop error saving table or raise notice logic above
`ExecResetTupleTable(estate->es_tupleTable, false)` in the function
CopyFrom.

>
> I also notice interesting behavior if the table was previously created by the user. When I was creating an error_table before the 'copy from' operation,
> I received a message saying that it is impossible to create a table with the same name (it is shown below) during the 'copy from' operation.
> I think you should add information about this in the documentation, since this seems to be normal behavior to me.
>

doc changed. you may check it.
From 3024bf3b727b728c58dfef41c62d7a93c083b887 Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Tue, 12 Dec 2023 20:58:45 +0800
Subject: [PATCH v11 1/1] Make COPY FROM more error tolerant

Instead of throwing errors while copying, save_error specifier will
save errors to a error saving table automatically.

We check the error saving table definition by column name and column data type.
if table already exists and meets the criteria then errors will save to that table.
if the table does not exist, then create one.

While copying, if error never happened, error saving  table will be dropped at the ending of COPY FROM.
If the error saving table exists, meaning at least once COPY FROM errors has happened,
then all the future errors will be saved to that table.
We save the error related meta info to error saving table using SPI,
that is construct a query string, then execute the query.
---
 contrib/file_fdw/file_fdw.c              |   4 +-
 doc/src/sgml/ref/copy.sgml               | 100 +++++++++++++-
 src/backend/commands/copy.c              |  12 ++
 src/backend/commands/copyfrom.c          | 146 +++++++++++++++++++-
 src/backend/commands/copyfromparse.c     | 169 +++++++++++++++++++++--
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   3 +-
 src/include/commands/copyfrom_internal.h |   7 +
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 135 ++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 108 +++++++++++++++
 12 files changed, 676 insertions(+), 20 deletions(-)

 

index 18ecc69c..fb303b4f 100644
 
@@ -411,6 +412,18 @@ WHERE <replaceable class="parameter">condition</replaceable>
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>SAVE_ERROR</literal></term>
+    <listitem>
+     <para>
+      Specifies that any data conversion errors while copying will automatically saved in an Error Saving table and the <command>COPY FROM</command> operation will not be interrupted by conversion errors.
+      This option is not allowed when using <literal>binary</literal> format. Note that this
+      is only supported in current <command>COPY FROM</command> syntax.
+      If this option is omitted, any data type conversion errors will be raised immediately.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
@@ -564,6 +577,7 @@ COPY <replaceable class="parameter">count</replaceable>
     amount to a considerable amount of wasted disk space if the failure
     happened well into a large copy operation. You might wish to invoke
     <command>VACUUM</command> to recover the wasted space.
+    To continue copying while skip conversion errors in a <command>COPY FROM</command>, you might wish to specify <literal>SAVE_ERROR</literal>.
    </para>
 
    <para>
@@ -572,6 +586,16 @@ COPY <replaceable class="parameter">count</replaceable>
     null strings to null values and unquoted null strings to empty strings.
    </para>
 
+   <para>
+    If the <literal>SAVE_ERROR</literal> option is specified and conversion errors occurred while copying, then
+     <productname>PostgreSQL</productname> will first try to create a regular Error Saving table to save all the conversion errors related information.
+     The Error Saving table naming rule is the existing table name concatenated with  <literal>_error</literal>.
+     If <productname>PostgreSQL</productname> cannot create the Error Saving table, <command>COPY FROM</command> operation stops, an error is raised.
+     All the future errors while copying to the same table will automatically saved to the same Error Saving table.
+     Conversion errors includes data type conversion failure, extra data or missing data in the source file.
+     Error Saving table detailed description listed in <xref linkend="copy-errorsave-table"/>.
+   </para>
+
  </refsect1>
 
  <refsect1>
@@ -588,7 +612,7 @@ COPY <replaceable class="parameter">count</replaceable>
     output function, or acceptable to the input function, of each
     attribute's data type.  The specified null string is used in
     place of columns that are null.
-    <command>COPY FROM</command> will raise an error if any line of the
+    By default, if <literal>SAVE_ERROR</literal> not specified, <command>COPY FROM</command> will raise an error if any line of the
     input file contains more or fewer columns than are expected.
    </para>
 
@@ -962,6 +986,80 @@ versions of <productname>PostgreSQL</productname>.
      check against somehow getting out of sync with the data.
     </para>
    </refsect3>
+
+   <refsect3>
+    <title>Error Save Table </title>
+    <para>
+        If <literal>SAVE_ERROR</literal> specified, all the data type conversion errors while copying will automatically saved in an Error Saving table.
+        <xref linkend="copy-errorsave-table"/> shows the Error Saving table's column name, data type, and description.
+    </para>
+
+   <table id="copy-errorsave-table">
+    <title>Error Saving table description </title>
+
+    <tgroup cols="3">
+     <thead>
+      <row>
+       <entry>Column name</entry>
+       <entry>Data type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+
+      <tbody>
+       <row>
+       <entry> <literal>filename</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The path name of the input file</entry>
+       </row>
+
+       <row>
+       <entry> <literal>lineno</literal> </entry>
+       <entry><type>bigint</type></entry>
+       <entry>Line number where the error occurred, counting from 1</entry>
+       </row>
+
+       <row>
+       <entry> <literal>line</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of the error occurred line</entry>
+       </row>
+
+       <row>
+       <entry> <literal>field</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Field name of the error occurred</entry>
+       </row>
+
+       <row>
+       <entry> <literal>source</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of the error occurred field</entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_message </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error message text </entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_detail</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Detailed error message </entry>
+       </row>
+
+       <row>
+       <entry> <literal>errorcode </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error code for the copying error</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </table>
+   </refsect3>
+
   </refsect2>
  </refsect1>

 
index f4861652..236d711b 100644
 
 	Assert(cstate->rel);
 
+	err_save_buf = makeStringInfo();
 	for (;;)
 	{
 		TupleTableSlot *myslot;
@@ -989,9 +993,13 @@ CopyFrom(CopyFromState cstate)
 		ExecClearTuple(myslot);

+		/* Soft error occured, skip this tuple. */
+		if (cstate->opts.save_error && cstate->line_error_occured)
+			continue;
+
 		ExecStoreVirtualTuple(myslot);
 
 		/*
@@ -1297,6 +1305,48 @@ CopyFrom(CopyFromState cstate)
 
 	ExecResetTupleTable(estate->es_tupleTable, false);
 
+	/* drop the error saving table or raise a notice */
+	if (cstate->opts.save_error)
+	{
+		Assert(cstate->error_nsp && cstate->error_rel);
+
+		if (cstate->error_rows_cnt > 0)
+		{
+			ereport(NOTICE,
+					errmsg("%llu rows were skipped because of conversion error."
+							" Skipped rows saved to table %s.%s",
+							(unsigned long long) cstate->error_rows_cnt,
+							cstate->error_nsp, cstate->error_rel));
+		}
+		else
+		{
+			StringInfoData 	querybuf;
+			if (cstate->error_firsttime)
+			{
+				ereport(NOTICE,
+						errmsg("No conversion error happened. "
+								"Error Saving table %s.%s will be dropped",
+								cstate->error_nsp, cstate->error_rel));
+				initStringInfo(&querybuf);
+				appendStringInfo(&querybuf,
+								"DROP TABLE IF EXISTS %s.%s CASCADE ",
+								cstate->error_nsp, cstate->error_rel);
+
+				if (SPI_connect() != SPI_OK_CONNECT)
+					elog(ERROR, "SPI_connect failed");
+				if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+					elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+				if (SPI_finish() != SPI_OK_FINISH)
+					elog(ERROR, "SPI_finish failed");
+			}
+			else
+				ereport(NOTICE,
+						errmsg("No error happened. "
+								"All previouly encountered conversion errors saved at %s.%s",
+								cstate->error_nsp, cstate->error_rel));
+		}
+	}
+
 	/* Allow the FDW to shut down */
 	if (target_resultRelInfo->ri_FdwRoutine != NULL &&
 		target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
@@ -1444,6 +1494,98 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+		* Verify whether the err_nsp.error_rel table already exists, and if so,
+		* examine its column names and data types.
+							"= '{ctid,filename,lineno,line,field,source,err_message,err_detail,errorcode}') "
+							"AND (ARRAY_AGG(pt.typname ORDER BY pa.attnum) "
index f5537345..aa168d3f 100644
 
+		/* reset line_error_occured to false for next new line. */
+								"INSERT INTO %s.%s(filename, lineno,line, "
+								"SELECT $$%s$$, $$%llu$$::bigint, $$%s$$, $$%s$$, "
+								(unsigned long long) cstate->cur_lineno,
+								cstate->line_buf.data, errmsg_extra,
 
 		fieldno = 0;

 			if (fieldno >= fldct)
-				ereport(ERROR,
+									"INSERT INTO %s.%s(filename,lineno,line, field, "
+									"SELECT $$%s$$, $$%llu$$::bigint, $$%s$$, $$%s$$, "
+									(unsigned long long)  cstate->cur_lineno,
+									cstate->line_buf.data, NameStr(att->attname), errmsg,
 
@@ -956,15 +1031,85 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
+						snprintf(errcode, sizeof(errcode), "%s",
+								 unpack_sql_state(cstate->escontext->error_data->sqlerrcode));
+
+						if (!cstate->escontext->error_data->detail)
+							err_detail = NULL;
+						else
+							err_detail = cstate->escontext->error_data->detail;
+
+						resetStringInfo(err_save_buf);
+						appendStringInfo(err_save_buf,
+										"INSERT INTO %s.%s(filename, lineno,line,field, "
+										"source, err_message, errorcode,err_detail) "
+										"SELECT $$%s$$, $$%llu$$::bigint, $$%s$$, $$%s$$, "
+										"$$%s$$, $$%s$$, $$%s$$, ",
+										cstate->error_nsp, cstate->error_rel,
+										cstate->filename ? cstate->filename : "STDIN",
+										(unsigned long long) cstate->cur_lineno,
+										cstate->line_buf.data, cstate->cur_attname, string,
+										cstate->escontext->error_data->message,
+										errcode);
+
+						if (!err_detail)
+							appendStringInfo(err_save_buf, "NULL::text");
+						else
+							appendStringInfo(err_save_buf,"$$%s$$", err_detail);
+
+						if (SPI_connect() != SPI_OK_CONNECT)
+							elog(ERROR, "SPI_connect failed");
+						if (SPI_execute(err_save_buf->data, false, 0) != SPI_OK_INSERT)
+							elog(ERROR, "SPI_execute failed: %s", err_save_buf->data);
+
+						if (SPI_finish() != SPI_OK_FINISH)
+							elog(ERROR, "SPI_finish failed");
+
+						/* line error occured, set it once per line */
+						if (!cstate->line_error_occured)
+							cstate->line_error_occured = true;
+						/* reset ErrorSaveContext */
+						cstate->escontext->error_occurred = false;
+						cstate->escontext->details_wanted = true;
+						memset(cstate->escontext->error_data,0, sizeof(ErrorData));
+					}
+				}
+			}
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
 
index f16bbd3c..3a616ab5 100644
 
+	SAVEPOINT SAVE_ERROR SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
 
@@ -17346,6 +17350,7 @@ unreserved_keyword:
 			| SCHEMAS
@@ -17954,6 +17959,7 @@ bare_label_keyword:
 			| SCHEMAS
 
index 5ec41589..dd41fcaa 100644

 /*
+	uint64		error_rows_cnt; /* total number of rows that have errors */
index c4178b9c..aa1398d7 100644
+NOTICE:  No conversion error happened. Error Saving table public.save_error_csv_error will be dropped
+--should fail. since table save_error_csv_error already exists.
+NOTICE:  1 rows were skipped because of conversion error. Skipped rows saved to table public.save_error_csv_error
+NOTICE:  1 rows were skipped because of conversion error. Skipped rows saved to table public.save_error_csv_error
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table public.save_error_csv_error
+NOTICE:  8 rows were skipped because of conversion error. Skipped rows saved to table public.check_ign_err_error
+NOTICE:  No error happened. All previouly encountered conversion errors saved at public.check_ign_err_error
+NOTICE:  4 rows were skipped because of conversion error. Skipped rows saved to table public.textrange_input_error
+NOTICE:  3 rows were skipped because of conversion error. Skipped rows saved to table public.copy_default_error_save_error
index a5486f60..3f43ce75 100644
 
+--
+--should fail. since table save_error_csv_error already exists.

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 14 Dec 2023 17:48:41 +0300

> you do want all the valid constraints to check all the copied rows?
No, I think it will be too much.
> `ExecResetTupleTable(estate->es_tupleTable, false)` in the function
Yes, I see it and agree with you.
> doc changed. you may check it.
Yes, I saw it. Thank you.

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 15 Dec 2023 05:48:52 +0900

Hi,

> doc changed. you may check it.

I've read this thread and the latest patch. IIUC with SAVE_ERROR
option, COPY FROM creates an error table for the target table and
writes error information there.

While I agree that the final shape of this feature would be something
like that design, I'm concerned some features are missing in order to
make this feature useful in practice. For instance, error logs are
inserted to error tables without bounds, meaning that users who want
to tolerate errors during COPY FROM  will have to truncate or drop the
error tables periodically, or the database will grow with error logs
without limit. Ideally such maintenance work should be done by the
database. There might be some users who want to log such conversion
errors in server logs to avoid such maintenance work. I think we
should provide an option for where to write, at least. Also, since the
error tables are normal user tables internally, error logs are also
replicated to subscribers if there is a publication FOR ALL TABLES,
unlike system catalogs. I think some users would not like such
behavior.

Looking at SAVE_ERROR feature closely, I think it consists of two
separate features. That is, it enables COPY FROM to load data while
(1) tolerating errors and (2) logging errors to somewhere (i.e., an
error table). If we implement only (1), it would be like COPY FROM
tolerate errors infinitely and log errors to /dev/null. The user
cannot see the error details but I guess it could still help some
cases as Andres mentioned[1] (it might be a good idea to send the
number of rows successfully loaded in a NOTICE message if some rows
could not be loaded). Then with (2), COPY FROM can log error
information to somewhere such as tables and server logs and the user
can select it. So I'm thinking we may be able to implement this
feature incrementally. The first step would be something like an
option to ignore all errors or an option to specify the maximum number
of errors to tolerate before raising an ERROR. The second step would
be to support logging destinations such as server logs and tables.

Regards,

[1] https://www.postgresql.org/message-id/20231109002600.fuihn34bjqqgmbjm%40awork3.anarazel.de

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: jian he <[email-masked]>
Date: Mon, 18 Dec 2023 08:15:00 +0800

> unlike system catalogs. I think some users would not like such

save the error metadata to  system catalogs would be more expensive,
please see below explanation.
I have no knowledge of publications.
but i feel there is a feature request: publication FOR ALL TABLES
exclude regex_pattern.
Anyway, that would be another topic.

> of errors to tolerate before raising an ERROR. The second step would

I don't think "specify the maximum number  of errors to tolerate
before raising an ERROR." is very useful....

QUOTE from [1]
MAXERROR [AS] error_count
If the load returns the error_count number of errors or greater, the
load fails. If the load returns fewer errors, it continues and returns
an INFO message that states the number of rows that could not be
loaded. Use this parameter to allow loads to continue when certain
rows fail to load into the table because of formatting errors or other
inconsistencies in the data.
Set this value to 0 or 1 if you want the load to fail as soon as the
first error occurs. The AS keyword is optional. The MAXERROR default
value is 0 and the limit is 100000.
The actual number of errors reported might be greater than the
specified MAXERROR because of the parallel nature of Amazon Redshift.
If any node in the Amazon Redshift cluster detects that MAXERROR has
been exceeded, each node reports all of the errors it has encountered.
END OF QUOTE

option MAXERROR error_count. iiuc, it fails while validating line
error_count + 1, else it raises a notice, tells you how many rows have
errors.

* case when error_count is small, and the copy fails, it only tells
you that at least the error_count line has malformed data. but what if
the actual malformed rows are very big. In this case, this failure
error message is not that helpful.
* case when error_count is very big, and the copy does not fail. then
the actual malformed data rows are very big (still less than
error_count). but there is no error report, you don't know which line
has an error.

Either way, if the file has a large portion of malformed rows, then
the MAXERROR option does not make sense.
so maybe we don't need a threshold for tolerating errors.

however, we can have an option, not actually copy to the table, but
only validate, similar to NOLOAD in [1]

why we save the error:
* if only a small portion of malformed rows then saving the error
metadata would be cheap.
* if a large portion of malformed rows then  copy will be slow but we
saved the error metadata. Now you can fix it based on this error
metadata.

I think saving errors to a regular table or text file seems sane, but
not to a catalog table.
* for a text file with M rows, N fields, contrived corner case would
be (M-2) * N errors, the last 2 rows have the duplicate keys, violate
primary key constraint. In this case, we first insert  (M-2) * N rows
to the catalog table then because of errors we undo it.
I think it will be expensive.
* error meta info is not as important as other pg_catalog tables.

log format is quite verbose, save_error to log seems not so good, I guess.

I suppose we can specify an ERRORFILE directory. similar
implementation [2], demo in [3]
it will generate 2 files, one file shows the malform line content as
is, another file shows the error info.

Let's assume we save the error info to a table:
Since the previous thread says one copy operation may create one error
table is not a good idea, looking back, I agree.
Similar to [4]
I come with the following logic/ideas:
* save_error table name be COPY_ERRORS, shema be the same as copy from
destination table.
* one COPY_ERRORS table saves all COPY FROM generated error metadata
* if save_error specified, before do COPY FROM, first check if the
table COPY_ERRORS
exists,
if not then create one? Or raise an error saying that COPY_ERRORS does
not exist, cannot save_error?
*  COPY_ERRORS table owner be current database owner?
* Only the table owner is allowed to INSERT/DELETE/UPDATE, others are
not allowed to INSERT/DELETE/UPDATE.
while doing copy error happened, record the userid, then switch
COPY_ERRORS owner execute the insert command
*  the user who is doing COPY FROM operation is allowed solely to view
(select) the errored row they generated.

COPY_ERRORS table would be:
userid    oid                        /* the user who is doing this operation */
error_time    timestamptz                       /* when this error
happened. not 100% sure this column is needed */
filename text                      /* the copy from source */
table_name text                 /*  the copy from destination */
lineno    bigint                    /* the error line number */
line          text                     /* the whole line raw content */
colname  text               -- Field with the error.
raw_field_value  text   --- The value for the field that leads to the error.
err_message      text   -- same as ErrorData->message
err_detail      text         --same as ErrorData->detail
errorcode     text         --transformed errcode, example "22P02"

[1] https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html
[2] https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16
[3] https://www.sqlshack.com/working-with-line-numbers-and-errors-using-bulk-insert/
[4] https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html

---

From: torikoshia <[email-masked]>
Date: Mon, 18 Dec 2023 11:41:42 +0900

On 2023-12-15 05:48, Masahiko Sawada wrote:

Thanks for joining this discussion!

> can select it.

+1.
I may be biased since I wrote some ~v6 patches which just output the 
soft errors and number of skipped rows to log, but I think just (1) 
would be worth implementing as you pointed out and I like if users could 
choose where to log output.

I think there would be situations where it is preferable to save errors 
to server log even considering problems which were pointed out in [1], 
i.e. manually loading data.

[1] 
https://www.postgresql.org/message-id/739953.1699467519%40sss.pgh.pa.us

> [1] 
> https://www.postgresql.org/message-id/20231109002600.fuihn34bjqqgmbjm%40awork3.anarazel.de

-- 
Regards,

---

From: torikoshia <[email-masked]>
Date: Mon, 18 Dec 2023 14:09:03 +0900

Hi,

> Anyway, that would be another topic.

I think saving error metadata to system catalog is not a good idea, too.
And I believe Sawada-san just pointed out missing features and did not 
suggested that we use system catalog.

> before raising an ERROR." is very useful....

That may be so.
I imagine it's useful in some use case since some loading tools have 
such options.
Anyway I agree it's not necessary for initial patch as mentioned in [1].

> is, another file shows the error info.

That may be a good option when considering "(2) logging errors to 
somewhere".

What do you think about the proposal to develop these features in 
incrementally?

On 2023-12-15 05:48, Masahiko Sawada wrote:
> So I'm thinking we may be able to implement this

[1] 
https://www.postgresql.org/message-id/752672.1699474336%40sss.pgh.pa.us

-- 
Regards,

---

From: jian he <[email-masked]>
Date: Mon, 18 Dec 2023 15:41:19 +0800

> What do you think about the proposal to develop these features in

I am more with  tom's idea [1], that is when errors happen (data type
conversion only), do not fail, AND we save the error to a table.  I
guess we can implement this logic together, only with a new COPY
option.

imagine a case (it's not that contrived, imho), while conversion from
text to table's int, postgres isspace is different from the source
text file's isspace logic.
then all the lines are malformed. If we just say on error continue and
not save error meta info, the user is still confused which field has
the wrong data, then the user will probably try to incrementally test
which field contains malformed data.

Since we need to save the error somewhere.
Everyone has the privilege to INSERT can do COPY.
I think we also need to handle the access privilege also.
So like I mentioned above, one copy_error error table hub, then
everyone can view/select their own copy failure record.

but save to a server text file/directory, not easy for an INSERT
privilege user to see these files, I think.
similarly not easy to see these failed records in log for limited privilege.

if someone wants to fail at maxerror rows, they can do it, since we
will count how many rows failed.
even though I didn't get it.

[1] https://www.postgresql.org/message-id/900123.1699488001%40sss.pgh.pa.us

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 19 Dec 2023 09:28:08 +0900

> Anyway, that would be another topic.

I don't think the new regex idea would be a good solution for the
existing users who are using FOR ALL TABLES publication. It's not
desirable that they have to change the publication because of this
feature. With the current patch, a logical replication using FOR ALL
TABLES publication will stop immediately after an error information is
inserted into a new error table unless the same error table is created
on subscribers.

> only validate, similar to NOLOAD in [1]

I'm fine even if the feature is not like MAXERROR. If we want a
feature to tolerate errors during COPY FROM, I just thought it might
be a good idea to have a tuning knob for better flexibility, not just
like a on/off switch.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 19 Dec 2023 10:13:31 +0900

> guess we can implement this logic together, only with a new COPY

If we want only such a feature we need to implement it together (the
patch could be split, though). But if some parts of the feature are
useful for users as well, I'd recommend implementing it incrementally.
That way, the patches can get small and it would be easy for reviewers
and committers to review/commit them.

> everyone can view/select their own copy failure record.

The error table hub idea is still unclear to me. I assume that there
are error tables at least on each database. And an error table can
have error data that happened during COPY FROM, including malformed
lines. Do the error tables grow without bounds and the users have to
delete rows at some point? If so, who can do that? How can we achieve
that the users can see only errored rows they generated? And the issue
with logical replication also needs to be resolved. Anyway, if we go
this direction, we need to discuss the overall design.

Regards,

---

From: jian he <[email-masked]>
Date: Wed, 20 Dec 2023 12:07:38 +0800

> this direction, we need to discuss the overall design.

Please check my latest attached POC.
Main content is to build spi query, execute the spi query, regress
test and regress output.

copy_errors one per schema.
foo.copy_errors will be owned by the schema: foo owner.

if you can insert to a table in that specific schema let's say foo,
then you will get privilege to INSERT/DELETE/SELECT
to foo.copy_errors.
If you are not a superuser, you are only allowed to do
INSERT/DELETE/SELECT on foo.copy_errors rows where USERID =
current_user::regrole::oid.
This is done via row level security.

Since foo.copy_errors is mainly INSERT operations, if copy_errors grow
too much, that means your source file has many errors, it will take a
very long time to finish the whole COPY. maybe we can capture how many
errors encountered in another client.

I don't know how to deal with logic replication. looking for ideas.
From 9affaf6d94eb4afe26fc7181e38e53eed14e0216 Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Wed, 20 Dec 2023 11:26:25 +0800
Subject: [PATCH v12 1/1] Make COPY FROM more error tolerant

Instead of throwing errors while copying, save_error specifier will
save errors to table copy_errors for all the copy from operation in the same schema.

We check the existing copy_error table definition by column name and column data type.
if table already exists and meets the criteria then errors will to it
if the table does not exist, then create one.

copy_errors is per schema, it's owned by schema's owner.
for non-superusers, if you can do insert in that schema, then you can insert to copy_errors,
but you are only allowed to select/delet your own rows, which is judged by current_user
with copy_error's userid column. Priviledge restirction is implmented via ROW LEVEL SECURITY.

While copying, if error never happened, error saving  table will be dropped at the ending of COPY FROM.
If the error saving table exists, meaning at least once COPY FROM errors has happened,
then all the future errors will be saved to that table.
We save the error related meta info to error saving table using SPI,
that is construct a query string, then execute the query.
---
 contrib/file_fdw/file_fdw.c              |   4 +-
 doc/src/sgml/ref/copy.sgml               | 122 ++++++++++++++-
 src/backend/commands/copy.c              |  12 ++
 src/backend/commands/copyfrom.c          | 168 ++++++++++++++++++++-
 src/backend/commands/copyfromparse.c     | 179 +++++++++++++++++++++--
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   3 +-
 src/include/commands/copyfrom_internal.h |   5 +
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 160 ++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 142 ++++++++++++++++++
 12 files changed, 787 insertions(+), 20 deletions(-)

 

index 18ecc69c..3dbf70ee 100644

+   <varlistentry>
+      Specifies that any data conversion errors while copying will automatically saved in table <literal>COPY_ERRORS</literal> and the <command>COPY FROM</command> operation will not be interrupted by conversion errors.

    <para>
@@ -572,6 +586,19 @@ COPY <replaceable class="parameter">count</replaceable>
     null strings to null values and unquoted null strings to empty strings.
    </para>
 
+   <para>
+
+    If the <literal>SAVE_ERROR</literal> option is specified and conversion errors occur while copying,
+    <productname>PostgreSQL</productname> will first check the table <literal>COPY_ERRORS</literal> existence, then save the conversion error related information to it.
+    If it does exist, but the actual table definition cannot use it to save the error information, an error is raised, <command>COPY FROM</command> operation stops.
+    If it does not exist, <productname>PostgreSQL</productname> will try to create it before doing the actual copy operation.
+    The table <literal>COPY_ERRORS</literal> owner is the current schema owner.
+    All the future errors related information generated while copying data to the same schema will automatically be saved to the same <literal>COPY_ERRORS</literal> table.
+    Copy conversion error is privileged information, non-superusers is only allowed to <literal>SELECT</literal>, <literal>DELETE</literal> or <literal>INSERT</literal> their own row in the <literal>COPY_ERRORS</literal> table.
+    Conversion errors include data type conversion failure, extra data or missing data in the source file.
+    <literal>COPY_ERRORS</literal> table detailed description listed in <xref linkend="copy-errors-table"/>.
+
+   </para>
  </refsect1>
 
  <refsect1>
@@ -588,7 +615,7 @@ COPY <replaceable class="parameter">count</replaceable>
 
@@ -962,6 +989,99 @@ versions of <productname>PostgreSQL</productname>.
      check against somehow getting out of sync with the data.
     </para>
    </refsect3>
+
+   <refsect3>
+    <title> TABLE COPY_ERRORS </title>
+    <para>
+        If <literal>SAVE_ERROR</literal> specified, all the data type conversion errors while copying will automatically saved in <literal>COPY_ERRORS</literal>
+        <xref linkend="copy-errors-table"/> shows <literal>COPY_ERRORS</literal>  table's column name, data type, and description.
+    </para>
+
+   <table id="copy-errors-table">
+    <title>Error Saving table description </title>
+
+    <tgroup cols="3">
+     <thead>
+      <row>
+       <entry>Column name</entry>
+       <entry>Data type</entry>
+       <entry>Description</entry>
+      </row>
+     </thead>
+
+      <tbody>
+       <row>
+       <entry> <literal>userid</literal> </entry>
+       <entry><type>oid</type></entry>
+       <entry>The user generated the conversion error.
+       Refer <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>.
+       There is no hard depenedency with <literal>pg_authid</literal>, if correspond <structfield>oid</structfield> deleted in <literal>pg_authid</literal>, it becomes stale.
+    </entry>
+       </row>
+
+       <row>
+       <entry> <literal>copy_destination</literal> </entry>
+       <entry><type>oid</type></entry>
+       <entry>The <command>COPY FROM</command> operation destination table oid.
+        Refer <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>.
+        There is no hard depenedency with <literal>pg_class</literal> if correspond <structfield>oid</structfield> deleted in <literal>pg_class</literal>, it becomes stale.
+
+        </entry>
+       </row>
+
+       <row>
+       <entry> <literal>filename</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The path name of the input filed</entry>
+       </row>
+
+       <row>
+       <entry> <literal>lineno</literal> </entry>
+       <entry><type>bigint</type></entry>
+       <entry>Line number where the error occurred, counting from 1</entry>
+       </row>
+
+       <row>
+       <entry> <literal>line</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of the error occurred line</entry>
+       </row>
+
+       <row>
+       <entry> <literal>colname</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Field where the error occurred</entry>
+       </row>
+
+       <row>
+       <entry> <literal>raw_field_value</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Raw content of the error occurred field</entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_message </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error message text </entry>
+       </row>
+
+       <row>
+       <entry> <literal>err_detail</literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>Detailed error message </entry>
+       </row>
+
+       <row>
+       <entry> <literal>errorcode </literal> </entry>
+       <entry><type>text</type></entry>
+       <entry>The error code for the copying error</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </table>
+   </refsect3>
+
   </refsect2>
  </refsect1>

 
+	if (opts_out->binary && opts_out->save_error)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify SAVE_ERROR in BINARY mode")));
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index f4861652..a84080b4 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -29,7 +29,9 @@
 #include "access/tableam.h"
 #include "access/xact.h"
 #include "access/xlog.h"
+#include "catalog/pg_authid.h"
 #include "catalog/namespace.h"
+#include "catalog/pg_namespace.h"
 #include "commands/copy.h"
 #include "commands/copyfrom_internal.h"
 #include "commands/progress.h"
@@ -38,6 +40,7 @@
 #include "executor/executor.h"
 #include "executor/nodeModifyTable.h"
 #include "executor/tuptable.h"
+#include "executor/spi.h"
 #include "foreign/fdwapi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
@@ -52,6 +55,7 @@
 #include "utils/portal.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 
 /*
  * No more than this many tuples per CopyMultiInsertBuffer
@@ -652,10 +656,12 @@ CopyFrom(CopyFromState cstate)
 	bool		has_before_insert_row_trig;
 	bool		has_instead_insert_row_trig;
 	bool		leafpart_use_multi_insert = false;
+	StringInfo	err_save_buf;
 
 	Assert(cstate->rel);
@@ -952,6 +958,7 @@ CopyFrom(CopyFromState cstate)
 
+	err_save_buf = makeStringInfo();
 	for (;;)
 	{
 		TupleTableSlot *myslot;
@@ -989,9 +996,13 @@ CopyFrom(CopyFromState cstate)
 		ExecClearTuple(myslot);

 
 		/*
@@ -1297,6 +1308,20 @@ CopyFrom(CopyFromState cstate)

+	if (cstate->opts.save_error)
+	{
+		Assert(cstate->copy_errors_nspname);
+
+		if (cstate->error_rows_cnt > 0)
+		{
+			ereport(NOTICE,
+					errmsg("%llu rows were skipped because of conversion error."
+							" Skipped rows saved to table %s.copy_errors",
+							(unsigned long long) cstate->error_rows_cnt,
+							cstate->copy_errors_nspname));
+		}
+	}
+
 	/* Allow the FDW to shut down */
 	if (target_resultRelInfo->ri_FdwRoutine != NULL &&
 		target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
@@ -1444,6 +1469,145 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+	/* Set up soft error handler for SAVE_ERROR */
+	if (cstate->opts.save_error)
+	{
+		StringInfoData 	querybuf;
+		bool		isnull;
+		bool		copy_erros_table_ok;
+		Oid			nsp_oid;
+		Oid			save_userid;
+		Oid			ownerId;
+		int			save_sec_context;
+		const char	*copy_errors_nspname;
+		HeapTuple	utup;
+		HeapTuple	tuple;
+		const char	*rname;
+
+		cstate->escontext = makeNode(ErrorSaveContext);
+		cstate->escontext->type = T_ErrorSaveContext;
+		cstate->escontext->details_wanted = true;
+		cstate->escontext->error_occurred = false;
+
+		copy_errors_nspname = get_namespace_name(RelationGetNamespace(cstate->rel));
+		nsp_oid = get_namespace_oid(copy_errors_nspname, false);
+
+		initStringInfo(&querybuf);
+		/*
+		*
+		* Verify whether the nsp_oid.COPY_ERRORS table already exists, and if so,
+		* examine its column names and data types.
+		*/
+		appendStringInfo(&querybuf,
+						"SELECT (array_agg(pa.attname ORDER BY pa.attnum) "
+							"= '{ctid,userid,copy_destination,filename,lineno, "
+							"line,colname,raw_field_value,err_message,err_detail,errorcode}') "
+							"AND (ARRAY_AGG(pt.typname ORDER BY pa.attnum) "
+							"= '{tid,oid,oid,text,int8,text,text,text,text,text,text}') "
+							"FROM pg_catalog.pg_attribute pa "
+							"JOIN pg_catalog.pg_class	pc ON pc.oid = pa.attrelid "
+							"JOIN pg_catalog.pg_type 	pt ON pt.oid = pa.atttypid "
+							"JOIN pg_catalog.pg_namespace pn "
+							"ON pn.oid = pc.relnamespace WHERE ");
+		appendStringInfo(&querybuf,
+							"relname = $$copy_errors$$ AND pn.nspname = $$%s$$ "
+							" AND pa.attnum >= -1 AND NOT attisdropped ",
+							copy_errors_nspname);
+
+		if (SPI_connect() != SPI_OK_CONNECT)
+			elog(ERROR, "SPI_connect failed");
+
+		if (SPI_execute(querybuf.data, false, 0) != SPI_OK_SELECT)
+			elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+		copy_erros_table_ok = DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0],
+									   SPI_tuptable->tupdesc,
+									   1, &isnull));
+		/*
+		* Switch to the schema owner's userid, so that the COPY_ERRORS table owned by
+		* that user. Also record the current userid.
+		*/
+		GetUserIdAndSecContext(&save_userid, &save_sec_context);
+
+		utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(save_userid));
+		if (!HeapTupleIsValid(utup))
+			elog(ERROR, "cache lookup failed for role %u", save_userid);
+
+		rname = pstrdup(NameStr(((Form_pg_authid) GETSTRUCT(utup))->rolname));
+		ReleaseSysCache(utup);
+
+		tuple = SearchSysCache1(NAMESPACEOID, ObjectIdGetDatum(nsp_oid));
+		if (!HeapTupleIsValid(utup))
+				ereport(ERROR,
+						(errcode(ERRCODE_UNDEFINED_SCHEMA),
+						errmsg("schema with OID %u does not exist", nsp_oid)));
+		ownerId = ((Form_pg_namespace) GETSTRUCT(tuple))->nspowner;
+		ReleaseSysCache(tuple);
+
+		/* not sure the flag is correct */
+		SetUserIdAndSecContext(ownerId,
+							save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
+							SECURITY_NOFORCE_RLS);
+
+		/* No copy_errors_nspname.COPY_ERRORS table then create it for holding all the potential error. */
+		if (isnull)
+		{
+			resetStringInfo(&querybuf);
+			appendStringInfo(&querybuf,
+				"CREATE TABLE %s.COPY_ERRORS( "
+					"USERID OID, COPY_DESTINATION OID, FILENAME TEXT,LINENO BIGINT "
+					",LINE TEXT, COLNAME text, RAW_FIELD_VALUE TEXT "
+					",ERR_MESSAGE TEXT, ERR_DETAIL TEXT, ERRORCODE TEXT)", copy_errors_nspname);
+
+			if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+				elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+			resetStringInfo(&querybuf);
+			appendStringInfo(&querybuf,
+				"CREATE POLICY copyerror ON %s.COPY_ERRORS "
+					"FOR ALL TO PUBLIC USING (USERID = current_user::regrole::oid)",
+					 copy_errors_nspname);
+
+			if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+				elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+			resetStringInfo(&querybuf);
+			appendStringInfo(&querybuf,
+			"ALTER TABLE %s.COPY_ERRORS ENABLE ROW LEVEL SECURITY", copy_errors_nspname);
+
+			if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+				elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+		}
+		else if(!copy_erros_table_ok)
+			ereport(ERROR,
+					(errmsg("table %s.COPY_ERRORS already exists. "
+								 "cannot use it for COPY FROM error saving",
+								 copy_errors_nspname)));
+
+		/* grant INSERT/SELECT on copy_errors to the copy operation user now */
+		resetStringInfo(&querybuf);
+		appendStringInfo(&querybuf,
+				"GRANT SELECT, DELETE, INSERT ON TABLE %s.COPY_ERRORS TO %s", copy_errors_nspname, rname);
+
+		if (SPI_execute(querybuf.data, false, 0) != SPI_OK_UTILITY)
+			elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+		if (SPI_finish() != SPI_OK_FINISH)
+			elog(ERROR, "SPI_finish failed");
+
+		/* Restore userid and security context */
+		SetUserIdAndSecContext(save_userid, save_sec_context);
+		cstate->copy_errors_nspname = pstrdup(copy_errors_nspname);
+	}
+	else
+	{
+		cstate->copy_errors_nspname = NULL;
+		cstate->escontext = NULL;
+	}
+
+	cstate->error_rows_cnt = 0;  		/* set the default to 0 */
+	cstate->line_error_occured = false;	/* default, assume conversion be ok. */
+
 	/* Convert convert_selectively name list to per-column flags */
 	if (cstate->opts.convert_selectively)
 	{
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index f5537345..f1a6f9dc 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -66,10 +66,12 @@
 #include "commands/copyfrom_internal.h"
 #include "commands/progress.h"
 #include "executor/executor.h"
+#include "executor/spi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -852,7 +854,7 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
  */
 bool
 NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
-			 Datum *values, bool *nulls)
+			 Datum *values, bool *nulls, StringInfo err_save_buf)
 {
 	TupleDesc	tupDesc;
 	AttrNumber	num_phys_attrs,
@@ -880,16 +882,60 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		int			fldct;
 		int			fieldno;
 		char	   *string;
+		char		*errmsg_extra;
+		Oid			save_userid;
+		int			save_sec_context;
 
 		/* read raw fields in the next line */
 		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
 			return false;
 
+		/* reset line_error_occured to false for next new line. */
+		if (cstate->line_error_occured)
+			cstate->line_error_occured = false;
+
+		/* we need to get the current userid for the SPI queries */
+		if (cstate->opts.save_error)
+			GetUserIdAndSecContext(&save_userid, &save_sec_context);
+
 		/* check for overflowing fields */
 		if (attr_count > 0 && fldct > attr_count)
-			ereport(ERROR,
-					(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-					 errmsg("extra data after last expected column")));
+		{
+			if(cstate->opts.save_error)
+			{
+				errmsg_extra = pstrdup("extra data after last expected column");
+
+				resetStringInfo(err_save_buf);
+				appendStringInfo(err_save_buf,
+								"INSERT INTO %s.copy_errors(userid, copy_destination, filename,lineno,line, "
+								"err_message, errorcode) "
+								"SELECT %u, %u,$$%s$$, %llu,$$%s$$, $$%s$$, $$%s$$",
+								cstate->copy_errors_nspname,
+								save_userid,
+								cstate->rel->rd_rel->oid,
+								cstate->filename ? cstate->filename : "STDIN",
+								(unsigned long long) cstate->cur_lineno,
+								cstate->line_buf.data,
+								errmsg_extra,
+								unpack_sql_state(ERRCODE_BAD_COPY_FILE_FORMAT));
+				if (SPI_connect() != SPI_OK_CONNECT)
+					elog(ERROR, "SPI_connect failed");
+
+				if (SPI_execute(err_save_buf->data, false, 0) != SPI_OK_INSERT)
+					elog(ERROR, "SPI_exec failed: %s", err_save_buf->data);
+
+				if (SPI_finish() != SPI_OK_FINISH)
+					elog(ERROR, "SPI_finish failed");
+
+				cstate->line_error_occured = true;
+				cstate->error_rows_cnt++;
+				return true;
+			}
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						errmsg("extra data after last expected column")));
+		}
 
 		fieldno = 0;
 
@@ -901,10 +947,50 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 			Form_pg_attribute att = TupleDescAttr(tupDesc, m);
 
 			if (fieldno >= fldct)
-				ereport(ERROR,
+								"INSERT INTO %s.copy_errors( "
+									"userid,copy_destination,filename, "
+									"lineno,line,COLNAME, err_message, errorcode) "
+									"SELECT %u, %u, $$%s$$, %llu, $$%s$$, $$%s$$, $$%s$$, $$%s$$ ",
+									cstate->copy_errors_nspname,
+									save_userid,
+									cstate->rel->rd_rel->oid,
+									cstate->line_buf.data,
+									NameStr(att->attname),
+									errmsg,
 
@@ -956,15 +1042,84 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
+										"INSERT INTO %s.copy_errors(userid,copy_destination, "
+										"filename, lineno,line,COLNAME, "
+										"raw_field_value, err_message,errorcode, err_detail) "
+										"SELECT %u, %u, $$%s$$, %llu, $$%s$$, $$%s$$, $$%s$$, $$%s$$, $$%s$$, ",
+										cstate->copy_errors_nspname,
+										save_userid,
+										cstate->rel->rd_rel->oid,
+										cstate->line_buf.data,
+										cstate->cur_attname,
 
index 63f172e1..f42e72aa 100644

 
index 5ec41589..65e34e89 100644

 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,10 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	ErrorSaveContext *escontext; 	/* soft error trapper during in_functions execution */
+	uint64		error_rows_cnt; /* total number of rows that have errors */
+	const char 	*copy_errors_nspname;		/* the copy_errors's namespace */
+	bool	 	line_error_occured;	/* does this line conversion error happened */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa..d0988a4c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -377,6 +377,7 @@ PG_KEYWORD("routines", ROUTINES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("row", ROW, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("save_error", SAVE_ERROR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("scalar", SCALAR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index c4178b9c..f5a84487 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -564,6 +564,142 @@ ERROR:  conflicting or redundant options
 LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
                                                              ^
 ROLLBACK;
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+ERROR:  cannot specify SAVE_ERROR in BINARY mode
+-- redundant options not allowed.
+COPY save_error_csv FROM STDIN WITH (save_error, save_error off);
+ERROR:  conflicting or redundant options
+LINE 1: COPY save_error_csv FROM STDIN WITH (save_error, save_error ...
+                                                         ^
+create table COPY_ERRORS();
+--should fail. since table COPY_ERRORS already exists.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+ERROR:  table public.COPY_ERRORS already exists. cannot use it for COPY FROM error saving
+drop table COPY_ERRORS;
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+ a | b |  c   |  d   | b_null | b_empty 
+---+---+------+------+--------+---------
+ 2 |   | NULL | NULL | f      | t
+(1 row)
+
+DROP TABLE save_error_csv;
+-- save error with extra data and missing data some column.
+---normal data type conversion error case.
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+NOTICE:  10 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+select	pc.relname, ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+from	copy_errors ce	join pg_class pc on pc.oid = ce.copy_destination
+where 	pc.relname = 'check_ign_err';
+    relname    | filename | lineno |                    line                    | colname |     raw_field_value     |                           err_message                           |        err_detail         | errorcode 
+---------------+----------+--------+--------------------------------------------+---------+-------------------------+-----------------------------------------------------------------+---------------------------+-----------
+ check_ign_err | STDIN    |      1 | 1       {1}     1       1       extra      | NULL    | NULL                    | extra data after last expected column                           | NULL                      | 22P04
+ check_ign_err | STDIN    |      2 | 2                                          | m       | NULL                    | missing data for column "m"                                     | NULL                      | 22P04
+ check_ign_err | STDIN    |      3 | \n      {1}     1       \-                 | n       |                        +| invalid input syntax for type integer: "                       +| NULL                      | 22P02
+               |          |        |                                            |         |                         | "                                                               |                           | 
+ check_ign_err | STDIN    |      4 | a       {2}     2       \r                 | n       | a                       | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      5 | 3       {\3}    3333333333      \n         | m       | {\x03}                  | invalid input syntax for type integer: "\x03"                   | NULL                      | 22P02
+ check_ign_err | STDIN    |      6 | 0x11    {3,}    3333333333      \\.        | m       | {3,}                    | malformed array literal: "{3,}"                                 | Unexpected "}" character. | 22P02
+ check_ign_err | STDIN    |      7 | d       {3,1/}  3333333333      \\0        | n       | d                       | invalid input syntax for type integer: "d"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      7 | d       {3,1/}  3333333333      \\0        | m       | {3,1/}                  | invalid input syntax for type integer: "1/"                     | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | n       | e                       | invalid input syntax for type integer: "e"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | m       | {3,\x01}                | invalid input syntax for type integer: "\x01"                   | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | k       | -3323879289873933333333 | value "-3323879289873933333333" is out of range for type bigint | NULL                      | 22003
+ check_ign_err | STDIN    |      9 | f       {3,1}   3323879289873933333333  \r | n       | f                       | invalid input syntax for type integer: "f"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      9 | f       {3,1}   3323879289873933333333  \r | k       | 3323879289873933333333  | value "3323879289873933333333" is out of range for type bigint  | NULL                      | 22003
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | n       | b                       | invalid input syntax for type integer: "b"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | m       | {a, 4}                  | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | k       | 1.1                     | invalid input syntax for type bigint: "1.1"                     | NULL                      | 22P02
+(16 rows)
+
+DROP TABLE check_ign_err;
+DROP TABLE COPY_ERRORS;
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+begin;
+CREATE USER test_copy_errors1;
+CREATE USER test_copy_errors2;
+CREATE USER test_copy_errors3;
+CREATE SCHEMA IF NOT EXISTS copy_errors_test AUTHORIZATION test_copy_errors3;
+SET LOCAL search_path TO copy_errors_test;
+GRANT USAGE on schema copy_errors_test to test_copy_errors1,test_copy_errors2,test_copy_errors3;
+GRANT CREATE on schema copy_errors_test to test_copy_errors3;
+set role test_copy_errors3;
+CREATE TABLE textrange_input(a public.textrange, b public.textrange, c public.textrange);
+GRANT insert on textrange_input to test_copy_errors1;
+GRANT insert on textrange_input to test_copy_errors2;
+set role test_copy_errors1;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table copy_errors_test.copy_errors
+---each user is only allowed to see their own rows.
+--based on userid is the same as current_user.
+select 	count(*) as should_be_zero
+from 	copy_errors_test.copy_errors ce
+join	pg_class	pc	on pc.oid = ce.copy_destination
+join	pg_roles	pr 	on pr.oid = ce.userid
+where 	ce.userid != current_user::regrole::oid;
+ should_be_zero 
+----------------
+              0
+(1 row)
+
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+     relname     |      rolname      | filename | lineno |         line          | colname | raw_field_value |                            err_message                            |                err_detail                | errorcode 
+-----------------+-------------------+----------+--------+-----------------------+---------+-----------------+-------------------------------------------------------------------+------------------------------------------+-----------
+ textrange_input | test_copy_errors1 | STDIN    |      1 | ,-[a\","z),[a","-inf) | b       | -[a\,z)         | malformed range literal: "-[a\,z)"                                | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      1 | ,-[a\","z),[a","-inf) | c       | [a,-inf)        | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | a       | (,a),(          | malformed range literal: "(,a),("                                 | Junk after right parenthesis or bracket. | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | b       | ,a),()          | malformed range literal: ",a),()"                                 | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | c       | a);             | malformed range literal: "a);"                                    | Missing left parenthesis or bracket.     | 22P02
+(5 rows)
+
+set role test_copy_errors2;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table copy_errors_test.copy_errors
+SAVEPOINT s1;
+--current user (non-super user) are not allowed to update)
+update copy_errors_test.copy_errors set userid = 0;
+ERROR:  permission denied for table copy_errors
+ROLLBACK to s1;
+--current user (non-super user) are allowed to delete all the record they created.
+delete from copy_errors_test.copy_errors;
+set role test_copy_errors1;
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+     relname     |      rolname      | filename | lineno |         line          | colname | raw_field_value |                            err_message                            |                err_detail                | errorcode 
+-----------------+-------------------+----------+--------+-----------------------+---------+-----------------+-------------------------------------------------------------------+------------------------------------------+-----------
+ textrange_input | test_copy_errors1 | STDIN    |      1 | ,-[a\","z),[a","-inf) | b       | -[a\,z)         | malformed range literal: "-[a\,z)"                                | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      1 | ,-[a\","z),[a","-inf) | c       | [a,-inf)        | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | a       | (,a),(          | malformed range literal: "(,a),("                                 | Junk after right parenthesis or bracket. | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | b       | ,a),()          | malformed range literal: ",a),()"                                 | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | test_copy_errors1 | STDIN    |      2 | (",a),(",",a),()",a); | c       | a);             | malformed range literal: "a);"                                    | Missing left parenthesis or bracket.     | 22P02
+(5 rows)
+
+set role test_copy_errors3;
+--owner allowed to drop the table.
+drop table copy_errors;
+ROLLBACK;
 \pset null ''
 -- test case with whole-row Var in a check constraint
 create table check_con_tbl (f1 int);
@@ -822,3 +958,27 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT only available using COPY FROM
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+NOTICE:  3 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+select 	ce.filename,ce.lineno,ce.line,
+		ce.colname, ce.raw_field_value,
+		ce.err_message, ce.err_detail,ce.errorcode
+from 	public.copy_errors ce
+join	pg_class	pc	on pc.oid = ce.copy_destination
+where	pc.relname = 'copy_default_error_save';
+ filename | lineno |               line               | colname  | raw_field_value  |                         err_message                         | err_detail | errorcode 
+----------+--------+----------------------------------+----------+------------------+-------------------------------------------------------------+------------+-----------
+ STDIN    |      1 | k       value   '2022-07-04'     | id       | k                | invalid input syntax for type integer: "k"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | id       | z                | invalid input syntax for type integer: "z"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | ts_value | '2022-07-03ASKL' | invalid input syntax for type timestamp: "'2022-07-03ASKL'" |            | 22007
+ STDIN    |      3 | s       \D      \D               | id       | s                | invalid input syntax for type integer: "s"                  |            | 22P02
+(4 rows)
+
+drop table copy_default_error_save;
+truncate copy_default;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index a5486f60..a4ef06d9 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -374,6 +374,126 @@ BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
 ROLLBACK;
 
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT,
+    d TEXT
+);
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+
+-- redundant options not allowed.
+COPY save_error_csv FROM STDIN WITH (save_error, save_error off);
+
+create table COPY_ERRORS();
+--should fail. since table COPY_ERRORS already exists.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+
+drop table COPY_ERRORS;
+
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+z,,""
+\0,,
+2,,
+\.
+
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+DROP TABLE save_error_csv;
+
+-- save error with extra data and missing data some column.
+---normal data type conversion error case.
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+1	{1}	1	1	extra
+2
+\n	{1}	1	\-
+a	{2}	2	\r
+3	{\3}	3333333333	\n
+0x11	{3,}	3333333333	\\.
+d	{3,1/}	3333333333	\\0
+e	{3,\1}	-3323879289873933333333	\n
+f	{3,1}	3323879289873933333333	\r
+b	{a, 4}	1.1	h
+5	{5}	5	\\
+\.
+
+select	pc.relname, ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+from	copy_errors ce	join pg_class pc on pc.oid = ce.copy_destination
+where 	pc.relname = 'check_ign_err';
+
+DROP TABLE check_ign_err;
+DROP TABLE COPY_ERRORS;
+
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+begin;
+
+CREATE USER test_copy_errors1;
+CREATE USER test_copy_errors2;
+CREATE USER test_copy_errors3;
+CREATE SCHEMA IF NOT EXISTS copy_errors_test AUTHORIZATION test_copy_errors3;
+SET LOCAL search_path TO copy_errors_test;
+
+GRANT USAGE on schema copy_errors_test to test_copy_errors1,test_copy_errors2,test_copy_errors3;
+GRANT CREATE on schema copy_errors_test to test_copy_errors3;
+set role test_copy_errors3;
+CREATE TABLE textrange_input(a public.textrange, b public.textrange, c public.textrange);
+GRANT insert on textrange_input to test_copy_errors1;
+GRANT insert on textrange_input to test_copy_errors2;
+
+set role test_copy_errors1;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+,-[a\","z),[a","-inf)
+(",a),(",",a),()",a);
+\.
+
+---each user is only allowed to see their own rows.
+--based on userid is the same as current_user.
+select 	count(*) as should_be_zero
+from 	copy_errors_test.copy_errors ce
+join	pg_class	pc	on pc.oid = ce.copy_destination
+join	pg_roles	pr 	on pr.oid = ce.userid
+where 	ce.userid != current_user::regrole::oid;
+
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+
+set role test_copy_errors2;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+(a",")),(]","a),(a","])
+[z","a],[z","2],[(","",")]
+\.
+
+SAVEPOINT s1;
+--current user (non-super user) are not allowed to update)
+update copy_errors_test.copy_errors set userid = 0;
+ROLLBACK to s1;
+
+--current user (non-super user) are allowed to delete all the record they created.
+delete from copy_errors_test.copy_errors;
+
+set role test_copy_errors1;
+
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+
+set role test_copy_errors3;
+--owner allowed to drop the table.
+drop table copy_errors;
+ROLLBACK;
 \pset null ''
 
 -- test case with whole-row Var in a check constraint
@@ -609,3 +729,25 @@ truncate copy_default;
 
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
+
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+k	value	'2022-07-04'
+z	\D	'2022-07-03ASKL'
+s	\D	\D
+\.
+
+select 	ce.filename,ce.lineno,ce.line,
+		ce.colname, ce.raw_field_value,
+		ce.err_message, ce.err_detail,ce.errorcode
+from 	public.copy_errors ce
+join	pg_class	pc	on pc.oid = ce.copy_destination
+where	pc.relname = 'copy_default_error_save';
+
+drop table copy_default_error_save;
+truncate copy_default;
\ No newline at end of file
-- 
2.34.1

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 20 Dec 2023 21:26:36 +0900

> Main content is to build spi query, execute the spi query, regress

Why do we need to use SPI? I think we can form heap tuples and insert
them to the error table. Creating the error table also doesn't need to
use SPI.

> foo.copy_errors will be owned by the schema: foo owner.

It seems that the error table is created when the SAVE_ERROR is used
for the first time. It probably blocks concurrent COPY FROM commands
with SAVE_ERROR option to different tables if the error table is not
created yet.

> This is done via row level security.

I don't think it works. If the user is dropped, the user's oid could
be reused for a different user.

Regards,

---

From: jian he <[email-masked]>
Date: Thu, 28 Dec 2023 11:57:00 +0800

> them to the error table. Creating the error table also doesn't need to
Thanks for pointing it out. I figured out how to form heap tuples and
insert them to the error table.
but I don't know how to create the error table without using SPI.
Please pointer it out.

> with SAVE_ERROR option to different tables if the error table is not
I don't know how to solve this problem.... Maybe we can document this.
but it will block the COPY FROM immediately.

> be reused for a different user.

You are right.
so I changed, now the schema owner will be the error table owner.
every error table tuple inserts,
I switch to schema owner, do the insert, then switch back to the
COPY_FROM operation user.
now everyone (except superuser) will need explicit grant to access the
error table.
From 8c8c266f1dc809ffa0ec9f4262bdd912ed6b758a Mon Sep 17 00:00:00 2001
From: pgaddict <[email-masked]>
Date: Wed, 27 Dec 2023 20:15:24 +0800
Subject: [PATCH v13 1/1] Make COPY FROM more error tolerant
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

We check the existing copy_errors table definition by column name and column data type.
if table already exists and meets the criteria then errors metadata will save to copy_errors.
if the table does not exist, then create one.

table copy_errors is per schema-wise, it's owned by the copy from
operation destination schema's owner.
The table owner has full privilege on copy_errors,
other non-superuser need gain privilege to access it.

Only works for COPY FROM, non-BINARY mode.
---
 doc/src/sgml/ref/copy.sgml               | 121 ++++++++++++-
 src/backend/commands/copy.c              |  12 ++
 src/backend/commands/copyfrom.c          | 133 +++++++++++++-
 src/backend/commands/copyfromparse.c     | 217 +++++++++++++++++++++--
 src/backend/parser/gram.y                |   8 +-
 src/bin/psql/tab-complete.c              |   3 +-
 src/include/commands/copy.h              |   1 +
 src/include/commands/copyfrom_internal.h |   6 +
 src/include/parser/kwlist.h              |   1 +
 src/test/regress/expected/copy2.out      | 137 ++++++++++++++
 src/test/regress/sql/copy2.sql           | 123 +++++++++++++
 11 files changed, 746 insertions(+), 16 deletions(-)

index 18ecc69c..1d0ff0b6 100644

+   <varlistentry>

    <para>
@@ -572,6 +586,18 @@ COPY <replaceable class="parameter">count</replaceable>
     null strings to null values and unquoted null strings to empty strings.
    </para>
 
+   <para>
+    Currenly only the owner can read and write data to table <literal>COPY_ERRORS</literal>.
 
  <refsect1>
@@ -588,7 +614,7 @@ COPY <replaceable class="parameter">count</replaceable>
 
@@ -962,6 +988,99 @@ versions of <productname>PostgreSQL</productname>.

 
index f4861652..a972ad87 100644
 
 /*
  * No more than this many tuples per CopyMultiInsertBuffer
@@ -655,7 +659,8 @@ CopyFrom(CopyFromState cstate)
 
 	Assert(cstate->rel);
@@ -992,6 +997,10 @@ CopyFrom(CopyFromState cstate)

 		/*
@@ -1297,6 +1306,20 @@ CopyFrom(CopyFromState cstate)

@@ -1444,6 +1467,114 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+		if (!HeapTupleIsValid(tuple))
+		cstate->copy_errors_owner = ownerId;
+		* that user.
+		cstate->copy_errors_owner = (Oid) 0;
index f5537345..f0849725 100644
@@ -58,18 +58,21 @@
  */
 #include "postgres.h"
 
+#include "access/heapam.h"
 #include <ctype.h>
 #include <unistd.h>
 #include <sys/stat.h>
-
+#include <catalog/namespace.h>
 #include "commands/copy.h"
 #include "commands/copyfrom_internal.h"
 #include "commands/progress.h"
 #include "executor/executor.h"
+#include "executor/spi.h"
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -880,16 +883,85 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		int			fldct;
 		int			fieldno;
 		char	   *string;
+		char		*errmsg_extra;
+		Oid			save_userid = InvalidOid;
+		int			save_sec_context = -1;
+		HeapTuple	copy_errors_tup;
+		Relation	copy_errorsrel;
+		TupleDesc	copy_errors_tupDesc;
+		Datum		t_values[10];
+		bool		t_isnull[10];

+		if (cstate->opts.save_error)
+		{
+			/*
+			* Open the copy_errors relation. we also need current userid for the later heap inserts.
+			*
+			*/
+			copy_errorsrel = table_open(RelnameGetRelid("copy_errors"), RowExclusiveLock);
+			copy_errors_tupDesc = copy_errorsrel->rd_att;
+			GetUserIdAndSecContext(&save_userid, &save_sec_context);
+		}
+
+		/* reset line_error_occured to false for next new line. */
+		if (cstate->line_error_occured)
+			cstate->line_error_occured = false;
+
 		/* check for overflowing fields */
 		if (attr_count > 0 && fldct > attr_count)
-			ereport(ERROR,
-					(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-					 errmsg("extra data after last expected column")));
+		{
+			if(cstate->opts.save_error)
+			{
+				errmsg_extra = pstrdup("extra data after last expected column");
+				t_values[0] = ObjectIdGetDatum(save_userid);
+				t_isnull[0] = false;
+				t_values[1] = ObjectIdGetDatum(cstate->rel->rd_rel->oid);
+				t_isnull[1] = false;
+				t_values[2] = CStringGetTextDatum(
+								cstate->filename ? cstate->filename : "STDIN");
+				t_isnull[2] = false;
+				t_values[3] = Int64GetDatum((long long) cstate->cur_lineno);
+				t_isnull[3] = false;
+				t_values[4] = CStringGetTextDatum(cstate->line_buf.data);
+				t_isnull[4] = false;
+				t_values[5] = (Datum) 0;
+				t_isnull[5] = true;
+				t_values[6] = (Datum) 0;
+				t_isnull[6] = true;
+				t_values[7] = CStringGetTextDatum(errmsg_extra);
+				t_isnull[7] = false;
+				t_values[8] = (Datum) 0;
+				t_isnull[8] = true;
+				t_values[9] = CStringGetTextDatum(
+							  unpack_sql_state(ERRCODE_BAD_COPY_FILE_FORMAT));
+				t_isnull[9] = false;
+
+				copy_errors_tup = heap_form_tuple(copy_errors_tupDesc,
+												 t_values,
+												 t_isnull);
+
+				/* using copy_errors owner do the simple_heap_insert */
+				SetUserIdAndSecContext(cstate->copy_errors_owner,
+									save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
+									SECURITY_NOFORCE_RLS);
+				simple_heap_insert(copy_errorsrel, copy_errors_tup);
+
+				/* Restore userid and security context */
+				SetUserIdAndSecContext(save_userid, save_sec_context);
+				cstate->line_error_occured = true;
+				cstate->error_rows_cnt++;
+				table_close(copy_errorsrel, RowExclusiveLock);
+				return true;
+			}
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						errmsg("extra data after last expected column")));
+		}
 
 		fieldno = 0;
 
@@ -901,10 +973,55 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 			Form_pg_attribute att = TupleDescAttr(tupDesc, m);
 
 			if (fieldno >= fldct)
-				ereport(ERROR,
-						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
-						 errmsg("missing data for column \"%s\"",
-								NameStr(att->attname))));
+			{
+				if(cstate->opts.save_error)
+				{
+					t_values[0] = ObjectIdGetDatum(save_userid);
+					t_isnull[0] = false;
+					t_values[1] = ObjectIdGetDatum(cstate->rel->rd_rel->oid);
+					t_isnull[1] = false;
+					t_values[2] = CStringGetTextDatum(cstate->filename ? cstate->filename : "STDIN");
+					t_isnull[2] = false;
+					t_values[3] = Int64GetDatum((long long) cstate->cur_lineno);
+					t_isnull[3] = false;
+					t_values[4] = CStringGetTextDatum(cstate->line_buf.data);
+					t_isnull[4] = false;
+					t_values[5] = (Datum) 0;
+					t_isnull[5] = true;
+					t_values[6] = (Datum) 0;
+					t_isnull[6] = true;
+					t_values[7] = CStringGetTextDatum(
+								psprintf("missing data for column \"%s\"", NameStr(att->attname)));
+					t_isnull[7] = false;
+					t_values[8] = (Datum) 0;
+					t_isnull[8] = true;
+					t_values[9] = CStringGetTextDatum(
+									unpack_sql_state(ERRCODE_BAD_COPY_FILE_FORMAT));
+					t_isnull[9] = false;
+
+					copy_errors_tup = heap_form_tuple(copy_errors_tupDesc,
+													t_values,
+													t_isnull);
+					/* using copy_errors owner do the simple_heap_insert */
+					SetUserIdAndSecContext(cstate->copy_errors_owner,
+										save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
+										SECURITY_NOFORCE_RLS);
+					simple_heap_insert(copy_errorsrel, copy_errors_tup);
+
+					/* Restore userid and security context */
+					SetUserIdAndSecContext(save_userid, save_sec_context);
+					cstate->line_error_occured = true;
+					cstate->error_rows_cnt++;
+					table_close(copy_errorsrel, RowExclusiveLock);
+					return true;
+				}
+				else
+					ereport(ERROR,
+							(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+							 errmsg("missing data for column \"%s\"",
+									NameStr(att->attname))));
+			}
+
 			string = field_strings[fieldno++];
 
@@ -956,15 +1073,91 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
+						char	*err_code;
+						err_code = pstrdup(unpack_sql_state(cstate->escontext->error_data->sqlerrcode));
 
+						if (!cstate->escontext->error_data->detail)
+							err_detail = NULL;
+						else
+							err_detail = cstate->escontext->error_data->detail;
+
+						t_values[0] = ObjectIdGetDatum(save_userid);
+						t_isnull[0] = false;
+						t_values[1] = ObjectIdGetDatum(cstate->rel->rd_rel->oid);
+						t_isnull[1] = false;
+						t_values[2] = CStringGetTextDatum(cstate->filename ? cstate->filename : "STDIN");
+						t_isnull[2] = false;
+						t_values[3] = Int64GetDatum((long long) cstate->cur_lineno);
+						t_isnull[3] = false;
+						t_values[4] = CStringGetTextDatum(cstate->line_buf.data);
+						t_isnull[4] = false;
+						t_values[5] = CStringGetTextDatum(cstate->cur_attname);
+						t_isnull[5] = false;
+						t_values[6] = CStringGetTextDatum(string);
+						t_isnull[6] = false;
+						t_values[7] = CStringGetTextDatum(cstate->escontext->error_data->message);
+						t_isnull[7] = false;
+						t_values[8] = err_detail ? CStringGetTextDatum(err_detail) : (Datum) 0;
+						t_isnull[8] = err_detail ? false: true;
+						t_values[9] = CStringGetTextDatum(err_code);
+						t_isnull[9] = false;
+
+						copy_errors_tup = heap_form_tuple(copy_errors_tupDesc,
+														t_values,
+														t_isnull);
+						/* using copy_errors owner do the simple_heap_insert */
+						SetUserIdAndSecContext(cstate->copy_errors_owner,
+											save_sec_context | SECURITY_LOCAL_USERID_CHANGE |
+											SECURITY_NOFORCE_RLS);
+
+						simple_heap_insert(copy_errorsrel, copy_errors_tup);
+
+						/* Restore userid and security context */
+						SetUserIdAndSecContext(save_userid, save_sec_context);
+
+						/* line error occured, set it once per line */
+						if (!cstate->line_error_occured)
+							cstate->line_error_occured = true;
+						/* reset ErrorSaveContext */
+						cstate->escontext->error_occurred = false;
+						cstate->escontext->details_wanted = true;
+						memset(cstate->escontext->error_data,0, sizeof(ErrorData));
+					}
+				}
+			}
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
 
+			table_close(copy_errorsrel, RowExclusiveLock);

 
index f2cca0b9..aa560dbb 100644
index 5ec41589..2c3b7b42 100644

 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,11 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	Oid		   copy_errors_owner;	/* the owner of copy_errors table */
+	ErrorSaveContext *escontext; 	/* soft error trapper during in_functions execution */
+	uint64		error_rows_cnt; /* total number of rows that have errors */
+	const char 	*copy_errors_nspname;		/* the copy_errors's namespace */
+	bool	 	line_error_occured;	/* does this line conversion error happened */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa..d0988a4c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -377,6 +377,7 @@ PG_KEYWORD("routines", ROUTINES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("row", ROW, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("save_error", SAVE_ERROR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("savepoint", SAVEPOINT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("scalar", SCALAR, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("schema", SCHEMA, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index c4178b9c..a2c6bf5aa 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -564,6 +564,118 @@ ERROR:  conflicting or redundant options
 LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
                                                              ^
 ROLLBACK;
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT
+);
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+ERROR:  cannot specify SAVE_ERROR in BINARY mode
+-- redundant options not allowed.
+COPY save_error_csv FROM STDIN WITH (save_error, save_error off);
+ERROR:  conflicting or redundant options
+LINE 1: COPY save_error_csv FROM STDIN WITH (save_error, save_error ...
+                                                         ^
+create table COPY_ERRORS();
+--should fail. since table COPY_ERRORS already exists.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+ERROR:  table public.COPY_ERRORS already exists. cannot use it for COPY FROM error saving
+drop table COPY_ERRORS;
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+ a | b |  c   | b_null | b_empty 
+---+---+------+--------+---------
+ 2 |   | NULL | f      | t
+(1 row)
+
+DROP TABLE save_error_csv;
+-- save error with extra data and missing data some column.
+---normal data type conversion error case.
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+NOTICE:  10 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+select	pc.relname, ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+from	copy_errors ce	join pg_class pc on pc.oid = ce.copy_destination
+where 	pc.relname = 'check_ign_err';
+    relname    | filename | lineno |                    line                    | colname |     raw_field_value     |                           err_message                           |        err_detail         | errorcode 
+---------------+----------+--------+--------------------------------------------+---------+-------------------------+-----------------------------------------------------------------+---------------------------+-----------
+ check_ign_err | STDIN    |      1 | 1       {1}     1       1       extra      | NULL    | NULL                    | extra data after last expected column                           | NULL                      | 22P04
+ check_ign_err | STDIN    |      2 | 2                                          | NULL    | NULL                    | missing data for column "m"                                     | NULL                      | 22P04
+ check_ign_err | STDIN    |      3 | \n      {1}     1       \-                 | n       |                        +| invalid input syntax for type integer: "                       +| NULL                      | 22P02
+               |          |        |                                            |         |                         | "                                                               |                           | 
+ check_ign_err | STDIN    |      4 | a       {2}     2       \r                 | n       | a                       | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      5 | 3       {\3}    3333333333      \n         | m       | {\x03}                  | invalid input syntax for type integer: "\x03"                   | NULL                      | 22P02
+ check_ign_err | STDIN    |      6 | 0x11    {3,}    3333333333      \\.        | m       | {3,}                    | malformed array literal: "{3,}"                                 | Unexpected "}" character. | 22P02
+ check_ign_err | STDIN    |      7 | d       {3,1/}  3333333333      \\0        | n       | d                       | invalid input syntax for type integer: "d"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      7 | d       {3,1/}  3333333333      \\0        | m       | {3,1/}                  | invalid input syntax for type integer: "1/"                     | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | n       | e                       | invalid input syntax for type integer: "e"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | m       | {3,\x01}                | invalid input syntax for type integer: "\x01"                   | NULL                      | 22P02
+ check_ign_err | STDIN    |      8 | e       {3,\1}  -3323879289873933333333 \n | k       | -3323879289873933333333 | value "-3323879289873933333333" is out of range for type bigint | NULL                      | 22003
+ check_ign_err | STDIN    |      9 | f       {3,1}   3323879289873933333333  \r | n       | f                       | invalid input syntax for type integer: "f"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |      9 | f       {3,1}   3323879289873933333333  \r | k       | 3323879289873933333333  | value "3323879289873933333333" is out of range for type bigint  | NULL                      | 22003
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | n       | b                       | invalid input syntax for type integer: "b"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | m       | {a, 4}                  | invalid input syntax for type integer: "a"                      | NULL                      | 22P02
+ check_ign_err | STDIN    |     10 | b       {a, 4}  1.1     h                  | k       | 1.1                     | invalid input syntax for type bigint: "1.1"                     | NULL                      | 22P02
+(16 rows)
+
+DROP TABLE check_ign_err;
+truncate COPY_ERRORS;
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+begin;
+CREATE USER regress_user12;
+CREATE USER regress_user13;
+CREATE SCHEMA IF NOT EXISTS copy_errors_test AUTHORIZATION regress_user12;
+SET LOCAL search_path TO copy_errors_test;
+GRANT USAGE on schema copy_errors_test to regress_user12,regress_user13;
+GRANT CREATE on schema copy_errors_test to regress_user12;
+set role regress_user12;
+CREATE TABLE textrange_input(a public.textrange, b public.textrange, c public.textrange);
+GRANT insert on textrange_input to regress_user13;
+set role regress_user13;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table copy_errors_test.copy_errors
+SAVEPOINT s1;
+--should fail. no priviledge
+select * from copy_errors_test.copy_errors;
+ERROR:  permission denied for table copy_errors
+ROLLBACK to s1;
+set role regress_user12;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+NOTICE:  2 rows were skipped because of conversion error. Skipped rows saved to table copy_errors_test.copy_errors
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+     relname     |    rolname     | filename | lineno |            line            | colname | raw_field_value |                            err_message                            |                err_detail                | errorcode 
+-----------------+----------------+----------+--------+----------------------------+---------+-----------------+-------------------------------------------------------------------+------------------------------------------+-----------
+ textrange_input | regress_user13 | STDIN    |      1 | ,-[a\","z),[a","-inf)      | b       | -[a\,z)         | malformed range literal: "-[a\,z)"                                | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | regress_user13 | STDIN    |      1 | ,-[a\","z),[a","-inf)      | c       | [a,-inf)        | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ textrange_input | regress_user13 | STDIN    |      2 | (",a),(",",a),()",a);      | a       | (,a),(          | malformed range literal: "(,a),("                                 | Junk after right parenthesis or bracket. | 22P02
+ textrange_input | regress_user13 | STDIN    |      2 | (",a),(",",a),()",a);      | b       | ,a),()          | malformed range literal: ",a),()"                                 | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | regress_user13 | STDIN    |      2 | (",a),(",",a),()",a);      | c       | a);             | malformed range literal: "a);"                                    | Missing left parenthesis or bracket.     | 22P02
+ textrange_input | regress_user12 | STDIN    |      1 | (a",")),(]","a),(a","])    | a       | (a,))           | malformed range literal: "(a,))"                                  | Junk after right parenthesis or bracket. | 22P02
+ textrange_input | regress_user12 | STDIN    |      1 | (a",")),(]","a),(a","])    | b       | (],a)           | malformed range literal: "(],a)"                                  | Missing comma after lower bound.         | 22P02
+ textrange_input | regress_user12 | STDIN    |      1 | (a",")),(]","a),(a","])    | c       | (a,])           | malformed range literal: "(a,])"                                  | Junk after right parenthesis or bracket. | 22P02
+ textrange_input | regress_user12 | STDIN    |      2 | [z","a],[z","2],[(","",")] | a       | [z,a]           | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ textrange_input | regress_user12 | STDIN    |      2 | [z","a],[z","2],[(","",")] | b       | [z,2]           | range lower bound must be less than or equal to range upper bound | NULL                                     | 22000
+ textrange_input | regress_user12 | STDIN    |      2 | [z","a],[z","2],[(","",")] | c       | [(,",)]         | malformed range literal: "[(,",)]"                                | Unexpected end of input.                 | 22P02
+(11 rows)
+
+--owner allowed to drop the table.
+drop table copy_errors;
+--should fail. no priviledge
+select * from public.copy_errors;
+ERROR:  permission denied for table copy_errors
+ROLLBACK;
 \pset null ''
 -- test case with whole-row Var in a check constraint
 create table check_con_tbl (f1 int);
@@ -822,3 +934,28 @@ truncate copy_default;
 -- DEFAULT cannot be used in COPY TO
 copy (select 1 as test) TO stdout with (default '\D');
 ERROR:  COPY DEFAULT only available using COPY FROM
+-- DEFAULT WITH SAVE_ERROR.
+create table copy_default_error_save (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+copy copy_default_error_save from stdin with (save_error, default '\D');
+NOTICE:  3 rows were skipped because of conversion error. Skipped rows saved to table public.copy_errors
+select 	ce.filename,ce.lineno,ce.line,
+		ce.colname, ce.raw_field_value,
+		ce.err_message, ce.err_detail,ce.errorcode
+from 	public.copy_errors ce
+join	pg_class	pc	on pc.oid = ce.copy_destination
+where	pc.relname = 'copy_default_error_save'
+order	by lineno, colname;
+ filename | lineno |               line               | colname  | raw_field_value  |                         err_message                         | err_detail | errorcode 
+----------+--------+----------------------------------+----------+------------------+-------------------------------------------------------------+------------+-----------
+ STDIN    |      1 | k       value   '2022-07-04'     | id       | k                | invalid input syntax for type integer: "k"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | id       | z                | invalid input syntax for type integer: "z"                  |            | 22P02
+ STDIN    |      2 | z       \D      '2022-07-03ASKL' | ts_value | '2022-07-03ASKL' | invalid input syntax for type timestamp: "'2022-07-03ASKL'" |            | 22007
+ STDIN    |      3 | s       \D      \D               | id       | s                | invalid input syntax for type integer: "s"                  |            | 22P02
+(4 rows)
+
+drop table copy_default_error_save, copy_errors;
+truncate copy_default;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index a5486f60..a37986df 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -374,6 +374,106 @@ BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
 ROLLBACK;
 
+--
+-- tests for SAVE_ERROR option with force_not_null, force_null
+\pset null NULL
+CREATE TABLE save_error_csv(
+    a INT NOT NULL,
+    b TEXT NOT NULL,
+    c TEXT
+);
+
+--save_error not allowed in binary mode
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT binary);
+
+-- redundant options not allowed.
+COPY save_error_csv FROM STDIN WITH (save_error, save_error off);
+
+create table COPY_ERRORS();
+--should fail. since table COPY_ERRORS already exists.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error);
+
+drop table COPY_ERRORS;
+
+--with FORCE_NOT_NULL and FORCE_NULL.
+COPY save_error_csv (a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
+z,,""
+\0,,
+2,,
+\.
+
+SELECT *, b is null as b_null, b = '' as b_empty FROM save_error_csv;
+DROP TABLE save_error_csv;
+
+-- save error with extra data and missing data some column.
+---normal data type conversion error case.
+CREATE TABLE check_ign_err (n int, m int[], k bigint, l text);
+COPY check_ign_err FROM STDIN WITH (save_error);
+1	{1}	1	1	extra
+2
+\n	{1}	1	\-
+a	{2}	2	\r
+3	{\3}	3333333333	\n
+0x11	{3,}	3333333333	\\.
+d	{3,1/}	3333333333	\\0
+e	{3,\1}	-3323879289873933333333	\n
+f	{3,1}	3323879289873933333333	\r
+b	{a, 4}	1.1	h
+5	{5}	5	\\
+\.
+
+select	pc.relname, ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+from	copy_errors ce	join pg_class pc on pc.oid = ce.copy_destination
+where 	pc.relname = 'check_ign_err';
+
+DROP TABLE check_ign_err;
+truncate COPY_ERRORS;
+
+--(type textrange was already made in test_setup.sql)
+--using textrange doing test
+begin;
+CREATE USER regress_user12;
+CREATE USER regress_user13;
+CREATE SCHEMA IF NOT EXISTS copy_errors_test AUTHORIZATION regress_user12;
+SET LOCAL search_path TO copy_errors_test;
+
+GRANT USAGE on schema copy_errors_test to regress_user12,regress_user13;
+GRANT CREATE on schema copy_errors_test to regress_user12;
+set role regress_user12;
+CREATE TABLE textrange_input(a public.textrange, b public.textrange, c public.textrange);
+GRANT insert on textrange_input to regress_user13;
+
+set role regress_user13;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+,-[a\","z),[a","-inf)
+(",a),(",",a),()",a);
+\.
+
+SAVEPOINT s1;
+--should fail. no priviledge
+select * from copy_errors_test.copy_errors;
+
+ROLLBACK to s1;
+
+set role regress_user12;
+COPY textrange_input(a, b, c) FROM STDIN WITH (save_error,FORMAT csv, FORCE_NULL *);
+(a",")),(]","a),(a","])
+[z","a],[z","2],[(","",")]
+\.
+
+SELECT	pc.relname,pr.rolname,ce.filename,ce.lineno,ce.line,ce.colname,
+		ce.raw_field_value,ce.err_message,ce.err_detail,ce.errorcode
+FROM	copy_errors_test.copy_errors ce
+JOIN 	pg_class pc ON pc.oid = ce.copy_destination
+JOIN 	pg_roles pr ON pr.oid = ce.userid;
+
+--owner allowed to drop the table.
+drop table copy_errors;
+
+--should fail. no priviledge
+select * from public.copy_errors;
+ROLLBACK;
 \pset null ''
 
 -- test case with whole-row Var in a check constraint
@@ -609,3 +709,26 @@ truncate copy_default;
 
+where	pc.relname = 'copy_default_error_save'
+order	by lineno, colname;
+drop table copy_default_error_save, copy_errors;

---

From: vignesh C <[email-masked]>
Date: Thu, 4 Jan 2024 21:35:20 +0530

> now everyone (except superuser) will need explicit grant to access the

There are some compilation issues reported at [1] for the patch:
[04:04:26.288] copyfromparse.c: In function ‘NextCopyFrom’:
[04:04:26.288] copyfromparse.c:1126:25: error: ‘copy_errors_tupDesc’
may be used uninitialized in this function
[-Werror=maybe-uninitialized]
[04:04:26.288] 1126 | copy_errors_tup = heap_form_tuple(copy_errors_tupDesc,
[04:04:26.288] | ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[04:04:26.288] 1127 | t_values,
[04:04:26.288] | ~~~~~~~~~
[04:04:26.288] 1128 | t_isnull);
[04:04:26.288] | ~~~~~~~~~
[04:04:26.288] copyfromparse.c:1160:4: error: ‘copy_errorsrel’ may be
used uninitialized in this function [-Werror=maybe-uninitialized]
[04:04:26.288] 1160 | table_close(copy_errorsrel, RowExclusiveLock);
[04:04:26.288] | ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[1] - https://cirrus-ci.com/task/4785221183209472

Regards,
Vignesh

---

From: jian he <[email-masked]>
Date: Fri, 5 Jan 2024 16:37:32 +0800

> [04:04:26.288] 1126 | copy_errors_tup = heap_form_tuple(copy_errors_tupDesc,

I fixed this issue, and also improved the doc.
Other implementations have not changed.

---

From: jian he <[email-masked]>
Date: Sat, 6 Jan 2024 08:50:00 +0800

> Other implementations have not changed.

bother again.
This time, I used the ci test it again.
now there should be no warning.

---

From: torikoshia <[email-masked]>
Date: Tue, 09 Jan 2024 23:36:37 +0900

> and committers to review/commit them.

Jian, how do you think this comment?

Looking back at the discussion so far, it seems that not everyone thinks 
saving table information is the best idea[1] and some people think just 
skipping error data is useful.[2]

Since there are issues to be considered from the design such as 
physical/logical replication treatment, putting error information to 
table is likely to take time for consensus building and development.

Wouldn't it be better to follow the following advice and develop the 
functionality incrementally?

On Fri, Dec 15, 2023 at 4:49 AM Masahiko Sawada 
> be to support logging destinations such as server logs and tables.

Attached a patch for this "first step" with reference to v7 patch, which 
logged errors and simpler than latest one.
- This patch adds new option SAVE_ERROR_TO, but currently only supports 
'none', which means just skips error data. It is expected to support 
'log' and 'table'.
- This patch Skips just soft errors and don't handle other errors such 
as missing column data.

> +               * InputFunctionCall is more faster than 

Have you measured this?
When I tested it in an older patch, there were no big difference[3].

  > +   SAVEPOINT SAVE_ERROR SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P 
SECURITY SELECT

There was a comment that we shouldn't add new keyword for this[4].

I left as it was in v7 patch regarding these points.

[1] 
https://www.postgresql.org/message-id/20231109002600.fuihn34bjqqgmbjm%40awork3.anarazel.de
[2] 
https://www.postgresql.org/message-id/CAD21AoCeEOBN49fu43e6tBTynnswugA3oZ5AZvLeyDCpxpCXPg%40mail.gmail.com
[3] 
https://www.postgresql.org/message-id/19551e8c2717c24689913083f841ddb5%40oss.nttdata.com
[4] 
https://www.postgresql.org/message-id/20230322175000.qbdctk7bnmifh5an%40awork3.anarazel.de

-- 
Regards,

From 675b8b8408e23f22940a99b40cb7ec3e1b36cac3 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Tue, 9 Jan 2024 23:10:14 +0900
Subject: [PATCH v1] Add new COPY option SAVE_ERROR_TO

Currently when source data contains unexpected data regarding data type or
range, entire COPY fails. However, in some cases such data can be ignored and
just copying normal data is preferable.

This patch adds a new option SAVE_ERROR_TO, which specifies where to save the
error information. When this option is specified, COPY skips soft errors and
continues copying data.

Currently SAVE_ERROR_TO only supports 'none'. This indicates error information
is not saved and COPY just skips the unexpected data and continues running.

Later works are expected to add more choices, such as 'log' and 'table'.

Author: Damir Belyalov, Atsushi Torikoshi, referenced with jian he's patch.
---
 doc/src/sgml/ref/copy.sgml               | 20 +++++++++++++-
 src/backend/commands/copy.c              | 19 ++++++++++++++
 src/backend/commands/copyfrom.c          | 33 ++++++++++++++++++++++++
 src/backend/commands/copyfromparse.c     | 16 +++++++++---
 src/bin/psql/tab-complete.c              |  7 ++++-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  3 +++
 src/test/regress/expected/copy2.out      | 28 ++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 27 +++++++++++++++++++
 9 files changed, 148 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 18ecc69c33..87f2b3e7a2 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
+    SAVE_ERROR_TO '<replaceable class="parameter">location</replaceable>'
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
 </synopsis>
  </refsynopsisdiv>
@@ -373,6 +374,22 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>SAVE_ERROR_TO</literal></term>
+    <listitem>
+     <para>
+      Specifies where to save error information when there are malformed data in
+      the input. If this option is specified, <command>COPY</command> skips
+      malformed data and continues copying data.
+      Currently only <literal>none</literal> is supported.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+      Note that this is only supported in current <command>COPY</command>
+      syntax.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>ENCODING</literal></term>
     <listitem>
@@ -556,7 +573,8 @@ COPY <replaceable class="parameter">count</replaceable>
    </para>
 
    <para>
-    <command>COPY</command> stops operation at the first error. This
+    <command>COPY</command> stops operation at the first error when
+    <literal>SAVE_ERROR_TO</literal> is not specified. This
     should not lead to problems in the event of a <command>COPY
     TO</command>, but the target table will already have received
     earlier rows in a <command>COPY FROM</command>. These rows will not
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index fe4cf957d7..5e5e8a5f34 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -571,6 +571,20 @@ ProcessCopyOptions(ParseState *pstate,
 								defel->defname),
 						 parser_errposition(pstate, defel->location)));
 		}
+		else if (strcmp(defel->defname, "save_error_to") == 0)
+		{
+			char	   *location = defGetString(defel);
+
+			if (opts_out->save_error_to)
+				errorConflictingDefElem(defel, pstate);
+			else if (strcmp(location, "none") == 0)
+				opts_out->save_error_to = location;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("COPY save_error_to \"%s\" not recognized", location),
+						 parser_errposition(pstate, defel->location)));
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -598,6 +612,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify DEFAULT in BINARY mode")));
 
+	if (opts_out->binary && opts_out->save_error_to)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify SAVE_ERROR_TO in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 37836a769c..d909123cd1 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -42,6 +42,7 @@
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "optimizer/optimizer.h"
 #include "pgstat.h"
 #include "rewrite/rewriteHandler.h"
@@ -752,6 +753,14 @@ CopyFrom(CopyFromState cstate)
 		ti_options |= TABLE_INSERT_FROZEN;
 	}
 
+	/* Set up soft error handler for SAVE_ERROR_TO */
+	if (cstate->opts.save_error_to)
+	{
+		ErrorSaveContext escontext = {T_ErrorSaveContext};
+		escontext.details_wanted = true;
+		cstate->escontext = escontext;
+	}
+
 	/*
 	 * We need a ResultRelInfo so we can use the regular executor's
 	 * index-entry-making machinery.  (There used to be a huge amount of code
@@ -992,6 +1001,25 @@ CopyFrom(CopyFromState cstate)
 		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
 			break;
 
+		/*
+		 * Soft error occured, skip this tuple and save error information
+		 * according to SAVE_ERROR_TO.
+		 */
+		if (cstate->escontext.error_occurred)
+		{
+			ErrorSaveContext new_escontext = {T_ErrorSaveContext};
+
+			/* Currently only "none" is supported */
+			Assert(strcmp(cstate->opts.save_error_to, "none") == 0);
+
+			ExecClearTuple(myslot);
+
+			new_escontext.details_wanted = true;
+			cstate->escontext = new_escontext;
+
+			continue;
+		}
+
 		ExecStoreVirtualTuple(myslot);
 
 		/*
@@ -1281,6 +1309,11 @@ CopyFrom(CopyFromState cstate)
 			CopyMultiInsertInfoFlush(&multiInsertInfo, NULL, &processed);
 	}
 
+	if (cstate->opts.save_error_to && cstate->num_errors > 0)
+		ereport(WARNING,
+				errmsg("%zd rows were skipped due to data type incompatibility",
+					   cstate->num_errors));
+
 	/* Done, clean up */
 	error_context_stack = errcallback.previous;
 
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index af4c36f645..0dd49d85e6 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -70,6 +70,7 @@
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -956,10 +957,17 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
 			}
 			else
-				values[m] = InputFunctionCall(&in_functions[m],
-											  string,
-											  typioparams[m],
-											  att->atttypmod);
+				/* If SAVE_ERROR_TO is specified, skip rows with soft errors */
+				if (!InputFunctionCallSafe(&in_functions[m],
+										   string,
+										   typioparams[m],
+										   att->atttypmod,
+										   (Node *) &cstate->escontext,
+										   &values[m]))
+				{
+					cstate->num_errors++;
+					return true;
+				}
 
index 09914165e4..efe2b7cc10 100644
@@ -2898,12 +2898,17 @@ psql_completion(const char *text, int start, int end)
+					  "SAVE_ERROR_TO");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
 		COMPLETE_WITH("binary", "csv", "text");
 
+	/* Complete COPY <sth> FROM filename WITH (SAVE_ERROR_TO */
+	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "SAVE_ERROR_TO"))
+		COMPLETE_WITH("none");
+
 	/* Complete COPY <sth> FROM <sth> WITH (<options>) */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM", MatchAny, "WITH", MatchAny))
 		COMPLETE_WITH("WHERE");
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index e6c1867a2f..f890b66f26 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -62,6 +62,7 @@ typedef struct CopyFormatOptions
 	bool		force_null_all; /* FORCE_NULL *? */
 	bool	   *force_null_flags;	/* per-column CSV FN flags */
 	bool		convert_selectively;	/* do selective binary conversion? */
+	char	   *save_error_to;	/* where to save error information */
 	List	   *convert_select; /* list of column names (can be NIL) */
 } CopyFormatOptions;
 
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 715939a907..e2a8f9dd6e 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@

 /*
  * Represents the different source cases we need to worry about at
@@ -94,6 +95,8 @@ typedef struct CopyFromStateData
 								 * default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
+	ErrorSaveContext escontext; /* soft error trapper during in_functions execution */
+	uint64		num_errors; 	/* total number of rows which contained soft errors */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index c4178b9c07..4a1777a4fa 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -82,6 +82,8 @@ COPY x to stdin (format BINARY, delimiter ',');
 ERROR:  cannot specify DELIMITER in BINARY mode
 COPY x to stdin (format BINARY, null 'x');
 ERROR:  cannot specify NULL in BINARY mode
+COPY x to stdin (format BINARY, save_error_to none);
+ERROR:  cannot specify SAVE_ERROR_TO in BINARY mode
 COPY x to stdin (format TEXT, force_quote(a));
 ERROR:  COPY FORCE_QUOTE requires CSV mode
 COPY x from stdin (format CSV, force_quote(a));
@@ -94,6 +96,10 @@ COPY x to stdout (format TEXT, force_null(a));
 ERROR:  COPY FORCE_NULL requires CSV mode
 COPY x to stdin (format CSV, force_null(a));
 ERROR:  COPY FORCE_NULL cannot be used with COPY TO
+COPY x to stdin (format BINARY, save_error_to unsupported);
+ERROR:  COPY save_error_to "unsupported" not recognized
+LINE 1: COPY x to stdin (format BINARY, save_error_to unsupported);
+                                        ^
 -- too many columns in column list: should fail
 COPY x (a, b, c, d, e, d, c) from stdin;
 ERROR:  column "d" specified more than once
@@ -710,6 +716,26 @@ SELECT * FROM instead_of_insert_tbl;
 (2 rows)
 
 COMMIT;
+-- tests for SAVE_ERROR_TO option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH (save_error_to none);
+WARNING:  4 rows were skipped due to data type incompatibility
+SELECT * FROM check_ign_err;
+ n |  m  | k 
+---+-----+---
+ 1 | {1} | 1
+ 5 | {5} | 5
+(2 rows)
+
+-- test datatype error that can't be handled as soft: should fail
+CREATE TABLE hard_err(foo widget);
+COPY hard_err FROM STDIN WITH (save_error_to none);
+ERROR:  invalid input syntax for type widget: "1"
+CONTEXT:  COPY hard_err, line 1, column foo: "1"
+-- test missing data: should fail
+COPY check_ign_err FROM STDIN WITH (save_error_to none);
+ERROR:  missing data for column "k"
+CONTEXT:  COPY check_ign_err, line 1: "1	{1}"
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -724,6 +750,8 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+DROP TABLE check_ign_err;
+DROP TABLE hard_err;
 --
 -- COPY FROM ... DEFAULT
 --
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index a5486f6086..17c5764b42 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -70,12 +70,14 @@ COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
 -- incorrect options
 COPY x to stdin (format BINARY, delimiter ',');
 COPY x to stdin (format BINARY, null 'x');
+COPY x to stdin (format BINARY, save_error_to none);
 COPY x to stdin (format TEXT, force_quote(a));
 COPY x from stdin (format CSV, force_quote(a));
 COPY x to stdout (format TEXT, force_not_null(a));
 COPY x to stdin (format CSV, force_not_null(a));
 COPY x to stdout (format TEXT, force_null(a));
 COPY x to stdin (format CSV, force_null(a));
+COPY x to stdin (format BINARY, save_error_to unsupported);
 
 -- too many columns in column list: should fail
 COPY x (a, b, c, d, e, d, c) from stdin;
@@ -494,6 +496,29 @@ test1
 SELECT * FROM instead_of_insert_tbl;
 COMMIT;
 
+-- tests for SAVE_ERROR_TO option
+CREATE TABLE check_ign_err (n int, m int[], k int);
+COPY check_ign_err FROM STDIN WITH (save_error_to none);
+1	{1}	1
+a	{2}	2
+3	{3}	3333333333
+4	{a, 4}	4
+
+5	{5}	5
+\.
+SELECT * FROM check_ign_err;
+
+-- test datatype error that can't be handled as soft: should fail
+CREATE TABLE hard_err(foo widget);
+COPY hard_err FROM STDIN WITH (save_error_to none);
+1
+\.
+
+-- test missing data: should fail
+COPY check_ign_err FROM STDIN WITH (save_error_to none);
+1	{1}
+\.
+
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -508,6 +533,8 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+DROP TABLE check_ign_err;
+DROP TABLE hard_err;
 
 --
 -- COPY FROM ... DEFAULT

base-commit: d596736a499858de800cabb241c0107c978f1b95
-- 
2.39.2

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 10 Jan 2024 16:42:07 +0900

> functionality incrementally?

Yeah, I'm still thinking it's better to implement this feature
incrementally. Given we're closing to feature freeze, I think it's
unlikely to get the whole feature into PG17 since there are still many
design discussions we need in addition to what Torikoshi-san pointed
out. The feature like "ignore errors" or "logging errors" would have
higher possibilities. Even if we get only these parts of the whole
"error table" feature into PG17, it will make it much easier to
implement "error tables" feature.

> - This patch Skips just soft errors and don't handle other errors such

Seems promising. I'll look at the patch.

Regards,

---

From: jian he <[email-masked]>
Date: Thu, 11 Jan 2024 11:13:35 +0800

> - This patch Skips just soft errors and don't handle other errors such

Hi.
I made the following change based on your patch
(v1-0001-Add-new-COPY-option-SAVE_ERROR_TO.patch)

* when specified SAVE_ERROR_TO, move the initialization of
ErrorSaveContext to the function BeginCopyFrom.
I think that's the right place to initialize struct CopyFromState field.
* I think your patch when there are N rows have malformed data, then it
will initialize N ErrorSaveContext.
In the struct CopyFromStateData, I changed it to ErrorSaveContext *escontext.
So if an error occurred, you can just set the escontext accordingly.
* doc: mention "If this option is omitted, <command>COPY</command>
stops operation at the first error."
* Since we only support 'none' for now, 'none' means we don't want
ErrorSaveContext metadata,
 so we should set cstate->escontext->details_wanted to false.

> When I tested it in an older patch, there were no big difference[3].
Thanks for pointing it out, I probably was over thinking.

> There was a comment that we shouldn't add new keyword for this[4].
Thanks for pointing it out.

---

From: torikoshia <[email-masked]>
Date: Fri, 12 Jan 2024 11:58:44 +0900

+1.
I'm also going to make patch for "logging errors", since this 
functionality is isolated from v7 patch.

> Seems promising. I'll look at the patch.
Thanks a lot!
Sorry to attach v2 if you already reviewed v1..

On 2024-01-11 12:13, jian he wrote:
>>   > -   SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P 
>> SECURITY
>> SELECT
>>   > +   SAVEPOINT SAVE_ERROR SCALAR SCHEMA SCHEMAS SCROLL SEARCH 
>> SECOND_P
>> SECURITY SELECT
>> 

Thanks for reviewing!

Updated the patch merging your suggestions except below points:

> +   cstate->num_errors = 0;

Since cstate is already initialized in below lines, this may be 
redundant.

|     /* Allocate workspace and zero all fields */
|     cstate = (CopyFromStateData *) palloc0(sizeof(CopyFromStateData));

>  +                   Assert(!cstate->escontext->details_wanted);

I'm not sure this is necessary, considering we're going to add other 
options like 'table' and 'log', which need details_wanted soon.

-- 
Regards,

From a3f14a0e7e9a7b5fb961ad6b6b7b163cf6534a26 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 12 Jan 2024 11:32:00 +0900
Subject: [PATCH v2] Add new COPY option SAVE_ERROR_TO

continues copying.

Currently SAVE_ERROR_TO only supports "none". This indicates error information
is not saved and COPY just skips the unexpected data and continues running.

Author: Damir Belyalov, Atsushi Torikoshi, referenced with jian he's patch.
---
 doc/src/sgml/ref/copy.sgml               | 21 +++++++++++-
 src/backend/commands/copy.c              | 19 +++++++++++
 src/backend/commands/copyfrom.c          | 43 ++++++++++++++++++++++++
 src/backend/commands/copyfromparse.c     | 17 +++++++---
 src/bin/psql/tab-complete.c              |  7 +++-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  3 ++
 src/test/regress/expected/copy2.out      | 28 +++++++++++++++
 src/test/regress/sql/copy2.sql           | 27 +++++++++++++++
 9 files changed, 159 insertions(+), 7 deletions(-)

index 18ecc69c33..71941c4ee5 100644
@@ -373,6 +374,23 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>SAVE_ERROR_TO</literal></term>
+    <listitem>
+     <para>
+      Specifies save error information to <replaceable class="parameter">
+      location</replaceable> when there are malformed data in the input.
+      If this option is specified, <command>COPY</command> skips malformed data
+      and continues copying data.
+      Currently only <literal>none</literal> is supported.
+      If this option is omitted, <command>COPY</command> stops operation at the
+      first error.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>ENCODING</literal></term>
     <listitem>
@@ -556,7 +574,8 @@ COPY <replaceable class="parameter">count</replaceable>
    </para>
 
    <para>
 
index 37836a769c..48484a2597 100644
@@ -656,6 +657,9 @@ CopyFrom(CopyFromState cstate)
 
@@ -992,6 +996,26 @@ CopyFrom(CopyFromState cstate)
 
+		if (cstate->opts.save_error_to && cstate->escontext->error_occurred)
+		{
+			/*
+			 * Soft error occured, skip this tuple and save error information
+			 * according to SAVE_ERROR_TO.
+			 */
+			if (strcmp(cstate->opts.save_error_to, "none") == 0)
+				/*
+				 * Just make ErrorSaveContext ready for the next NextCopyFrom.
+				 * Since we don't set details_wanted and error_data is not to be
+				 * filled, just resetting error_occurred is enough.
+				 */
+				cstate->escontext->error_occurred = false;
+			else
+				elog(ERROR, "unexpected SAVE_ERROR_TO location : %s",
+						cstate->opts.save_error_to);
+
+			continue;
+		}
+
 		ExecStoreVirtualTuple(myslot);
 
 		/*
@@ -1281,6 +1305,11 @@ CopyFrom(CopyFromState cstate)
 			CopyMultiInsertInfoFlush(&multiInsertInfo, NULL, &processed);
 	}

@@ -1419,6 +1448,20 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
+	/* Set up soft error handler for SAVE_ERROR_TO */
+	if (cstate->opts.save_error_to)
+	{
+		cstate->escontext = makeNode(ErrorSaveContext);
+		cstate->escontext->type = T_ErrorSaveContext;
+		cstate->escontext->error_occurred = false;
+
+		 /* Currently we only support "none". We'll add other options later */
+		if (strcmp(cstate->opts.save_error_to, "none") == 0)
+			cstate->escontext->details_wanted = false;
+	}
+	else
+		cstate->escontext = NULL;
+
 	/* Convert FORCE_NULL name list to per-column flags, check validity */
 	cstate->opts.force_null_flags = (bool *) palloc0(num_phys_attrs * sizeof(bool));
 	if (cstate->opts.force_null_all)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index af4c36f645..7041815dee 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -70,6 +70,7 @@
 #include "libpq/pqformat.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/miscnodes.h"
 #include "pgstat.h"
 #include "port/pg_bswap.h"
 #include "utils/builtins.h"
@@ -955,11 +956,17 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
-			else
+			else if (!InputFunctionCallSafe(&in_functions[m],

 
 
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 715939a907..3744fac017 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@

 /*
 
 COMMIT;

 
 --
 -- COPY FROM ... DEFAULT

base-commit: 08c3ad27eb5348d0cbffa843a3edb11534f9904a
-- 
2.39.2

---

From: jian he <[email-masked]>
Date: Sat, 13 Jan 2024 22:19:35 +0800

> options like 'table' and 'log', which need details_wanted soon.

make save_error_to option cannot be used with COPY TO.
add redundant test, save_error_to with COPY TO test.

---

From: Alexander Korotkov <[email-masked]>
Date: Sun, 14 Jan 2024 03:30:32 +0200

Hi!

I think this is a demanding and long-waited feature.  The thread is
pretty long, but mostly it was disputes about how to save the errors.
The present patch includes basic infrastructure and ability to ignore
errors, thus it's pretty simple.

> add redundant test, save_error_to with COPY TO test.

I've incorporated these changes.  Also, I've changed
CopyFormatOptions.save_error_to to enum and made some edits in
comments and the commit message.  I'm going to push this if there are
no objections.

------
Regards,
Alexander Korotkov

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 15 Jan 2024 05:34:25 +0900

> comments and the commit message.  I'm going to push this if there are

Thank you for updating the patch. Here are two comments:

---
+   if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_UNSPECIFIED &&
+       cstate->num_errors > 0)
+       ereport(WARNING,
+               errmsg("%zd rows were skipped due to data type incompatibility",
+                      cstate->num_errors));
+
    /* Done, clean up */
    error_context_stack = errcallback.previous;

If a malformed input is not the last data, the context message seems odd:

postgres(1:1769258)=# create table test (a int);
CREATE TABLE
postgres(1:1769258)=# copy test from stdin (save_error_to none);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> a
>> 1
2024-01-15 05:05:53.980 JST [1769258] WARNING:  1 rows were skipped
due to data type incompatibility
2024-01-15 05:05:53.980 JST [1769258] CONTEXT:  COPY test, line 3: ""
COPY 1

I think it's better to report the WARNING after resetting the
error_context_stack. Or is a WARNING really appropriate here? The
v15-0001-Make-COPY-FROM-more-error-tolerant.patch[1] uses NOTICE but
the v1-0001-Add-new-COPY-option-SAVE_ERROR_TO.patch[2] changes it to
WARNING without explanation.

---

We might want to cover the extra data cases too.

Regards,

[1] https://www.postgresql.org/message-id/CACJufxEkkqnozdnvNMGxVAA94KZaCPkYw_Cx4JKG9ueNaZma_A%40mail.gmail.com
[2] https://www.postgresql.org/message-id/3d0b349ddbd4ae5f605f77b491697158%40oss.nttdata.com

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 15 Jan 2024 01:21:07 +0200

> WARNING without explanation.

Thank you for noticing this.  I think NOTICE is more appropriate here.
There is nothing to "worry" about: the user asked to ignore the errors
and we did.  And yes, it doesn't make sense to use the last line as
the context.  Fixed.

> We might want to cover the extra data cases too.

Agreed, the relevant test is added.

------
Regards,
Alexander Korotkov

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 15 Jan 2024 15:43:46 +0900

> Agreed, the relevant test is added.

Thank you for updating the patch. I have one minor point:

+       if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_UNSPECIFIED &&
+               cstate->num_errors > 0)
+               ereport(NOTICE,
+                               errmsg("%zd rows were skipped due to
data type incompatibility",
+                                          cstate->num_errors));
+

We can use errmsg_plural() instead.

I have a question about the option values; do you think we need to
have another value of SAVE_ERROR_TO option to explicitly specify the
current default behavior, i.e. not accept any error? With the v4
patch, the user needs to omit SAVE_ERROR_TO option to accept errors
during COPY FROM. If we change the default behavior in the future,
many users will be affected and probably end up changing their
applications to keep the current default behavior.

Regards,

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 15 Jan 2024 17:17:33 +0200

> We can use errmsg_plural() instead.

Makes sense.  Fixed.

> applications to keep the current default behavior.

Valid point.  I've implemented the handling of CopySaveErrorToChoice
in a similar way to CopyHeaderChoice.

Please, check the revised patch attached.

------
Regards,
Alexander Korotkov

---

From: torikoshia <[email-masked]>
Date: Tue, 16 Jan 2024 09:27:26 +0900

> Please, check the revised patch attached.

Thanks for updating the patch!

Here is a minor comment:

> +/*
> + * Extract a defGetCopySaveErrorToChoice value from a DefElem.
> + */

Should be Extract a "CopySaveErrorToChoice"?

BTW I'm thinking we should add a column to pg_stat_progress_copy that 
counts soft errors. I'll suggest this in another thread.

-- 
Regards,

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 16 Jan 2024 17:08:45 +0200

Hi,

> Thanks for updating the patch!

You're welcome!

> Should be Extract a "CopySaveErrorToChoice"?

Fixed.

> counts soft errors. I'll suggest this in another thread.

Please do!

------
Regards,
Alexander Korotkov

---

From: torikoshia <[email-masked]>
Date: Wed, 17 Jan 2024 14:38:54 +0900

Hi,

Thanks for applying!

> +                               errmsg_plural("%zd row were skipped due 
> to data type incompatibility",

Sorry, I just noticed it, but 'were' should be 'was' here?

>> counts soft errors. I'll suggest this in another thread.

I've started it here:

https://www.postgresql.org/message-id/[email-masked]

-- 
Regards,

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Wed, 17 Jan 2024 16:48:59 +0900 (JST)

At Wed, 17 Jan 2024 14:38:54 +0900, torikoshia <[email-masked]> wrote in 
> Hi,
> 
> Thanks for applying!
> 
> > + errmsg_plural("%zd row were skipped due to data type
> > incompatibility",
> 

Switching topics, this commit (9e2d870119) adds the following help message:

>                       "COPY { %s [ ( %s [, ...] ) ] | ( %s ) }\n"
>                       "    TO { '%s' | PROGRAM '%s' | STDOUT }\n"
> ...
>                       "    SAVE_ERROR_TO '%s'\n"
> ...
>                       _("location"),

On the other hand, SAVE_ERROR_TO takes 'error' or 'none', which
indicate "immediately error out" and 'just ignore the failure'
respectively, but these options hardly seem to denote a 'location',
and appear more like an 'action'. I somewhat suspect that this
parameter name intially conceived with the assupmtion that it would
take file names or similar parameters. I'm not sure if others will
agree, but I think the parameter name might not be the best
choice. For instance, considering the addition of the third value
'log', something like on_error_action (error, ignore, log) would be
more intuitively understandable. What do you think?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 17 Jan 2024 23:01:59 +0200

> Sorry, I just noticed it, but 'were' should be 'was' here?

Sure, the fix is pushed.

------
Regards,
Alexander Korotkov

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 17 Jan 2024 23:06:56 +0200

> more intuitively understandable. What do you think?

Probably, but I'm not sure about that.  The name SAVE_ERROR_TO assumes
the next word will be location, not action.  With some stretch we can
assume 'error' to be location.  I think it would be even more stretchy
to think that SAVE_ERROR_TO is followed by action.  Probably, we can
replace SAVE_ERROR_TO with another name which could be naturally
followed by action, but I don't have something appropriate in mind.
However, I'm not native english speaker and certainly could miss
something.

------
Regards,
Alexander Korotkov

---

From: Tom Lane <[email-masked]>
Date: Wed, 17 Jan 2024 16:37:57 -0500

> to think that SAVE_ERROR_TO is followed by action.

The other problem with this terminology is that with 'none', what it
is doing is the exact opposite of "saving" the errors.  I agree we
need a better name.

Kyotaro-san's suggestion isn't bad, though I might shorten it to
error_action {error|ignore|log} (or perhaps "stop" instead of "error")?
You will need a separate parameter anyway to specify the destination
of "log", unless "none" became an illegal table name when I wasn't
looking.  I don't buy that one parameter that has some special values
while other values could be names will be a good design.  Moreover,
what if we want to support (say) log-to-file along with log-to-table?
Trying to distinguish a file name from a table name without any other
context seems impossible.

			regards, tom lane

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 18 Jan 2024 09:56:39 +0900

> is doing is the exact opposite of "saving" the errors.  I agree we

Agreed.

> context seems impossible.

I've been thinking we can add more values to this option to log errors
not only to the server logs but also to the error table (not sure
details but I imagined an error table is created for each table on
error), without an additional option for the destination name. The
values would be like error_action {error|ignore|save-logs|save-table}.

Regards,

---

From: jian he <[email-masked]>
Date: Thu, 18 Jan 2024 09:10:51 +0800

> values would be like error_action {error|ignore|save-logs|save-table}.

another idea:
on_error {error|ignore|other_future_option}
if not specified then by default ERROR.
You can also specify ERROR or IGNORE for now.

I agree, the parameter "error_action" is better than "location".

---

From: torikoshia <[email-masked]>
Date: Thu, 18 Jan 2024 11:15:47 +0900

> I agree, the parameter "error_action" is better than "location".

I'm not sure whether error_action or on_error is better, but either way 
"error_action error" and "on_error error" seems a bit odd to me.
I feel "stop" is better for both cases as Tom suggested.

-- 
Regards,

---

From: Alexander Korotkov <[email-masked]>
Date: Thu, 18 Jan 2024 09:59:20 +0200

> I feel "stop" is better for both cases as Tom suggested.

OK.  What about this?
on_error {stop|ignore|other_future_option}
where other_future_option might be compound like "file 'copy.log'" or
"table 'copy_log'".

------
Regards,
Alexander Korotkov

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 18 Jan 2024 09:01:24 +0100

čt 18. 1. 2024 v 8:59 odesílatel Alexander Korotkov <[email-masked]>
napsal:

> > > On Thu, Jan 18, 2024 at 8:57 AM Masahiko Sawada <[email-masked]
> >
> > > wrote:

+1

it is consistent with psql

Regards

Pavel

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 18 Jan 2024 17:33:16 +0900

+1

Regards,

---

From: torikoshia <[email-masked]>
Date: Thu, 18 Jan 2024 21:09:13 +0900

> where other_future_option might be compound like "file 'copy.log'" or

Thanks, also +1 from me.

-- 
Regards,

---

From: jian he <[email-masked]>
Date: Thu, 18 Jan 2024 22:59:44 +0800

Hi.
patch refactored based on "on_error {stop|ignore}"
doc changes:

--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,7 +43,7 @@ COPY { <replaceable
class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable
class="parameter">column_name</replaceable> [, ...] ) | * }
-    SAVE_ERROR_TO '<replaceable class="parameter">location</replaceable>'
+    ON_ERROR '<replaceable class="parameter">error_action</replaceable>'
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
 </synopsis>
  </refsynopsisdiv>
@@ -375,20 +375,20 @@ COPY { <replaceable
class="parameter">table_name</replaceable> [ ( <replaceable
    </varlistentry>

    <varlistentry>
-    <term><literal>SAVE_ERROR_TO</literal></term>
+    <term><literal>ON_ERROR</literal></term>
     <listitem>
      <para>
-      Specifies to save error information to <replaceable class="parameter">
-      location</replaceable> when there is malformed data in the input.
-      Currently, only <literal>error</literal> (default) and
<literal>none</literal>
+      Specifies which <replaceable class="parameter">
+      error_action</replaceable> to perform when there is malformed
data in the input.
+      Currently, only <literal>stop</literal> (default) and
<literal>ignore</literal>
       values are supported.
-      If the <literal>error</literal> value is specified,
+      If the <literal>stop</literal> value is specified,
       <command>COPY</command> stops operation at the first error.
-      If the <literal>none</literal> value is specified,
+      If the <literal>ignore</literal> value is specified,
       <command>COPY</command> skips malformed data and continues copying data.
       The option is allowed only in <command>COPY FROM</command>.
-      The <literal>none</literal> value is allowed only when
-      not using <literal>binary</literal> format.
+      Only <literal>stop</literal> value is allowed only when
+      using <literal>binary</literal> format.
      </para>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 85881ca0..c30baec1 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,7 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
-    SAVE_ERROR_TO '<replaceable class="parameter">location</replaceable>'
+    ON_ERROR '<replaceable class="parameter">error_action</replaceable>'
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
 </synopsis>
  </refsynopsisdiv>
@@ -375,20 +375,20 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
    </varlistentry>
 
    <varlistentry>
-    <term><literal>SAVE_ERROR_TO</literal></term>
+    <term><literal>ON_ERROR</literal></term>
     <listitem>
      <para>
-      Specifies to save error information to <replaceable class="parameter">
-      location</replaceable> when there is malformed data in the input.
-      Currently, only <literal>error</literal> (default) and <literal>none</literal>
+      Specifies which <replaceable class="parameter">
+      error_action</replaceable> to perform when there is malformed data in the input.
+      Currently, only <literal>stop</literal> (default) and <literal>ignore</literal>
       values are supported.
-      If the <literal>error</literal> value is specified,
+      If the <literal>stop</literal> value is specified,
       <command>COPY</command> stops operation at the first error.
-      If the <literal>none</literal> value is specified,
+      If the <literal>ignore</literal> value is specified,
       <command>COPY</command> skips malformed data and continues copying data.
       The option is allowed only in <command>COPY FROM</command>.
-      The <literal>none</literal> value is allowed only when
-      not using <literal>binary</literal> format.
+      Only <literal>stop</literal> value is allowed only when
+      using <literal>binary</literal> format.
      </para>
     </listitem>
    </varlistentry>
@@ -577,7 +577,7 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY</command> stops operation at the first error when
-    <literal>SAVE_ERROR_TO</literal> is not specified. This
+    <literal>ON_ERROR</literal> is not specified. This
     should not lead to problems in the event of a <command>COPY
     TO</command>, but the target table will already have received
     earlier rows in a <command>COPY FROM</command>. These rows will not
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index c36d7f1d..cc0786c6 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -395,39 +395,39 @@ defGetCopyHeaderChoice(DefElem *def, bool is_from)
 }
 
 /*
- * Extract a CopySaveErrorToChoice value from a DefElem.
+ * Extract a CopyOnErrorChoice value from a DefElem.
  */
-static CopySaveErrorToChoice
-defGetCopySaveErrorToChoice(DefElem *def, ParseState *pstate, bool is_from)
+static CopyOnErrorChoice
+defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 {
 	char	   *sval;
 
 	if (!is_from)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-				 errmsg("COPY SAVE_ERROR_TO cannot be used with COPY TO"),
+				 errmsg("COPY ON_ERROR cannot be used with COPY TO"),
 				 parser_errposition(pstate, def->location)));
 
 	/*
 	 * If no parameter value given, assume the default value.
 	 */
 	if (def->arg == NULL)
-		return COPY_SAVE_ERROR_TO_ERROR;
+		return COPY_ON_ERROR_STOP;
 
 	/*
-	 * Allow "error", or "none" values.
+	 * Allow "stop", or "ignore" values.
 	 */
 	sval = defGetString(def);
-	if (pg_strcasecmp(sval, "error") == 0)
-		return COPY_SAVE_ERROR_TO_ERROR;
-	if (pg_strcasecmp(sval, "none") == 0)
-		return COPY_SAVE_ERROR_TO_NONE;
+	if (pg_strcasecmp(sval, "stop") == 0)
+		return COPY_ON_ERROR_STOP;
+	if (pg_strcasecmp(sval, "ignore") == 0)
+		return COPY_ON_ERROR_IGNORE;
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-			 errmsg("COPY save_error_to \"%s\" not recognized", sval),
+			 errmsg("COPY ON_ERROR \"%s\" not recognized", sval),
 			 parser_errposition(pstate, def->location)));
-	return COPY_SAVE_ERROR_TO_ERROR;	/* keep compiler quiet */
+	return COPY_ON_ERROR_STOP;	/* keep compiler quiet */
 }
 
 /*
@@ -455,7 +455,7 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		format_specified = false;
 	bool		freeze_specified = false;
 	bool		header_specified = false;
-	bool		save_error_to_specified = false;
+	bool		on_error_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -608,12 +608,12 @@ ProcessCopyOptions(ParseState *pstate,
 								defel->defname),
 						 parser_errposition(pstate, defel->location)));
 		}
-		else if (strcmp(defel->defname, "save_error_to") == 0)
+		else if (strcmp(defel->defname, "on_error") == 0)
 		{
-			if (save_error_to_specified)
+			if (on_error_specified)
 				errorConflictingDefElem(defel, pstate);
-			save_error_to_specified = true;
-			opts_out->save_error_to = defGetCopySaveErrorToChoice(defel, pstate, is_from);
+			on_error_specified = true;
+			opts_out->on_error = defGetCopyOnErrorChoice(defel, pstate, is_from);
 		}
 		else
 			ereport(ERROR,
@@ -642,10 +642,10 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify DEFAULT in BINARY mode")));
 
-	if (opts_out->binary && opts_out->save_error_to != COPY_SAVE_ERROR_TO_ERROR)
+	if (opts_out->binary && opts_out->on_error != COPY_ON_ERROR_STOP)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("cannot specify SAVE_ERROR_TO in BINARY mode")));
+				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
 
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 50e245d5..c956cfa4 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -657,7 +657,7 @@ CopyFrom(CopyFromState cstate)
 	Assert(cstate->rel);
 	Assert(list_length(cstate->range_table) == 1);
 
-	if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_ERROR)
+	if (cstate->opts.on_error != COPY_ON_ERROR_STOP)
 		Assert(cstate->escontext);
 
 	/*
@@ -996,14 +996,14 @@ CopyFrom(CopyFromState cstate)
 		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
 			break;
 
-		if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_ERROR &&
+		if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
 			cstate->escontext->error_occurred)
 		{
 			/*
-			 * Soft error occured, skip this tuple and save error information
-			 * according to SAVE_ERROR_TO.
+			 * Soft error occured, skip this tuple and deal with error information
+			 * according to ON_ERROR.
 			 */
-			if (cstate->opts.save_error_to == COPY_SAVE_ERROR_TO_NONE)
+			if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
 
 				/*
 				 * Just make ErrorSaveContext ready for the next NextCopyFrom.
@@ -1307,7 +1307,7 @@ CopyFrom(CopyFromState cstate)
 	/* Done, clean up */
 	error_context_stack = errcallback.previous;
 
-	if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_ERROR &&
+	if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
 		cstate->num_errors > 0)
 		ereport(NOTICE,
 				errmsg_plural("%llu row was skipped due to data type incompatibility",
@@ -1450,18 +1450,18 @@ BeginCopyFrom(ParseState *pstate,
 		}
 	}
 
-	/* Set up soft error handler for SAVE_ERROR_TO */
-	if (cstate->opts.save_error_to != COPY_SAVE_ERROR_TO_ERROR)
+	/* Set up soft error handler for ON_ERROR */
+	if (cstate->opts.on_error != COPY_ON_ERROR_STOP)
 	{
 		cstate->escontext = makeNode(ErrorSaveContext);
 		cstate->escontext->type = T_ErrorSaveContext;
 		cstate->escontext->error_occurred = false;
 
 		/*
-		 * Currently we only support COPY_SAVE_ERROR_TO_NONE. We'll add other
+		 * Currently we only support COPY_ON_ERROR_IGNORE. We'll add other
 		 * options later
 		 */
-		if (cstate->opts.save_error_to == COPY_SAVE_ERROR_TO_NONE)
+		if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
 			cstate->escontext->details_wanted = false;
 	}
 	else
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 7207eb26..36214aab 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -956,7 +956,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
 			}
-			/* If SAVE_ERROR_TO is specified, skip rows with soft errors */
+			/* If ON_ERROR is specified with IGNORE, skip rows with soft errors */
 			else if (!InputFunctionCallSafe(&in_functions[m],
 											string,
 											typioparams[m],
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6bfdb5f0..ada711d0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2899,15 +2899,15 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
 					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
-					  "SAVE_ERROR_TO");
+					  "ON_ERROR");

-	/* Complete COPY <sth> FROM filename WITH (SAVE_ERROR_TO */
-	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "SAVE_ERROR_TO"))
-		COMPLETE_WITH("error", "none");
+	/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
+	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR"))
+		COMPLETE_WITH("stop", "ignore");
 
 	/* Complete COPY <sth> FROM <sth> WITH (<options>) */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM", MatchAny, "WITH", MatchAny))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 8972c618..78af1b0e 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -34,11 +34,11 @@ typedef enum CopyHeaderChoice
  * Represents where to save input processing errors.  More values to be added
  * in the future.
  */
-typedef enum CopySaveErrorToChoice
+typedef enum CopyOnErrorChoice
 {
-	COPY_SAVE_ERROR_TO_ERROR = 0,	/* immediately throw errors */
-	COPY_SAVE_ERROR_TO_NONE,	/* ignore errors */
-} CopySaveErrorToChoice;
+	COPY_ON_ERROR_STOP = 0,	/* immediately throw errors, default */
+	COPY_ON_ERROR_IGNORE,	/* ignore errors */
+} CopyOnErrorChoice;
 
 /*
  * A struct to hold COPY options, in a parsed form. All of these are related
@@ -72,7 +72,7 @@ typedef struct CopyFormatOptions
 	bool		force_null_all; /* FORCE_NULL *? */
 	bool	   *force_null_flags;	/* per-column CSV FN flags */
 	bool		convert_selectively;	/* do selective binary conversion? */
-	CopySaveErrorToChoice save_error_to;	/* where to save error information */
+	CopyOnErrorChoice on_error;	/* what to do when error happened */
 	List	   *convert_select; /* list of column names (can be NIL) */
 } CopyFormatOptions;
 
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 42cbcb2e..d982ae4f 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -77,21 +77,21 @@ COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
 ERROR:  conflicting or redundant options
 LINE 1: COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii...
                                                  ^
-COPY x from stdin (save_error_to none,save_error_to none);
+COPY x from stdin (ON_ERROR ignore, ON_ERROR ignore);
 ERROR:  conflicting or redundant options
-LINE 1: COPY x from stdin (save_error_to none,save_error_to none);
-                                              ^
+LINE 1: COPY x from stdin (ON_ERROR ignore, ON_ERROR ignore);
+                                            ^
 -- incorrect options
 COPY x to stdin (format BINARY, delimiter ',');
 ERROR:  cannot specify DELIMITER in BINARY mode
 COPY x to stdin (format BINARY, null 'x');
 ERROR:  cannot specify NULL in BINARY mode
-COPY x from stdin (format BINARY, save_error_to none);
-ERROR:  cannot specify SAVE_ERROR_TO in BINARY mode
-COPY x to stdin (save_error_to none);
-ERROR:  COPY SAVE_ERROR_TO cannot be used with COPY TO
-LINE 1: COPY x to stdin (save_error_to none);
-                         ^
+COPY x from stdin (format BINARY, ON_ERROR ignore);
+ERROR:  only ON_ERROR STOP is allowed in BINARY mode
+COPY x from stdin (ON_ERROR unsupported);
+ERROR:  COPY ON_ERROR "unsupported" not recognized
+LINE 1: COPY x from stdin (ON_ERROR unsupported);
+                           ^
 COPY x to stdin (format TEXT, force_quote(a));
 ERROR:  COPY FORCE_QUOTE requires CSV mode
 COPY x from stdin (format CSV, force_quote(a));
@@ -104,9 +104,9 @@ COPY x to stdout (format TEXT, force_null(a));
 ERROR:  COPY FORCE_NULL requires CSV mode
 COPY x to stdin (format CSV, force_null(a));
 ERROR:  COPY FORCE_NULL cannot be used with COPY TO
-COPY x to stdin (format BINARY, save_error_to unsupported);
-ERROR:  COPY SAVE_ERROR_TO cannot be used with COPY TO
-LINE 1: COPY x to stdin (format BINARY, save_error_to unsupported);
+COPY x to stdin (format BINARY, ON_ERROR unsupported);
+ERROR:  COPY ON_ERROR cannot be used with COPY TO
+LINE 1: COPY x to stdin (format BINARY, ON_ERROR unsupported);
                                         ^
 -- too many columns in column list: should fail
 COPY x (a, b, c, d, e, d, c) from stdin;
@@ -724,12 +724,12 @@ SELECT * FROM instead_of_insert_tbl;
 (2 rows)
 
 COMMIT;
--- tests for SAVE_ERROR_TO option
+-- tests for ON_ERROR option
 CREATE TABLE check_ign_err (n int, m int[], k int);
-COPY check_ign_err FROM STDIN WITH (save_error_to error);
+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 (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 NOTICE:  4 rows were skipped due to data type incompatibility
 SELECT * FROM check_ign_err;
  n |  m  | k 
@@ -740,15 +740,15 @@ SELECT * FROM check_ign_err;
 
 -- test datatype error that can't be handled as soft: should fail
 CREATE TABLE hard_err(foo widget);
-COPY hard_err FROM STDIN WITH (save_error_to none);
+COPY hard_err FROM STDIN WITH (ON_ERROR ignore);
 ERROR:  invalid input syntax for type widget: "1"
 CONTEXT:  COPY hard_err, line 1, column foo: "1"
 -- test missing data: should fail
-COPY check_ign_err FROM STDIN WITH (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 ERROR:  missing data for column "k"
 CONTEXT:  COPY check_ign_err, line 1: "1	{1}"
 -- test extra data: should fail
-COPY check_ign_err FROM STDIN WITH (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 ERROR:  extra data after last expected column
 CONTEXT:  COPY check_ign_err, line 1: "1	{1}	3	abc"
 -- clean up
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index c48d5563..73b2e688 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -66,20 +66,20 @@ COPY x from stdin (force_not_null (a), force_not_null (b));
 COPY x from stdin (force_null (a), force_null (b));
 COPY x from stdin (convert_selectively (a), convert_selectively (b));
 COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
-COPY x from stdin (save_error_to none,save_error_to none);
+COPY x from stdin (ON_ERROR ignore, ON_ERROR ignore);
 
 -- incorrect options
 COPY x to stdin (format BINARY, delimiter ',');
 COPY x to stdin (format BINARY, null 'x');
-COPY x from stdin (format BINARY, save_error_to none);
-COPY x to stdin (save_error_to none);
+COPY x from stdin (format BINARY, ON_ERROR ignore);
+COPY x from stdin (ON_ERROR unsupported);
 COPY x to stdin (format TEXT, force_quote(a));
 COPY x from stdin (format CSV, force_quote(a));
 COPY x to stdout (format TEXT, force_not_null(a));
 COPY x to stdin (format CSV, force_not_null(a));
 COPY x to stdout (format TEXT, force_null(a));
 COPY x to stdin (format CSV, force_null(a));
-COPY x to stdin (format BINARY, save_error_to unsupported);
+COPY x to stdin (format BINARY, ON_ERROR unsupported);
 
 -- too many columns in column list: should fail
 COPY x (a, b, c, d, e, d, c) from stdin;
@@ -498,9 +498,9 @@ test1
 SELECT * FROM instead_of_insert_tbl;
 COMMIT;
 
--- tests for SAVE_ERROR_TO option
+-- tests for ON_ERROR option
 CREATE TABLE check_ign_err (n int, m int[], k int);
-COPY check_ign_err FROM STDIN WITH (save_error_to error);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR stop);
 1	{1}	1
 a	{2}	2
 3	{3}	3333333333
@@ -508,7 +508,7 @@ a	{2}	2
 
 5	{5}	5
 \.
-COPY check_ign_err FROM STDIN WITH (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 1	{1}	1
 a	{2}	2
 3	{3}	3333333333
@@ -520,17 +520,17 @@ SELECT * FROM check_ign_err;
 
 -- test datatype error that can't be handled as soft: should fail
 CREATE TABLE hard_err(foo widget);
-COPY hard_err FROM STDIN WITH (save_error_to none);
+COPY hard_err FROM STDIN WITH (ON_ERROR ignore);
 1
 \.
 
 -- test missing data: should fail
-COPY check_ign_err FROM STDIN WITH (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 1	{1}
 \.
 
 -- test extra data: should fail
-COPY check_ign_err FROM STDIN WITH (save_error_to none);
+COPY check_ign_err FROM STDIN WITH (ON_ERROR ignore);
 1	{1}	3	abc
 \.
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 29fd1cae..456461f8 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -478,6 +478,7 @@ CopyHeaderChoice
 CopyInsertMethod
 CopyMultiInsertBuffer
 CopyMultiInsertInfo
+CopyOnErrorChoice
 CopySource
 CopyStmt
 CopyToState
@@ -4041,4 +4042,3 @@ manifest_writer
 rfile
 ws_options
 ws_file_info
-CopySaveErrorToChoice

---

From: torikoshia <[email-masked]>
Date: Fri, 19 Jan 2024 21:37:38 +0900

On 2024-01-18 23:59, jian he wrote:
> -    SAVE_ERROR_TO '<replaceable 
> class="parameter">location</replaceable>'
> +    ON_ERROR '<replaceable 
> class="parameter">error_action</replaceable>'
>      ENCODING '<replaceable 
> class="parameter">encoding_name</replaceable>'
>  </synopsis>
>   </refsynopsisdiv>

Thanks for making the patch!

> +      using <literal>binary</literal> format.

The second 'only' may be unnecessary.

> +                       /* If ON_ERROR is specified with IGNORE, skip 

This is correct now, but considering future works which add other 
options like "file 'copy.log'" and
"table 'copy_log'", it may be better not to limit the case to 'IGNORE'.
How about something like this?

   If ON_ERROR is specified and the value is not STOP, skip rows with 
soft errors

>  COPY x from stdin (format CSV, force_quote(a));

In the existing test for copy2.sql, the COPY options are written in 
lower case(e.g. 'format')　and option value(e.g. 'BINARY') are written in 
upper case.
It would be more consistent to align them.

-- 
Regards,

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 19 Jan 2024 15:27:18 +0200

Hi!

> Thanks for making the patch!

The patch is pushed!  The proposed changes are incorporated excluding this.

>    If ON_ERROR is specified and the value is not STOP, skip rows with

I think when we have more options, then we wouldn't just skip rows
with soft errors but rather save them.  So, I left this comment as is
for now.

------
Regards,
Alexander Korotkov

---

From: torikoshia <[email-masked]>
Date: Fri, 19 Jan 2024 23:26:25 +0900

> with soft errors but rather save them.  So, I left this comment as is

Agreed.
Thanks for the notification!

-- 
Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 28 Mar 2024 10:20:50 +0900

Hi,

> > where other_future_option might be compound like "file 'copy.log'" or

I realized that ON_ERROR syntax synoposis in the documentation is not
correct. The option doesn't require the value to be quoted and the
value can be omitted. The attached patch fixes it.

Regards,

---

From: torikoshia <[email-masked]>
Date: Thu, 28 Mar 2024 21:38:12 +0900

> value can be omitted. The attached patch fixes it.

Thanks!

Attached patch fixes the doc, but I'm wondering perhaps it might be 
better to modify the codes to prohibit abbreviation of the value.

When seeing the query which abbreviates ON_ERROR value, I feel it's not 
obvious what happens compared to other options which tolerates 
abbreviation of the value such as FREEZE or HEADER.

   COPY t1 FROM stdin WITH (ON_ERROR);

What do you think?

-- 
Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 28 Mar 2024 21:54:02 +0900

>    COPY t1 FROM stdin WITH (ON_ERROR);

Indeed. Looking at options of other commands such as VACUUM and
EXPLAIN, I can see that we can omit a boolean value, but non-boolean
parameters require its value. The HEADER option is not a pure boolean
parameter but we can omit the value. It seems to be for backward
compatibility; it used to be a boolean parameter. I agree that the
above example would confuse users.

Regards,

---

From: torikoshia <[email-masked]>
Date: Fri, 29 Mar 2024 11:54:10 +0900

> above example would confuse users.

Thanks for your comment!

Attached a patch which modifies the code to prohibit omission of its 
value.

I was a little unsure about adding a regression test for this, but I 
have not added it since other COPY option doesn't test the omission of 
its value.

-- 
Regards,

From 1b4bec3c2223246ec59ffb9eb7de2f1de27315f7 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 29 Mar 2024 11:36:12 +0900
Subject: [PATCH v1] Disallow ON_ERROR option without value

Currently ON_ERROR option of COPY allows to omit its value,
but the syntax synopsis in the documentation requires it.

Since it seems non-boolean parameters usually require its value
and it's not obvious what happens when value of ON_ERROR is
omitted, this patch disallows ON_ERROR without its value.
---
 src/backend/commands/copy.c | 9 +--------
 1 file changed, 1 insertion(+), 8 deletions(-)

diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 28cf8b040a..2719bf28b7 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -392,7 +392,7 @@ defGetCopyHeaderChoice(DefElem *def, bool is_from)
 static CopyOnErrorChoice
 defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 {
-	char	   *sval;
+	char	   *sval = defGetString(def);
 
 	if (!is_from)
 		ereport(ERROR,
@@ -400,16 +400,9 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 				 errmsg("COPY ON_ERROR cannot be used with COPY TO"),
 				 parser_errposition(pstate, def->location)));
 
-	/*
-	 * If no parameter value given, assume the default value.
-	 */
-	if (def->arg == NULL)
-		return COPY_ON_ERROR_STOP;
-
 	/*
 	 * Allow "stop", or "ignore" values.
 	 */
-	sval = defGetString(def);
 	if (pg_strcasecmp(sval, "stop") == 0)
 		return COPY_ON_ERROR_STOP;
 	if (pg_strcasecmp(sval, "ignore") == 0)

base-commit: 0075d78947e3800c5a807f48fd901f16db91101b
-- 
2.39.2

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 1 Apr 2024 11:31:56 +0900

> have not added it since other COPY option doesn't test the omission of

Probably should we change the doc as well since ON_ERROR value doesn't
necessarily need to be single-quoted?

The rest looks good to me.

Alexander, what do you think about this change as you're the committer
of this feature?

Regards,

---

From: torikoshia <[email-masked]>
Date: Tue, 02 Apr 2024 19:34:45 +0900

> necessarily need to be single-quoted?

Agreed.
Since it seems this issue is independent from the omission of ON_ERROR 
option value, attached a separate patch.

-- 
Regards,

 
 	if (!is_from)
 		ereport(ERROR,
 
-	/*

From 840152c20d47220f106d5fe14af4a86cec99987e Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Tue, 2 Apr 2024 19:11:01 +0900
Subject: [PATCH v1] doc: Fix COPY ON_ERROR option syntax synopsis.

Since ON_ERROR value doesn't require quotations, this patch removes them.
Oversight in b725b7eec43.
---
 doc/src/sgml/ref/copy.sgml | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 33ce7c4ea6..1ce19668d8 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,7 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
-    ON_ERROR '<replaceable class="parameter">error_action</replaceable>'
+    ON_ERROR <replaceable class="parameter">error_action</replaceable>
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
     LOG_VERBOSITY <replaceable class="parameter">mode</replaceable>
 </synopsis>

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 16 Apr 2024 13:16:18 +0900

> option value, attached a separate patch.

Thank you for the patches! These patches look good to me. I'll push
them, barring any objections.

Regards,

---

From: torikoshia <[email-masked]>
Date: Wed, 17 Apr 2024 16:28:29 +0900

> them, barring any objections.

Thanks for your review and apply!

-- 
Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 17 Apr 2024 16:30:31 +0900

> Thanks for your review and apply!

Thank you for the patches!

Pushed: a6d0fa5ef8 and f6f8ac8e75.

Regards,