Hi.
I previously did some work in COPY FROM save error information to a table.
still based on this suggestion:
https://www.postgresql.org/message-id/752672.1699474336%40sss.pgh.pa.us
Now I refactored it.
the syntax:
ON_ERROR 'table', TABLE 'error_saving_tbl'
if ON_ERROR is not specified with 'table', TABLE is specified, then error.
if ON_ERROR is specified with 'table', TABLE is not specified or
error_saving_tbl does not exist, then error.
In BeginCopyFrom, we check the data definition of error_saving_table,
we also check if the user has INSERT privilege to error_saving_table
(all the columns).
We also did a preliminary check of the lock condition of error_saving_table.
if it does not meet these conditions, we quickly error out.
error_saving_table will be the same schema as the copy from table.
Because "table" is a keyword, I have to add the following changes to gram.y.
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3420,6 +3420,10 @@ copy_opt_item:
{
$$ = makeDefElem("null", (Node *) makeString($3), @1);
}
+ | TABLE opt_as Sconst
+ {
+ $$ = makeDefElem("table", (Node *) makeString($3), @1);
+ }
since "table" is already a keyword, so there is no influence on the
parsing speed?
demo:
create table err_tbl(
userid oid, -- the user oid while copy generated this entry
copy_tbl oid, --copy table
filename text,
lineno int8,
line text,
colname text,
raw_field_value text,
err_message text,
err_detail text,
errorcode text
);
create table t_copy_tbl(a int, b int, c int);
COPY t_copy_tbl FROM STDIN WITH (delimiter ',', on_error 'table',
table err_tbl);
1,2,a
\.
table err_tbl \gx
-[ RECORD 1 ]---+-------------------------------------------
userid | 10
copy_tbl | 17920
filename | STDIN
lineno | 1
line | 1,2,a
colname | c
raw_field_value | a
err_message | invalid input syntax for type integer: "a"
err_detail |
errorcode | 22P02
From bb6f263ef4d37c2871086db44dca217fa91f5080 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 3 Feb 2024 11:13:14 +0800
Subject: [PATCH v1 1/1] on_error table, saving error info to a table
introduce on_error table option for COPY FROM.
the syntax is {on_error table, table 'error_saving_tbl'}.
we first check table error_saving_tbl's existence and data definition.
if it does not meet our criteria, then we quickly abort the COPY operation.
we also did preliminary check the lock of error saving table
so the COPY can insert tuples to it.
once there is a error happened, we save the error metedata
and insert it to the error_saving_table.
---
contrib/file_fdw/file_fdw.c | 4 +-
doc/src/sgml/ref/copy.sgml | 106 +++++++++++++++++++++-
src/backend/commands/copy.c | 26 ++++++
src/backend/commands/copyfrom.c | 129 ++++++++++++++++++++++++++-
src/backend/commands/copyfromparse.c | 52 ++++++++++-
src/backend/parser/gram.y | 4 +
src/include/commands/copy.h | 4 +-
src/test/regress/expected/copy2.out | 93 +++++++++++++++++++
src/test/regress/sql/copy2.sql | 80 +++++++++++++++++
9 files changed, 492 insertions(+), 6 deletions(-)
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 249d82d3..1d536e9e 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 21a5c4a0..ae13c3b6 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_tbl</replaceable>'
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
</synopsis>
</refsynopsisdiv>
@@ -380,12 +381,14 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
<para>
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>
+ Currently, only <literal>stop</literal> (default) , <literal>ignore</literal>, <literal>table</literal>
values are supported.
If the <literal>stop</literal> value is specified,
<command>COPY</command> stops operation at the first error.
If the <literal>ignore</literal> value is specified,
<command>COPY</command> skips malformed data and continues copying data.
+ If the <literal>table</literal> value is specified,
+ <command>COPY</command> skips malformed data and continues copying data, it aslo insert error related information to <replaceable class="parameter">error_saving_tbl</replaceable>.
The option is allowed only in <command>COPY FROM</command>.
Only <literal>stop</literal> value is allowed when
using <literal>binary</literal> format.
@@ -405,6 +408,107 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>table</literal></term>
+ <listitem>
+ <para>
+ Save error information to the table <replaceable class="parameter">error_saving_table</replaceable>
+ when it encounters an error while doing <command>COPY FROM</command> operation.
+ The table, <replaceable class="parameter">error_saving_table</replaceable>'s schema must be the same as the schema of
+ <command>COPY FROM</command> destination table.
+ This option is allowed only in <command>COPY FROM</command> and
+ <literal>ON_ERROR</literal> is specified with <literal>TABLE</literal>.
+ The <command>COPY FROM</command> user requires <literal>INSERT</literal> privileges for ecah column in <replaceable class="parameter">error_saving_table</replaceable>.
+ If this option is omitted, the <literal>ON_ERROR</literal> parameter must not specified with <literal>table</literal>.
+</para>
+ <para>
+ If table <replaceable class="parameter">error_saving_table</replaceable> does not exists or meet the following definition (column order should also be the same), an error will be raised.
+
+<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 user generated the error.
+ Reference <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>,
+ However there is no hard depenedency with catalog <literal>pg_authid</literal>. If the correspond row on <literal>pg_authid</literal> deleted, this value become stale.
+ </entry>
+ </row>
+
+ <row>
+ <entry> <literal>copy_tbl</literal> </entry>
+ <entry><type>oid</type></entry>
+ <entry>The <command>COPY FROM</command> operation destination table oid.
+ Reference <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>,
+ However there is no hard depenedency with catalog <literal>pg_class</literal>. If the correspond row on <literal>pg_class</literal> deleted, this value become 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 error message</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 </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>WHERE</literal></term>
<listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index cc0786c6..c7d24917 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -423,6 +423,8 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
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),
errmsg("COPY ON_ERROR \"%s\" not recognized", sval),
@@ -456,6 +458,7 @@ ProcessCopyOptions(ParseState *pstate,
bool freeze_specified = false;
bool header_specified = false;
bool on_error_specified = false;
+ bool on_error_tbl_specified = false;
ListCell *option;
/* Support external use for option sanity checking */
@@ -615,6 +618,20 @@ ProcessCopyOptions(ParseState *pstate,
on_error_specified = true;
opts_out->on_error = defGetCopyOnErrorChoice(defel, pstate, is_from);
}
+ 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);
+ if (!opts_out->on_error_tbl)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("argument to option \"%s\" must be a valid table name",
+ defel->defname),
+ parser_errposition(pstate, defel->location)));
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -623,6 +640,15 @@ ProcessCopyOptions(ParseState *pstate,
parser_errposition(pstate, defel->location)));
}
+ if (!on_error_tbl_specified && opts_out->on_error == COPY_ON_ERROR_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("argument to option \"table\" can only applied when ON ERROR is specified")));
+
+ if (on_error_tbl_specified && opts_out->on_error != COPY_ON_ERROR_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("argument to option \"table\" can only applied when ON ERROR is specified")));
/*
* Check for incompatible options (must do these two before inserting
* defaults)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 1fe70b91..b5678317 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -37,6 +37,7 @@
#include "executor/execPartition.h"
#include "executor/executor.h"
#include "executor/nodeModifyTable.h"
+#include "executor/spi.h"
#include "executor/tuptable.h"
#include "foreign/fdwapi.h"
#include "libpq/libpq.h"
@@ -47,6 +48,7 @@
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
+#include "storage/lmgr.h"
#include "tcop/tcopprot.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -655,11 +657,20 @@ CopyFrom(CopyFromState cstate)
bool has_instead_insert_row_trig;
bool leafpart_use_multi_insert = false;
+ /* the error inforamtion while doing NextCopyFrom saving table */
+ Relation on_error_rel = NULL;
+
Assert(cstate->rel);
Assert(list_length(cstate->range_table) == 1);
if (cstate->opts.on_error != COPY_ON_ERROR_STOP)
Assert(cstate->escontext);
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ Assert(cstate->opts.on_error_tbl);
+ on_error_rel = table_open(RelnameGetRelid(cstate->opts.on_error_tbl), AccessShareLock);
+ table_close(on_error_rel, AccessShareLock);
+ }
/*
* The target must be a plain, foreign, or partitioned relation, or have
@@ -994,7 +1005,7 @@ 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, on_error_rel))
break;
if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
@@ -1017,6 +1028,12 @@ CopyFrom(CopyFromState cstate)
pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
++skipped);
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ cstate->escontext->error_occurred = false;
+ cstate->escontext->details_wanted = true;
+ memset(cstate->escontext->error_data,0, sizeof(ErrorData));
+ }
continue;
}
@@ -1472,6 +1489,116 @@ BeginCopyFrom(ParseState *pstate,
else
cstate->escontext = NULL;
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ StringInfoData querybuf;
+ Oid err_tbl;
+ const char* copy_nspname;
+ Oid copy_nspoid;
+ bool on_error_tbl_ok;
+ bool isnull;
+
+ Assert(cstate->escontext != NULL);
+ Assert(cstate->opts.on_error_tbl != NULL);
+
+ /* COPY FROM destination table schemas should be same as the error_saving table */
+ copy_nspname = get_namespace_name(RelationGetNamespace(cstate->rel));
+ copy_nspoid = get_namespace_oid(copy_nspname, false);
+ err_tbl = get_relname_relid(cstate->opts.on_error_tbl, copy_nspoid);
+
+ if (!OidIsValid(err_tbl))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("error saving table \"%s\".\"%s\" does not exist",
+ copy_nspname, cstate->opts.on_error_tbl)));
+
+ /*
+ * we may need insert tuples to the error saving table, to do that we need
+ * first check the lock condition. If the table is already udner heavy locked,
+ * then our COPY operation would be stuck.
+ * instead of let COPY stuck, just error report that the table is in heavy lock.
+ */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "select 1 as exists from ( "
+ "select 1 "
+ "from pg_class, pg_locks "
+ "where pg_class.oid = pg_locks.relation "
+ "and pg_class.relnamespace = %d "
+ "and pg_class.oid = %d "
+ "and mode not in ('AccessShareLock', 'RowShareLock', 'RowExclusiveLock')); "
+ ,copy_nspoid, err_tbl);
+
+ 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);
+
+ if (SPI_processed != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\".\"%s\" was locked, cannot be used for error saving"
+ ,copy_nspname, cstate->opts.on_error_tbl)));
+ SPI_processed = 0;
+ resetStringInfo(&querybuf);
+
+ /*
+ *
+ * Verify whether the error saving table already exists, and if so,
+ * examine it's column names and data types. current user requires
+ * INSERT previledge for each columns in error saving table
+ *
+ */
+ appendStringInfo(&querybuf,
+ "SELECT (array_agg(pa.attname ORDER BY pa.attnum) "
+ "= '{ctid,userid,copy_tbl,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,
+ "pn.nspname = $$%s$$ AND relname = $$%s$$ "
+ " AND pa.attnum >= -1 AND NOT attisdropped "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 1::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 2::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 3::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 4::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 5::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 6::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 7::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 8::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 9::smallint, 'INSERT') "
+ "AND has_column_privilege (CURRENT_USER, $$%s$$, 10::smallint, 'INSERT') ",
+ copy_nspname, cstate->opts.on_error_tbl,
+ cstate->opts.on_error_tbl, cstate->opts.on_error_tbl, cstate->opts.on_error_tbl,
+ cstate->opts.on_error_tbl, cstate->opts.on_error_tbl, cstate->opts.on_error_tbl,
+ cstate->opts.on_error_tbl, cstate->opts.on_error_tbl, cstate->opts.on_error_tbl,
+ cstate->opts.on_error_tbl);
+
+ if (SPI_execute(querybuf.data, false, 0) != SPI_OK_SELECT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+ on_error_tbl_ok = DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0],
+ SPI_tuptable->tupdesc,
+ 1, &isnull));
+
+ if(!on_error_tbl_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\".\"%s\" cannot be used for COPY FROM error saving", copy_nspname, cstate->opts.on_error_tbl),
+ errdetail("The table (\"%s\".\"%s\") data definition cannot be used for error saving or current user don't enough priviledge on it",
+ copy_nspname, cstate->opts.on_error_tbl),
+ errhint("Ensure current user have enough priviledge on \"%s\".\"%s\", also ensure the data definition can be used for error saving",
+ copy_nspname, cstate->opts.on_error_tbl)));
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+ cstate->escontext->details_wanted = true;
+ }
/* 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 7cacd0b7..157017cf 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -66,6 +66,7 @@
#include "commands/copyfrom_internal.h"
#include "commands/progress.h"
#include "executor/executor.h"
+#include "access/heapam.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
@@ -850,10 +851,11 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
*
* 'values' and 'nulls' arrays must be the same length as columns of the
* relation passed to BeginCopyFrom. This function fills the arrays.
+ * if on_error is specified with 'table', then on_error_rel is the error saving table
*/
bool
NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
- Datum *values, bool *nulls)
+ Datum *values, bool *nulls, Relation on_error_rel)
{
TupleDesc tupDesc;
AttrNumber num_phys_attrs,
@@ -862,6 +864,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
FmgrInfo *in_functions = cstate->in_functions;
Oid *typioparams = cstate->typioparams;
int i;
+ HeapTuple on_error_tup;
+ TupleDesc on_error_tupDesc;
int *defmap = cstate->defmap;
ExprState **defexprs = cstate->defexprs;
@@ -968,6 +972,52 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
(Node *) cstate->escontext,
&values[m]))
{
+ if (cstate->opts.on_error == COPY_ON_ERROR_TABLE)
+ {
+ /*
+ * details of data definition of error saving table,
+ * see function BeginCopyFrom. here we based on node ErrorSaveContext
+ * content, form a tuple and insert tuple to the error saving table.
+ * we also did lock check in BeginCopyFrom.
+ */
+ char *err_detail;
+ char *err_code;
+ Datum t_values[10] = {0};
+ bool t_isnull[10] = {0};
+
+ 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(GetCurrentRoleId());
+ 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;
+ on_error_tupDesc = on_error_rel->rd_att;
+ on_error_tup = heap_form_tuple(on_error_tupDesc,
+ t_values,
+ t_isnull);
+ simple_heap_insert(on_error_rel, on_error_tup);
+ }
cstate->num_errors++;
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 130f7fc7..46fba94d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3420,6 +3420,10 @@ copy_opt_item:
{
$$ = makeDefElem("null", (Node *) makeString($3), @1);
}
+ | TABLE opt_as Sconst
+ {
+ $$ = makeDefElem("table", (Node *) makeString($3), @1);
+ }
| CSV
{
$$ = makeDefElem("format", (Node *) makeString("csv"), @1);
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index b3da3cb0..1db62e57 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -38,6 +38,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;
/*
@@ -73,6 +74,7 @@ typedef struct CopyFormatOptions
bool *force_null_flags; /* per-column CSV FN flags */
bool convert_selectively; /* do selective binary conversion? */
CopyOnErrorChoice on_error; /* what to do when error happened */
+ char *on_error_tbl; /* on error, save error info to table */
List *convert_select; /* list of column names (can be NIL) */
} CopyFormatOptions;
@@ -93,7 +95,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, Relation on_error_rel);
extern bool NextCopyFromRawFields(CopyFromState cstate,
char ***fields, int *nfields);
extern void CopyFromErrorCallback(void *arg);
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 25c401ce..423be157 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -751,6 +751,96 @@ CONTEXT: COPY check_ign_err, line 1: "1 {1}"
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"
+create table err_tbl(
+ userid oid, -- the user oid while copy generated this entry
+ copy_tbl oid, --copy table
+ filename text,
+ lineno int8,
+ line text,
+ colname text,
+ raw_field_value text,
+ err_message text,
+ err_detail text,
+ errorcode text
+);
+create table err_tbl_1(
+ userid oid, -- the user oid while copy generated this entry
+ copy_tbl oid, --copy table
+ filename text,
+ lineno int8,
+ line text,
+ colname text,
+ raw_field_value text,
+ err_message text,
+ err_detail text
+);
+create table t_copy_tbl(a int, b int, c int);
+--should fail.
+COPY t_copy_tbl FROM STDIN WITH (on_error 'table');
+ERROR: argument to option "table" can only applied when ON ERROR is specified
+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 FROM STDIN WITH (table err_tbl);
+ERROR: argument to option "table" can only applied when ON ERROR is specified
+COPY t_copy_tbl TO STDIN WITH (table err_tbl);
+ERROR: argument to option "table" can only applied when ON ERROR is specified
+COPY t_copy_tbl(a,b) FROM STDIN WITH (on_error 'table', table not_exists);
+ERROR: error saving table "public"."not_exists" does not exist
+--should fail. err_tbl_1 does not meet criteria
+COPY t_copy_tbl(a,b) FROM STDIN WITH (on_error 'table', table err_tbl_1);
+ERROR: table "public"."err_tbl_1" cannot be used for COPY FROM error saving
+DETAIL: The table ("public"."err_tbl_1") data definition cannot be used for error saving or current user don't enough priviledge on it
+HINT: Ensure current user have enough priviledge on "public"."err_tbl_1", also ensure the data definition can be used for error saving
+--should fail, 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, 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,"
+--ok.
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', on_error 'table', table err_tbl);
+NOTICE: 4 rows were skipped due to data type incompatibility
+--should fail. lack priviledge on column errorcode
+begin;
+create user regress_user20;
+grant insert(userid,copy_tbl,filename,lineno,line,colname,raw_field_value,err_message,err_detail) on table err_tbl to alice;
+ERROR: role "alice" does not exist
+grant insert on table t_copy_tbl to alice;
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+set role regress_user20;
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', on_error 'table', table err_tbl);
+ERROR: current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+select pg_class.relname as copy_destination,
+ filename
+ ,lineno
+ ,line
+ ,colname
+ ,raw_field_value,err_message
+ ,err_detail
+ ,errorcode
+from err_tbl join pg_class on copy_tbl = pg_class.oid;
+ copy_destination | 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 | 7 | 1,11,4238679732489879879 | c | 4238679732489879879 | value "4238679732489879879" is out of range for type integer | | 22003
+(4 rows)
+
+select * from t_copy_tbl;
+ a | b | c
+---+---+----
+ 1 | 2 | 3
+ 4 | 5 | 6
+ 8 | 9 | 10
+(3 rows)
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -767,6 +857,9 @@ DROP VIEW instead_of_insert_tbl_view_2;
DROP FUNCTION fun_instead_of_insert_tbl();
DROP TABLE check_ign_err;
DROP TABLE hard_err;
+DROP TABLE err_tbl;
+DROP TABLE err_tbl_1;
+DROP TABLE t_copy_tbl;
--
-- COPY FROM ... DEFAULT
--
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b5e549e8..811ebee3 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -534,6 +534,83 @@ COPY check_ign_err FROM STDIN WITH (on_error ignore);
1 {1} 3 abc
\.
+create table err_tbl(
+ userid oid, -- the user oid while copy generated this entry
+ copy_tbl oid, --copy table
+ filename text,
+ lineno int8,
+ line text,
+ colname text,
+ raw_field_value text,
+ err_message text,
+ err_detail text,
+ errorcode text
+);
+create table err_tbl_1(
+ userid oid, -- the user oid while copy generated this entry
+ copy_tbl oid, --copy table
+ filename text,
+ lineno int8,
+ line text,
+ colname text,
+ raw_field_value text,
+ err_message text,
+ err_detail text
+);
+create table t_copy_tbl(a int, b int, c int);
+
+--should fail.
+COPY t_copy_tbl FROM STDIN WITH (on_error 'table');
+COPY t_copy_tbl TO STDIN WITH (on_error 'table');
+COPY t_copy_tbl FROM STDIN WITH (table err_tbl);
+COPY t_copy_tbl TO STDIN WITH (table err_tbl);
+COPY t_copy_tbl(a,b) FROM STDIN WITH (on_error 'table', table not_exists);
+
+--should fail. err_tbl_1 does not meet criteria
+COPY t_copy_tbl(a,b) FROM STDIN WITH (on_error 'table', table err_tbl_1);
+
+--should fail, extra columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (delimiter ',', on_error 'table', table err_tbl);
+1,2,3,4
+\.
+
+--should fail, less columns
+COPY t_copy_tbl(a,b) FROM STDIN WITH (delimiter ',', on_error 'table', table err_tbl);
+1,2,
+\.
+
+--ok.
+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
+8,9,10
+1,11,4238679732489879879
+\.
+
+--should fail. lack priviledge on column errorcode
+begin;
+create user regress_user20;
+grant insert(userid,copy_tbl,filename,lineno,line,colname,raw_field_value,err_message,err_detail) on table err_tbl to alice;
+grant insert on table t_copy_tbl to alice;
+set role regress_user20;
+COPY t_copy_tbl FROM STDIN WITH (delimiter ',', on_error 'table', table err_tbl);
+ROLLBACK;
+
+select pg_class.relname as copy_destination,
+ filename
+ ,lineno
+ ,line
+ ,colname
+ ,raw_field_value,err_message
+ ,err_detail
+ ,errorcode
+from err_tbl join pg_class on copy_tbl = pg_class.oid;
+
+select * from t_copy_tbl;
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -550,6 +627,9 @@ DROP VIEW instead_of_insert_tbl_view_2;
DROP FUNCTION fun_instead_of_insert_tbl();
DROP TABLE check_ign_err;
DROP TABLE hard_err;
+DROP TABLE err_tbl;
+DROP TABLE err_tbl_1;
+DROP TABLE t_copy_tbl;
--
-- COPY FROM ... DEFAULT
--
2.34.1
