hi.
The previous discussion mentioned using built-in typed tables for the
error saving table.
It's doable.
first create an build-in composite type in system_functions.sql:
CREATE TYPE copy_error_saving AS(
userid oid,
copy_tbl oid,
filename text COLLATE "C",
lineno bigint,
line text COLLATE "C",
colname text COLLATE "C",
raw_field_value text COLLATE "C",
err_message text COLLATE "C",
err_detail text COLLATE "C",
errorcode text COLLATE "C"
);
then we can use it to create a table like:
CREATE TABLE error_saving_table OF copy_error_saving;
The downside of this:
If the pg_catalog composite (type copy_error_saving) were to change,
it could lead to potential compatibility issues.
We need to be confident that copy_error_saving definitions are
unlikely to occur in the future.
For the above type copy_error_saving, I am wondering, do we aslo need
add a timestamptz field like "starttime" to indicate COPY beginning time.
anyway, please take a look at the attached patch.
It introduces a built-in composite type, allowing users to simply use
CREATE TABLE x OF copy_error_saving
to create a table for storing COPY FROM error-related information.
From 0593ef9fee095ed437ec7a5db44e359ee729ca40 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 22 Oct 2025 13:09:03 +0800
Subject: [PATCH v7 1/1] COPY FROM (on_error table)
The syntax is {on_error table, table error_saving_tbl}.
The error_saving_tbl must be a typed table, based on internal type
copy_error_saving. A preliminary lock check is also performed on the
error-saving table to ensure that inserts into it will not block.
When an error occurs, we record the error metadata and insert it into the
error_saving_tbl, then proceed to the next row. Although the error_saving_tbl
may not capture information for every invalid column in each row, it retains the
raw_field_value, which can be used for further investigation.
the build-in type: pg_catalog.copy_error_saving definition is
CREATE TYPE copy_error_saving AS
(
userid oid,
copy_tbl oid,
filename text COLLATE "C",
lineno bigint,
line text COLLATE "C",
colname text COLLATE "C",
raw_field_value text COLLATE "C",
err_message text COLLATE "C",
err_detail text COLLATE "C",
errorcode text COLLATE "C"
);
It's declared in src/backend/catalog/system_functions.sql.
If it's going to change, which will cause potential issue.
we need be sure that this will unlikely to change in the future.
TODO: Should we also add field (starttime timestamptz) to copy_error_saving to
indicate the time when this error record was inserted.
---
doc/src/sgml/datatype.sgml | 100 +++++++++++++++
doc/src/sgml/ref/copy.sgml | 32 ++++-
src/backend/catalog/system_functions.sql | 13 ++
src/backend/commands/copy.c | 37 +++++-
src/backend/commands/copyfrom.c | 147 +++++++++++++++++++++--
src/backend/commands/copyfromparse.c | 51 +++++++-
src/backend/parser/gram.y | 1 +
src/include/commands/copy.h | 2 +
src/include/commands/copyfrom_internal.h | 1 +
src/test/regress/expected/copy2.out | 91 ++++++++++++++
src/test/regress/sql/copy2.sql | 77 ++++++++++++
11 files changed, 537 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index b81d89e2608..d5aaafbd2ea 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -5092,6 +5092,106 @@ WHERE ...
</para>
</sect1>
+ <sect1 id="datatype-copy_error_saving">
+ <title><type>copy_error_saving</type> Type</title>
+ <indexterm zone="datatype-copy_error_saving">
+ <primary>copy_error_saving</primary>
+ </indexterm>
+
+ <para>
+ The built-in composite type <type>copy_error_saving</type> is used by the
+ <link linkend="sql-copy"><command>COPY FROM</command></link> command.
+ It contains the following fields, which are used to store information when <command>COPY FROM</command>
+ encounters an error converting a column’s input value to its data type.
+ </para>
+
+ <para>
+<informaltable>
+ <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 <command>COPY FROM</command> operation user.
+ Reference <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>,
+ however there is no hard dependency with catalog <literal>pg_authid</literal>.
+ If the corresponding row on <literal>pg_authid</literal> is deleted, this value becomes stale.
+ </entry>
+ </row>
+
+ <row>
+ <entry> <literal>copy_tbl</literal> </entry>
+ <entry><type>oid</type></entry>
+ <entry>The <command>COPY FROM</command> operation destination table.
+ Reference <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>,
+ however there is no hard dependency with catalog <literal>pg_class</literal>.
+ If the corresponding row on <literal>pg_class</literal> is deleted, this value becomes stale.
+ </entry>
+ </row>
+
+ <row>
+ <entry> <literal>filename</literal> </entry>
+ <entry><type>text</type></entry>
+ <entry>The path name of the <command>COPY FROM</command> input</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 primary error message, see <link linkend="error-message-reporting">ereport</link></entry>
+ </row>
+
+ <row>
+ <entry> <literal>err_detail</literal> </entry>
+ <entry><type>text</type></entry>
+ <entry>The optionally detailed error message</entry>
+ </row>
+
+ <row>
+ <entry> <literal>errorcode </literal> </entry>
+ <entry><type>text</type></entry>
+ <entry>The SQLSTATE error identifier code for the error condition</entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </para>
+ </sect1>
+
+
<sect1 id="datatype-pg-lsn">
<title><type>pg_lsn</type> Type</title>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index fdc24b36bb8..0215aac0771 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> [, ...] ) | * }
ON_ERROR <replaceable class="parameter">error_action</replaceable>
+ TABLE <replaceable class="parameter">error_saving_table</replaceable>
REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
LOG_VERBOSITY <replaceable class="parameter">verbosity</replaceable>
@@ -413,15 +414,24 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
input value into its data type.
An <replaceable class="parameter">error_action</replaceable> value of
<literal>stop</literal> means fail the command, while
- <literal>ignore</literal> means discard the input row and continue with the next one.
+ <literal>ignore</literal> means discard the input row and continue with the next one,
+ <literal>table</literal> means save error information to <replaceable class="parameter">error_saving_table</replaceable>
+ and continue with the next one.
The default is <literal>stop</literal>.
</para>
<para>
- The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
+ The <literal>ignore</literal> and <literal>table</literal> option are applicable only for <command>COPY FROM</command>
when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
</para>
<para>
- A <literal>NOTICE</literal> message containing the ignored row count is
+ If <literal>ON_ERROR</literal> option is set to <literal>table</literal>,
+ a <literal>NOTICE</literal> message containing the row count saved to
+ <replaceable class="parameter">error_saving_table</replaceable> is
+ emitted at the end of the <command>COPY FROM</command>.
+ </para>
+
+ <para>
+ If <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>, a <literal>NOTICE</literal> message containing the ignored row count is
emitted at the end of the <command>COPY FROM</command> if at least one
row was discarded. When <literal>LOG_VERBOSITY</literal> option is set to
<literal>verbose</literal>, a <literal>NOTICE</literal> message
@@ -481,6 +491,22 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLE</literal></term>
+ <listitem>
+ <para>
+ Save error context details to the specified table: <replaceable class="parameter">error_saving_table</replaceable>.
+ This option is allowed only in <command>COPY FROM</command> and
+ <literal>ON_ERROR</literal> is specified with <literal>TABLE</literal>.
+ It also require the current <command>COPY FROM</command> user have <literal>INSERT</literal> privileges on all columns
+ of the <replaceable class="parameter">error_saving_table</replaceable>.
+ The <replaceable class="parameter">error_saving_table</replaceable> must
+ be a typed table derived from composite type <link
+ linkend="datatype-copy_error_saving"><command>copy_error_saving</command></link>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>WHERE</literal></term>
<listitem>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..dc58125915d 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -813,3 +813,16 @@ GRANT pg_read_all_settings TO pg_monitor;
GRANT pg_read_all_stats TO pg_monitor;
GRANT pg_stat_scan_tables TO pg_monitor;
+
+CREATE TYPE copy_error_saving AS(
+ userid oid,
+ copy_tbl oid,
+ filename text COLLATE "C",
+ lineno bigint,
+ line text COLLATE "C",
+ colname text COLLATE "C",
+ raw_field_value text COLLATE "C",
+ err_message text COLLATE "C",
+ err_detail text COLLATE "C",
+ errorcode text COLLATE "C"
+);
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 44020d0ae80..c896fc67529 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -417,13 +417,16 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
parser_errposition(pstate, def->location)));
/*
- * Allow "stop", or "ignore" values.
+ * Allow "stop", "ignore" or "table" values.
*/
if (pg_strcasecmp(sval, "stop") == 0)
return COPY_ON_ERROR_STOP;
if (pg_strcasecmp(sval, "ignore") == 0)
return COPY_ON_ERROR_IGNORE;
+ if (pg_strcasecmp(sval, "table") == 0)
+ return COPY_ON_ERROR_TABLE;
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
/*- translator: first %s is the name of a COPY option, e.g. ON_ERROR */
@@ -516,6 +519,7 @@ ProcessCopyOptions(ParseState *pstate,
bool freeze_specified = false;
bool header_specified = false;
bool on_error_specified = false;
+ bool on_error_tbl_specified = false;
bool log_verbosity_specified = false;
bool reject_limit_specified = false;
ListCell *option;
@@ -691,6 +695,13 @@ ProcessCopyOptions(ParseState *pstate,
reject_limit_specified = true;
opts_out->reject_limit = defGetCopyRejectLimitOption(defel);
}
+ else if (strcmp(defel->defname, "table") == 0)
+ {
+ if (on_error_tbl_specified)
+ errorConflictingDefElem(defel, pstate);
+ on_error_tbl_specified = true;
+ opts_out->on_error_tbl = defGetString(defel);
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -699,6 +710,30 @@ ProcessCopyOptions(ParseState *pstate,
parser_errposition(pstate, defel->location)));
}
+ if (opts_out->on_error == COPY_ON_ERROR_TABLE)
+ {
+ if (opts_out->on_error_tbl == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set option %s to \"%s\" when \"%s\" is not specified", "ON_ERROR", "TABLE", "TABLE"),
+ errhint("You may need also specify \"%s\" option.", "TABLE"));
+
+ /* TODO: these two options can be supported */
+ if (opts_out->reject_limit)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot set option %s when %s is specified as \"%s\"", "REJECT_LIMIT", "ON_ERROR", "TABLE"));
+
+ if (opts_out->log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot set option %s to \"%s\" when %s is specified as \"%s\"", "LOG_VERBOSITY", "VERBOSE", "ON_ERROR", "TABLE"));
+ }
+ else if (opts_out->on_error_tbl != NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("COPY %s can only be used when option %s is set to \"%s\"", "TABLE", "ON_ERROR", "TABLE"));
+
/*
* Check for incompatible options (must do these three before inserting
* defaults)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..629bdfb4b38 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -28,6 +28,7 @@
#include "access/tableam.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/pg_namespace.h"
#include "commands/copyapi.h"
#include "commands/copyfrom_internal.h"
#include "commands/progress.h"
@@ -45,11 +46,14 @@
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
#include "utils/rel.h"
+#include "utils/regproc.h"
#include "utils/snapmgr.h"
+#include "utils/syscache.h"
/*
* No more than this many tuples per CopyMultiInsertBuffer
@@ -1174,6 +1178,25 @@ CopyFrom(CopyFromState cstate)
/* Repeat NextCopyFrom() until no soft error occurs */
continue;
}
+ else if (cstate->opts.on_error == COPY_ON_ERROR_TABLE &&
+ cstate->escontext->error_occurred)
+ {
+ /*
+ * Soft error occurred, skip this tuple and just make
+ * ErrorSaveContext ready for the next NextCopyFrom.
+ * We also need to set details_wanted to true.
+ */
+ cstate->escontext->error_occurred = false;
+ cstate->escontext->details_wanted = true;
+ memset(cstate->escontext->error_data, 0, sizeof(ErrorData));
+
+ /* Report that this tuple was skipped by the ON_ERROR clause */
+ pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
+ cstate->num_errors);
+
+ /* Repeat NextCopyFrom() until no soft error occurs */
+ continue;
+ }
ExecStoreVirtualTuple(myslot);
@@ -1467,14 +1490,23 @@ CopyFrom(CopyFromState cstate)
/* Done, clean up */
error_context_stack = errcallback.previous;
- if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
- cstate->num_errors > 0 &&
+ if (cstate->num_errors > 0 &&
cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
- ereport(NOTICE,
- errmsg_plural("%" PRIu64 " row was skipped due to data type incompatibility",
- "%" PRIu64 " rows were skipped due to data type incompatibility",
- cstate->num_errors,
- cstate->num_errors));
+ {
+ if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+ ereport(NOTICE,
+ errmsg_plural("%" PRIu64 " row was skipped due to data type incompatibility",
+ "%" PRIu64 " rows were skipped due to data type incompatibility",
+ cstate->num_errors,
+ cstate->num_errors));
+ else if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ ereport(NOTICE,
+ errmsg_plural("%" PRIu64 " row was saved to table \"%s\" due to data type incompatibility",
+ "%" PRIu64 " rows were saved to table \"%s\" due to data type incompatibility",
+ cstate->num_errors,
+ cstate->num_errors,
+ RelationGetRelationName(cstate->error_saving)));
+ }
if (bistate != NULL)
FreeBulkInsertState(bistate);
@@ -1509,6 +1541,9 @@ CopyFrom(CopyFromState cstate)
FreeExecutorState(estate);
+ if (cstate->error_saving)
+ table_close(cstate->error_saving, NoLock);
+
return processed;
}
@@ -1622,15 +1657,109 @@ BeginCopyFrom(ParseState *pstate,
cstate->escontext->error_occurred = false;
/*
- * Currently we only support COPY_ON_ERROR_IGNORE. We'll add other
- * options later
+ * Currently we only support COPY_ON_ERROR_IGNORE, COPY_ON_ERROR_TABLE.
+ * We'll add other options later.
*/
if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
cstate->escontext->details_wanted = false;
+ else if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ cstate->escontext->details_wanted = true;
}
else
cstate->escontext = NULL;
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ Oid reloftype;
+ Oid typoid;
+ Oid err_tbl_oid;
+ Datum value;
+ RangeVar *relvar;
+ List *relname_list;
+ HeapTuple tp;
+
+ Assert(cstate->opts.on_error_tbl != NULL);
+
+ relname_list = stringToQualifiedNameList(cstate->opts.on_error_tbl, NULL);
+ relvar = makeRangeVarFromNameList(relname_list);
+
+ /*
+ * We might insert tuples into the error-saving table later, so we first
+ * need to check its lock status. If it is already heavily locked, our
+ * subsequent COPY FROM may stuck. Instead of letting COPY FROM hang,
+ * report an error indicating that the error-saving table is under heavy
+ * lock.
+ */
+ err_tbl_oid = RangeVarGetRelidExtended(relvar,
+ RowExclusiveLock,
+ RVR_NOWAIT,
+ NULL,
+ NULL);
+
+ if (RelationGetRelid(cstate->rel) == err_tbl_oid)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot use relation \"%s\" for COPY error saving while copying data to it",
+ cstate->opts.on_error_tbl));
+
+ /* error saving table must be a regular realtion */
+ if (get_rel_relkind(err_tbl_oid) != RELKIND_RELATION)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot use relation \"%s\" for COPY error saving",
+ cstate->opts.on_error_tbl),
+ errdetail_relkind_not_supported(get_rel_relkind(err_tbl_oid)));
+
+ cstate->error_saving = table_open(err_tbl_oid, NoLock);
+
+ /* The user should have INSERT privilege on error_saving table */
+ value = DirectFunctionCall3(has_table_privilege_id_id,
+ ObjectIdGetDatum(GetUserId()),
+ ObjectIdGetDatum(err_tbl_oid),
+ CStringGetTextDatum("INSERT"));
+ if (!DatumGetBool(value))
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to use table \"%s\" for COPY FROM error saving",
+ RelationGetRelationName(cstate->error_saving)),
+ errhint("Ensure the current user has INSERT privilege on table \"%s\" to use it for COPY FROM error saving.",
+ RelationGetRelationName(cstate->error_saving)));
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(err_tbl_oid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_class reltup = (Form_pg_class) GETSTRUCT(tp);
+
+ reloftype = reltup->reloftype;
+ ReleaseSysCache(tp);
+ }
+ else
+ elog(ERROR, "cache lookup failed for relation %u", err_tbl_oid);
+
+ if (!OidIsValid(reloftype))
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot use relation \"%s\" for COPY error saving",
+ cstate->opts.on_error_tbl),
+ errhint("The COPY error saving table must be a typed table base on type \"%s\".",
+ "copy_error_saving"));
+
+ typoid = GetSysCacheOid2(TYPENAMENSP, Anum_pg_type_oid,
+ PointerGetDatum("copy_error_saving"),
+ ObjectIdGetDatum(PG_CATALOG_NAMESPACE));
+
+ if (reloftype != typoid)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot use relation \"%s\" for COPY error saving",
+ cstate->opts.on_error_tbl),
+ errdetail("relation \"%s\" is a typed table based on type \"%s\"",
+ cstate->opts.on_error_tbl,
+ format_type_be(reloftype)),
+ errhint("The COPY error saving table must be a typed table base on type \"%s\".",
+ format_type_be(typoid)));
+ }
+
/* 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 b1ae97b833d..41bb179a9d9 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -62,6 +62,7 @@
#include <unistd.h>
#include <sys/stat.h>
+#include "access/heapam.h"
#include "commands/copyapi.h"
#include "commands/copyfrom_internal.h"
#include "commands/progress.h"
@@ -1033,7 +1034,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
}
/*
- * If ON_ERROR is specified with IGNORE, skip rows with soft errors
+ * If ON_ERROR is specified with IGNORE or TABLE, skip rows with soft errors
*/
else if (!InputFunctionCallSafe(&in_functions[m],
string,
@@ -1044,9 +1045,55 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
{
Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ /*
+ * We use ErrorSaveContext stored information to form a tuple
+ * and insert it to the specified error saving table.
+ */
+ HeapTuple tuple;
+ TupleDesc tupdesc;
+ char *err_detail;
+ char *err_code;
+ Datum values[10] = {0};
+ bool isnull[10] = {0};
+ int j = 0;
+
+ Assert(cstate->rel != NULL);
+ Assert(cstate->escontext->error_occurred);
+
+ values[j++] = ObjectIdGetDatum(GetUserId());
+ values[j++] = ObjectIdGetDatum(cstate->rel->rd_rel->oid);
+ values[j++] = CStringGetTextDatum(cstate->filename ? cstate->filename : "STDIN");
+ values[j++] = Int64GetDatum((long long) cstate->cur_lineno);
+ values[j++] = CStringGetTextDatum(cstate->line_buf.data);
+ values[j++] = CStringGetTextDatum(cstate->cur_attname);
+ values[j++] = CStringGetTextDatum(string);
+ values[j++] = CStringGetTextDatum(cstate->escontext->error_data->message);
+
+ if (cstate->escontext->error_data->detail == NULL)
+ err_detail = NULL;
+ else
+ err_detail = cstate->escontext->error_data->detail;
+
+ values[j] = err_detail ? CStringGetTextDatum(err_detail) : (Datum) 0;
+ isnull[j++] = err_detail ? false : true;
+
+ err_code = unpack_sql_state(cstate->escontext->error_data->sqlerrcode);
+ values[j++] = CStringGetTextDatum(err_code);
+
+ tupdesc = RelationGetDescr(cstate->error_saving);
+ tuple = heap_form_tuple(tupdesc, values, isnull);
+
+ simple_heap_insert(cstate->error_saving, tuple);
+
+ heap_freetuple(tuple);
+ }
+
cstate->num_errors++;
- if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+ if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE &&
+ cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
{
/*
* Since we emit line number and column info in the below
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dc0c2886674..50855d781c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3625,6 +3625,7 @@ copy_generic_opt_arg:
| NumericOnly { $$ = (Node *) $1; }
| '*' { $$ = (Node *) makeNode(A_Star); }
| DEFAULT { $$ = (Node *) makeString("default"); }
+ | TABLE { $$ = (Node *) makeString("table"); }
| '(' copy_generic_opt_arg_list ')' { $$ = (Node *) $2; }
| /* EMPTY */ { $$ = NULL; }
;
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 541176e1980..80f12af600a 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -35,6 +35,7 @@ typedef enum CopyOnErrorChoice
{
COPY_ON_ERROR_STOP = 0, /* immediately throw errors, default */
COPY_ON_ERROR_IGNORE, /* ignore errors */
+ COPY_ON_ERROR_TABLE, /* saving errors info to table */
} CopyOnErrorChoice;
/*
@@ -84,6 +85,7 @@ typedef struct CopyFormatOptions
CopyOnErrorChoice on_error; /* what to do when error happened */
CopyLogVerbosityChoice log_verbosity; /* verbosity of logged messages */
int64 reject_limit; /* maximum tolerable number of errors */
+ char *on_error_tbl; /* on error, save error info to the table, table name */
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 c8b22af22d8..aa4f984d1fa 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -73,6 +73,7 @@ typedef struct CopyFromStateData
/* parameters from the COPY command */
Relation rel; /* relation to copy from */
+ Relation error_saving; /* relation for copy from error saving */
List *attnumlist; /* integer list of attnums to copy */
char *filename; /* filename, or NULL for STDIN */
bool is_program; /* is 'filename' a program to popen? */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index f3fdce23459..97160a4d02b 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -820,6 +820,91 @@ ERROR: skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
CONTEXT: COPY check_ign_err, line 5, column n: ""
COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
NOTICE: 4 rows were skipped due to data type incompatibility
+CREATE TABLE err_tbl OF copy_error_saving;
+--cannot use for error saving.
+CREATE TEMP TABLE err_tbl_1 AS SELECT * FROM err_tbl;
+CREATE TEMP TABLE t_copy_tbl(a int, b int, c int);
+CREATE TYPE t_copy_typ AS (a int, b int, c int);
+CREATE TABLE t_copy_tbl1 OF t_copy_typ;
+CREATE TEMP VIEW t_copy_v1 AS SELECT * FROM t_copy_tbl;
+----invalid options, the below all should fails
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE, TABLE err_tbl, LOG_VERBOSITY VERBOSE); --error
+ERROR: cannot set option LOG_VERBOSITY to "VERBOSE" when ON_ERROR is specified as "TABLE"
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_tbl); --error
+ERROR: cannot use relation "t_copy_tbl" for COPY error saving while copying data to it
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE);
+ERROR: cannot set option ON_ERROR to "TABLE" when "TABLE" is not specified
+HINT: You may need also specify "TABLE" option.
+COPY t_copy_tbl FROM STDIN WITH (TABLE err_tbl);
+ERROR: COPY TABLE can only be used when option ON_ERROR is set to "TABLE"
+COPY t_copy_tbl TO STDIN WITH (ON_ERROR TABLE);
+ERROR: COPY ON_ERROR cannot be used with COPY TO
+LINE 1: COPY t_copy_tbl TO STDIN WITH (ON_ERROR TABLE);
+ ^
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, reject_limit 10, TABLE err_tbl);
+ERROR: cannot set option REJECT_LIMIT when ON_ERROR is specified as "TABLE"
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, TABLE not_exists);
+ERROR: relation "not_exists" does not exist
+COPY t_copy_tbl(a) FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_v1);
+ERROR: cannot use relation "t_copy_v1" for COPY error saving
+DETAIL: This operation is not supported for views.
+COPY t_copy_tbl(a) FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_tbl1);
+ERROR: cannot use relation "t_copy_tbl1" for COPY error saving
+DETAIL: relation "t_copy_tbl1" is a typed table based on type "t_copy_typ"
+HINT: The COPY error saving table must be a typed table base on type "copy_error_saving".
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, TABLE err_tbl_1);
+ERROR: cannot use relation "err_tbl_1" for COPY error saving
+HINT: The COPY error saving table must be a typed table base on type "copy_error_saving".
+----invalid options, the above all should fails
+--should fail, copied data have extra columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+ERROR: extra data after last expected column
+CONTEXT: COPY t_copy_tbl, line 1: "1,2,3,4"
+--should fail, copied data have less columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+ERROR: extra data after last expected column
+CONTEXT: COPY t_copy_tbl, line 1: "1,2,"
+BEGIN;
+CREATE USER regress_user30;
+GRANT INSERT(userid, copy_tbl, filename, lineno, line, colname,
+ raw_field_value, err_message, err_detail)
+ON TABLE err_tbl TO regress_user30;
+GRANT INSERT ON TABLE t_copy_tbl TO regress_user30;
+GRANT SELECT ON TABLE err_tbl TO regress_user30;
+SAVEPOINT s1;
+SET ROLE regress_user30;
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', ON_ERROR TABLE, TABLE err_tbl); --should fail
+ERROR: permission denied to use table "err_tbl" for COPY FROM error saving
+HINT: Ensure the current user has INSERT privilege on table "err_tbl" to use it for COPY FROM error saving.
+ROLLBACK TO SAVEPOINT s1;
+RESET ROLE;
+GRANT INSERT on TABLE err_tbl to regress_user30;
+SET ROLE regress_user30;
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', ON_ERROR TABLE, TABLE err_tbl); --ok
+NOTICE: 1 row was saved to table "err_tbl" due to data type incompatibility
+SELECT copy_tbl::regclass, filename, lineno, line, colname, raw_field_value,
+ err_message, err_detail, errorcode
+FROM err_tbl;
+ copy_tbl | filename | lineno | line | colname | raw_field_value | err_message | err_detail | errorcode
+------------+----------+--------+------+---------+-----------------+--------------------------------------------+------------+-----------
+ t_copy_tbl | STDIN | 1 | a,b | a | a | invalid input syntax for type integer: "a" | | 22P02
+(1 row)
+
+ROLLBACK;
+--ok cases
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+NOTICE: 4 rows were saved to table "err_tbl" due to data type incompatibility
+SELECT copy_tbl::regclass, filename, lineno, line, colname, raw_field_value,
+ err_message, err_detail, errorcode
+FROM err_tbl;
+ copy_tbl | filename | lineno | line | colname | raw_field_value | err_message | err_detail | errorcode
+------------+----------+--------+--------------------------+---------+---------------------+--------------------------------------------------------------+------------+-----------
+ t_copy_tbl | STDIN | 1 | 1,2,a | c | a | invalid input syntax for type integer: "a" | | 22P02
+ t_copy_tbl | STDIN | 3 | 1,_junk,test | b | _junk | invalid input syntax for type integer: "_junk" | | 22P02
+ t_copy_tbl | STDIN | 4 | cola,colb,colc | a | cola | invalid input syntax for type integer: "cola" | | 22P02
+ t_copy_tbl | STDIN | 6 | 1,11,4238679732489879879 | c | 4238679732489879879 | value "4238679732489879879" is out of range for type integer | | 22003
+(4 rows)
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -838,6 +923,12 @@ DROP TABLE check_ign_err;
DROP TABLE check_ign_err2;
DROP DOMAIN dcheck_ign_err2;
DROP TABLE hard_err;
+DROP TABLE err_tbl;
+DROP TABLE err_tbl_1;
+DROP VIEW t_copy_v1;
+DROP TABLE t_copy_tbl;
+DROP TABLE t_copy_tbl1;
+DROP TYPE t_copy_typ;
--
-- COPY FROM ... DEFAULT
--
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index cef45868db5..2e384cc544c 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -591,6 +591,77 @@ a {7} 7
10 {10} 10
\.
+CREATE TABLE err_tbl OF copy_error_saving;
+--cannot use for error saving.
+CREATE TEMP TABLE err_tbl_1 AS SELECT * FROM err_tbl;
+CREATE TEMP TABLE t_copy_tbl(a int, b int, c int);
+CREATE TYPE t_copy_typ AS (a int, b int, c int);
+CREATE TABLE t_copy_tbl1 OF t_copy_typ;
+CREATE TEMP VIEW t_copy_v1 AS SELECT * FROM t_copy_tbl;
+
+----invalid options, the below all should fails
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE, TABLE err_tbl, LOG_VERBOSITY VERBOSE); --error
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_tbl); --error
+COPY t_copy_tbl FROM STDIN WITH (ON_ERROR TABLE);
+COPY t_copy_tbl FROM STDIN WITH (TABLE err_tbl);
+COPY t_copy_tbl TO STDIN WITH (ON_ERROR TABLE);
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, reject_limit 10, TABLE err_tbl);
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, TABLE not_exists);
+COPY t_copy_tbl(a) FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_v1);
+COPY t_copy_tbl(a) FROM STDIN WITH (ON_ERROR TABLE, TABLE t_copy_tbl1);
+COPY t_copy_tbl(a,b) FROM STDIN WITH (ON_ERROR TABLE, TABLE err_tbl_1);
+----invalid options, the above all should fails
+
+--should fail, copied data have extra columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+1,2,3,4
+\.
+
+--should fail, copied data have less columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+1,2,
+\.
+
+BEGIN;
+CREATE USER regress_user30;
+GRANT INSERT(userid, copy_tbl, filename, lineno, line, colname,
+ raw_field_value, err_message, err_detail)
+ON TABLE err_tbl TO regress_user30;
+
+GRANT INSERT ON TABLE t_copy_tbl TO regress_user30;
+GRANT SELECT ON TABLE err_tbl TO regress_user30;
+SAVEPOINT s1;
+
+SET ROLE regress_user30;
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', ON_ERROR TABLE, TABLE err_tbl); --should fail
+ROLLBACK TO SAVEPOINT s1;
+
+RESET ROLE;
+GRANT INSERT on TABLE err_tbl to regress_user30;
+SET ROLE regress_user30;
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', ON_ERROR TABLE, TABLE err_tbl); --ok
+a,b
+\.
+
+SELECT copy_tbl::regclass, filename, lineno, line, colname, raw_field_value,
+ err_message, err_detail, errorcode
+FROM err_tbl;
+ROLLBACK;
+
+--ok cases
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_ERROR TABLE, TABLE err_tbl);
+1,2,a
+1,2,3
+1,_junk,test
+cola,colb,colc
+4,5,6
+1,11,4238679732489879879
+\.
+SELECT copy_tbl::regclass, filename, lineno, line, colname, raw_field_value,
+ err_message, err_detail, errorcode
+FROM err_tbl;
+
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -609,6 +680,12 @@ DROP TABLE check_ign_err;
DROP TABLE check_ign_err2;
DROP DOMAIN dcheck_ign_err2;
DROP TABLE hard_err;
+DROP TABLE err_tbl;
+DROP TABLE err_tbl_1;
+DROP VIEW t_copy_v1;
+DROP TABLE t_copy_tbl;
+DROP TABLE t_copy_tbl1;
+DROP TYPE t_copy_typ;
--
-- COPY FROM ... DEFAULT
--
2.34.1
