Skip to content

Commit f5a2278

Browse files
MasahikoSawadaMasahikoSawada
committed
Add new COPY option LOG_VERBOSITY.
This commit adds a new COPY option LOG_VERBOSITY, which controls the amount of messages emitted during processing. Valid values are 'default' and 'verbose'. This is currently used in COPY FROM when ON_ERROR option is set to ignore. If 'verbose' is specified, a NOTICE message is emitted for each discarded row, providing additional information such as line number, column name, and the malformed value. This helps users to identify problematic rows that failed to load. Author: Bharath Rupireddy Reviewed-by: Michael Paquier, Atsushi Torikoshi, Masahiko Sawada Discussion: https://www.postgresql.org/message-id/CALj2ACUk700cYhx1ATRQyRw-fBM%2BaRo6auRAitKGff7XNmYfqQ%40mail.gmail.com
1 parent f4ad002 commit f5a2278

File tree

10 files changed

+175
-11
lines changed

10 files changed

+175
-11
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 23 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
4545
FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
4646
ON_ERROR '<replaceable class="parameter">error_action</replaceable>'
4747
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
48+
LOG_VERBOSITY <replaceable class="parameter">mode</replaceable>
4849
</synopsis>
4950
</refsynopsisdiv>
5051

@@ -400,8 +401,12 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
400401
when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
401402
</para>
402403
<para>
403-
A <literal>NOTICE</literal> message containing the ignored row count is emitted at the end
404-
of the <command>COPY FROM</command> if at least one row was discarded.
404+
A <literal>NOTICE</literal> message containing the ignored row count is
405+
emitted at the end of the <command>COPY FROM</command> if at least one
406+
row was discarded. When <literal>LOG_VERBOSITY</literal> option is set to
407+
<literal>verbose</literal>, a <literal>NOTICE</literal> message
408+
containing the line of the input file and the column name whose input
409+
conversion has failed is emitted for each discarded row.
405410
</para>
406411
</listitem>
407412
</varlistentry>
@@ -418,6 +423,22 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
418423
</listitem>
419424
</varlistentry>
420425

426+
<varlistentry>
427+
<term><literal>LOG_VERBOSITY</literal></term>
428+
<listitem>
429+
<para>
430+
Specify the amount of messages emitted by a <command>COPY</command>
431+
command: <literal>default</literal> or <literal>verbose</literal>. If
432+
<literal>verbose</literal> is specified, additional messages are emitted
433+
during processing.
434+
</para>
435+
<para>
436+
This is currently used in <command>COPY FROM</command> command when
437+
<literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
438+
</para>
439+
</listitem>
440+
</varlistentry>
441+
421442
<varlistentry>
422443
<term><literal>WHERE</literal></term>
423444
<listitem>

‎src/backend/commands/copy.c

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -422,6 +422,30 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
422422
return COPY_ON_ERROR_STOP; /* keep compiler quiet */
423423
}
424424

425+
/*
426+
* Extract a CopyLogVerbosityChoice value from a DefElem.
427+
*/
428+
static CopyLogVerbosityChoice
429+
defGetCopyLogVerbosityChoice(DefElem *def, ParseState *pstate)
430+
{
431+
char *sval;
432+
433+
/*
434+
* Allow "default", or "verbose" values.
435+
*/
436+
sval = defGetString(def);
437+
if (pg_strcasecmp(sval, "default") == 0)
438+
return COPY_LOG_VERBOSITY_DEFAULT;
439+
if (pg_strcasecmp(sval, "verbose") == 0)
440+
return COPY_LOG_VERBOSITY_VERBOSE;
441+
442+
ereport(ERROR,
443+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
444+
errmsg("COPY LOG_VERBOSITY \"%s\" not recognized", sval),
445+
parser_errposition(pstate, def->location)));
446+
return COPY_LOG_VERBOSITY_DEFAULT; /* keep compiler quiet */
447+
}
448+
425449
/*
426450
* Process the statement option list for COPY.
427451
*
@@ -448,6 +472,7 @@ ProcessCopyOptions(ParseState *pstate,
448472
bool freeze_specified = false;
449473
bool header_specified = false;
450474
bool on_error_specified = false;
475+
bool log_verbosity_specified = false;
451476
ListCell *option;
452477

453478
/* Support external use for option sanity checking */
@@ -607,6 +632,13 @@ ProcessCopyOptions(ParseState *pstate,
607632
on_error_specified = true;
608633
opts_out->on_error = defGetCopyOnErrorChoice(defel, pstate, is_from);
609634
}
635+
else if (strcmp(defel->defname, "log_verbosity") == 0)
636+
{
637+
if (log_verbosity_specified)
638+
errorConflictingDefElem(defel, pstate);
639+
log_verbosity_specified = true;
640+
opts_out->log_verbosity = defGetCopyLogVerbosityChoice(defel, pstate);
641+
}
610642
else
611643
ereport(ERROR,
612644
(errcode(ERRCODE_SYNTAX_ERROR),

‎src/backend/commands/copyfrom.c

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -101,8 +101,6 @@ typedef struct CopyMultiInsertInfo
101101

102102

103103
/* non-export function prototypes */
104-
static char *limit_printout_length(const char *str);
105-
106104
static void ClosePipeFromProgram(CopyFromState cstate);
107105

108106
/*
@@ -141,7 +139,7 @@ CopyFromErrorCallback(void *arg)
141139
/* error is relevant to a particular column */
142140
char *attval;
143141

144-
attval = limit_printout_length(cstate->cur_attval);
142+
attval = CopyLimitPrintoutLength(cstate->cur_attval);
145143
errcontext("COPY %s, line %llu, column %s: \"%s\"",
146144
cstate->cur_relname,
147145
(unsigned long long) cstate->cur_lineno,
@@ -168,7 +166,7 @@ CopyFromErrorCallback(void *arg)
168166
{
169167
char *lineval;
170168

171-
lineval = limit_printout_length(cstate->line_buf.data);
169+
lineval = CopyLimitPrintoutLength(cstate->line_buf.data);
172170
errcontext("COPY %s, line %llu: \"%s\"",
173171
cstate->cur_relname,
174172
(unsigned long long) cstate->cur_lineno, lineval);
@@ -189,8 +187,8 @@ CopyFromErrorCallback(void *arg)
189187
*
190188
* Returns a pstrdup'd copy of the input.
191189
*/
192-
static char *
193-
limit_printout_length(const char *str)
190+
char *
191+
CopyLimitPrintoutLength(const char *str)
194192
{
195193
#define MAX_COPY_DATA_DISPLAY 100
196194

‎src/backend/commands/copyfromparse.c

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -967,7 +967,42 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
967967
(Node *) cstate->escontext,
968968
&values[m]))
969969
{
970+
Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
971+
970972
cstate->num_errors++;
973+
974+
if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
975+
{
976+
/*
977+
* Since we emit line number and column info in the below
978+
* notice message, we suppress error context information
979+
* other than the relation name.
980+
*/
981+
Assert(!cstate->relname_only);
982+
cstate->relname_only = true;
983+
984+
if (cstate->cur_attval)
985+
{
986+
char *attval;
987+
988+
attval = CopyLimitPrintoutLength(cstate->cur_attval);
989+
ereport(NOTICE,
990+
errmsg("skipping row due to data type incompatibility at line %llu for column %s: \"%s\"",
991+
(unsigned long long) cstate->cur_lineno,
992+
cstate->cur_attname,
993+
attval));
994+
pfree(attval);
995+
}
996+
else
997+
ereport(NOTICE,
998+
errmsg("skipping row due to data type incompatibility at line %llu for column %s: null input",
999+
(unsigned long long) cstate->cur_lineno,
1000+
cstate->cur_attname));
1001+
1002+
/* reset relname_only */
1003+
cstate->relname_only = false;
1004+
}
1005+
9711006
return true;
9721007
}
9731008

‎src/backend/parser/gram.y

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3530,6 +3530,7 @@ copy_generic_opt_arg:
35303530
opt_boolean_or_string { $$ = (Node *) makeString($1); }
35313531
| NumericOnly { $$ = (Node *) $1; }
35323532
| '*' { $$ = (Node *) makeNode(A_Star); }
3533+
| DEFAULT { $$ = (Node *) makeString("default"); }
35333534
| '(' copy_generic_opt_arg_list ')' { $$ = (Node *) $2; }
35343535
| /* EMPTY */ { $$ = NULL; }
35353536
;

‎src/bin/psql/tab-complete.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2904,7 +2904,7 @@ psql_completion(const char *text, int start, int end)
29042904
COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
29052905
"HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
29062906
"FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
2907-
"ON_ERROR");
2907+
"ON_ERROR", "LOG_VERBOSITY");
29082908

29092909
/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
29102910
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
@@ -2914,6 +2914,10 @@ psql_completion(const char *text, int start, int end)
29142914
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR"))
29152915
COMPLETE_WITH("stop", "ignore");
29162916

2917+
/* Complete COPY <sth> FROM filename WITH (LOG_VERBOSITY */
2918+
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "LOG_VERBOSITY"))
2919+
COMPLETE_WITH("default", "verbose");
2920+
29172921
/* Complete COPY <sth> FROM <sth> WITH (<options>) */
29182922
else if (Matches("COPY|\\copy", MatchAny, "FROM", MatchAny, "WITH", MatchAny))
29192923
COMPLETE_WITH("WHERE");

‎src/include/commands/copy.h

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,15 @@ typedef enum CopyOnErrorChoice
4040
COPY_ON_ERROR_IGNORE, /* ignore errors */
4141
} CopyOnErrorChoice;
4242

43+
/*
44+
* Represents verbosity of logged messages by COPY command.
45+
*/
46+
typedef enum CopyLogVerbosityChoice
47+
{
48+
COPY_LOG_VERBOSITY_DEFAULT = 0, /* logs no additional messages, default */
49+
COPY_LOG_VERBOSITY_VERBOSE, /* logs additional messages */
50+
} CopyLogVerbosityChoice;
51+
4352
/*
4453
* A struct to hold COPY options, in a parsed form. All of these are related
4554
* to formatting, except for 'freeze', which doesn't really belong here, but
@@ -73,6 +82,7 @@ typedef struct CopyFormatOptions
7382
bool *force_null_flags; /* per-column CSV FN flags */
7483
bool convert_selectively; /* do selective binary conversion? */
7584
CopyOnErrorChoice on_error; /* what to do when error happened */
85+
CopyLogVerbosityChoice log_verbosity; /* verbosity of logged messages */
7686
List *convert_select; /* list of column names (can be NIL) */
7787
} CopyFormatOptions;
7888

@@ -97,6 +107,7 @@ extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
97107
extern bool NextCopyFromRawFields(CopyFromState cstate,
98108
char ***fields, int *nfields);
99109
extern void CopyFromErrorCallback(void *arg);
110+
extern char *CopyLimitPrintoutLength(const char *str);
100111

101112
extern uint64 CopyFrom(CopyFromState cstate);
102113

‎src/test/regress/expected/copy2.out

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -81,6 +81,10 @@ COPY x from stdin (on_error ignore, on_error ignore);
8181
ERROR: conflicting or redundant options
8282
LINE 1: COPY x from stdin (on_error ignore, on_error ignore);
8383
^
84+
COPY x from stdin (log_verbosity default, log_verbosity verbose);
85+
ERROR: conflicting or redundant options
86+
LINE 1: COPY x from stdin (log_verbosity default, log_verbosity verb...
87+
^
8488
-- incorrect options
8589
COPY x to stdin (format BINARY, delimiter ',');
8690
ERROR: cannot specify DELIMITER in BINARY mode
@@ -108,6 +112,10 @@ COPY x to stdin (format BINARY, on_error unsupported);
108112
ERROR: COPY ON_ERROR cannot be used with COPY TO
109113
LINE 1: COPY x to stdin (format BINARY, on_error unsupported);
110114
^
115+
COPY x to stdout (log_verbosity unsupported);
116+
ERROR: COPY LOG_VERBOSITY "unsupported" not recognized
117+
LINE 1: COPY x to stdout (log_verbosity unsupported);
118+
^
111119
-- too many columns in column list: should fail
112120
COPY x (a, b, c, d, e, d, c) from stdin;
113121
ERROR: column "d" specified more than once
@@ -729,8 +737,31 @@ CREATE TABLE check_ign_err (n int, m int[], k int);
729737
COPY check_ign_err FROM STDIN WITH (on_error stop);
730738
ERROR: invalid input syntax for type integer: "a"
731739
CONTEXT: COPY check_ign_err, line 2, column n: "a"
732-
COPY check_ign_err FROM STDIN WITH (on_error ignore);
740+
-- want context for notices
741+
\set SHOW_CONTEXT always
742+
COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity verbose);
743+
NOTICE: skipping row due to data type incompatibility at line 2 for column n: "a"
744+
CONTEXT: COPY check_ign_err
745+
NOTICE: skipping row due to data type incompatibility at line 3 for column k: "3333333333"
746+
CONTEXT: COPY check_ign_err
747+
NOTICE: skipping row due to data type incompatibility at line 4 for column m: "{a, 4}"
748+
CONTEXT: COPY check_ign_err
749+
NOTICE: skipping row due to data type incompatibility at line 5 for column n: ""
750+
CONTEXT: COPY check_ign_err
751+
NOTICE: skipping row due to data type incompatibility at line 7 for column m: "a"
752+
CONTEXT: COPY check_ign_err
753+
NOTICE: skipping row due to data type incompatibility at line 8 for column k: "a"
754+
CONTEXT: COPY check_ign_err
733755
NOTICE: 6 rows were skipped due to data type incompatibility
756+
-- tests for on_error option with log_verbosity and null constraint via domain
757+
CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
758+
CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
759+
COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
760+
NOTICE: skipping row due to data type incompatibility at line 2 for column l: null input
761+
CONTEXT: COPY check_ign_err2
762+
NOTICE: 1 row was skipped due to data type incompatibility
763+
-- reset context choice
764+
\set SHOW_CONTEXT errors
734765
SELECT * FROM check_ign_err;
735766
n | m | k
736767
---+-----+---
@@ -739,6 +770,12 @@ SELECT * FROM check_ign_err;
739770
8 | {8} | 8
740771
(3 rows)
741772

773+
SELECT * FROM check_ign_err2;
774+
n | m | k | l
775+
---+-----+---+-------
776+
1 | {1} | 1 | 'foo'
777+
(1 row)
778+
742779
-- test datatype error that can't be handled as soft: should fail
743780
CREATE TABLE hard_err(foo widget);
744781
COPY hard_err FROM STDIN WITH (on_error ignore);
@@ -767,6 +804,8 @@ DROP VIEW instead_of_insert_tbl_view;
767804
DROP VIEW instead_of_insert_tbl_view_2;
768805
DROP FUNCTION fun_instead_of_insert_tbl();
769806
DROP TABLE check_ign_err;
807+
DROP TABLE check_ign_err2;
808+
DROP DOMAIN dcheck_ign_err2;
770809
DROP TABLE hard_err;
771810
--
772811
-- COPY FROM ... DEFAULT

‎src/test/regress/sql/copy2.sql

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@ COPY x from stdin (force_null (a), force_null (b));
6767
COPY x from stdin (convert_selectively (a), convert_selectively (b));
6868
COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
6969
COPY x from stdin (on_error ignore, on_error ignore);
70+
COPY x from stdin (log_verbosity default, log_verbosity verbose);
7071

7172
-- incorrect options
7273
COPY x to stdin (format BINARY, delimiter ',');
@@ -80,6 +81,7 @@ COPY x to stdin (format CSV, force_not_null(a));
8081
COPY x to stdout (format TEXT, force_null(a));
8182
COPY x to stdin (format CSV, force_null(a));
8283
COPY x to stdin (format BINARY, on_error unsupported);
84+
COPY x to stdout (log_verbosity unsupported);
8385

8486
-- too many columns in column list: should fail
8587
COPY x (a, b, c, d, e, d, c) from stdin;
@@ -508,7 +510,11 @@ a {2} 2
508510

509511
5 {5} 5
510512
\.
511-
COPY check_ign_err FROM STDIN WITH (on_error ignore);
513+
514+
-- want context for notices
515+
\set SHOW_CONTEXT always
516+
517+
COPY check_ign_err FROM STDIN WITH (on_error ignore, log_verbosity verbose);
512518
1 {1} 1
513519
a {2} 2
514520
3 {3} 3333333333
@@ -519,8 +525,22 @@ a {2} 2
519525
7 {7} a
520526
8 {8} 8
521527
\.
528+
529+
-- tests for on_error option with log_verbosity and null constraint via domain
530+
CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
531+
CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
532+
COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
533+
1 {1} 1 'foo'
534+
2 {2} 2 \N
535+
\.
536+
537+
-- reset context choice
538+
\set SHOW_CONTEXT errors
539+
522540
SELECT * FROM check_ign_err;
523541

542+
SELECT * FROM check_ign_err2;
543+
524544
-- test datatype error that can't be handled as soft: should fail
525545
CREATE TABLE hard_err(foo widget);
526546
COPY hard_err FROM STDIN WITH (on_error ignore);
@@ -552,6 +572,8 @@ DROP VIEW instead_of_insert_tbl_view;
552572
DROP VIEW instead_of_insert_tbl_view_2;
553573
DROP FUNCTION fun_instead_of_insert_tbl();
554574
DROP TABLE check_ign_err;
575+
DROP TABLE check_ign_err2;
576+
DROP DOMAIN dcheck_ign_err2;
555577
DROP TABLE hard_err;
556578

557579
--

‎src/tools/pgindent/typedefs.list

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -480,6 +480,7 @@ CopyFromState
480480
CopyFromStateData
481481
CopyHeaderChoice
482482
CopyInsertMethod
483+
CopyLogVerbosityChoice
483484
CopyMultiInsertBuffer
484485
CopyMultiInsertInfo
485486
CopyOnErrorChoice

0 commit comments

Comments
 (0)