Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf5a2278

Browse files
Add new COPY option LOG_VERBOSITY.
This commit adds a new COPY option LOG_VERBOSITY, which controls theamount of messages emitted during processing. Valid values are'default' and 'verbose'.This is currently used in COPY FROM when ON_ERROR option is set toignore. If 'verbose' is specified, a NOTICE message is emitted foreach discarded row, providing additional information such as linenumber, column name, and the malformed value. This helps users toidentify problematic rows that failed to load.Author: Bharath RupireddyReviewed-by: Michael Paquier, Atsushi Torikoshi, Masahiko SawadaDiscussion:https://www.postgresql.org/message-id/CALj2ACUk700cYhx1ATRQyRw-fBM%2BaRo6auRAitKGff7XNmYfqQ%40mail.gmail.com
1 parentf4ad002 commitf5a2278

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
returnCOPY_ON_ERROR_STOP;/* keep compiler quiet */
423423
}
424424

425+
/*
426+
* Extract a CopyLogVerbosityChoice value from a DefElem.
427+
*/
428+
staticCopyLogVerbosityChoice
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+
returnCOPY_LOG_VERBOSITY_DEFAULT;
439+
if (pg_strcasecmp(sval,"verbose")==0)
440+
returnCOPY_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+
returnCOPY_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
boolfreeze_specified= false;
449473
boolheader_specified= false;
450474
boolon_error_specified= false;
475+
boollog_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+
elseif (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-
staticchar*limit_printout_length(constchar*str);
105-
106104
staticvoidClosePipeFromProgram(CopyFromStatecstate);
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-
staticchar*
193-
limit_printout_length(constchar*str)
190+
char*
191+
CopyLimitPrintoutLength(constchar*str)
194192
{
195193
#defineMAX_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
elseif (Matches("COPY|\\copy",MatchAny,"FROM|TO",MatchAny,"WITH","(","FORMAT"))
@@ -2914,6 +2914,10 @@ psql_completion(const char *text, int start, int end)
29142914
elseif (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+
elseif (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
elseif (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+
typedefenumCopyLogVerbosityChoice
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
boolconvert_selectively;/* do selective binary conversion? */
7584
CopyOnErrorChoiceon_error;/* what to do when error happened */
85+
CopyLogVerbosityChoicelog_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
externboolNextCopyFromRawFields(CopyFromStatecstate,
98108
char***fields,int*nfields);
99109
externvoidCopyFromErrorCallback(void*arg);
110+
externchar*CopyLimitPrintoutLength(constchar*str);
100111

101112
externuint64CopyFrom(CopyFromStatecstate);
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 xfrom stdin (convert_selectively (a), convert_selectively (b));
6868
COPY xfrom stdin (encoding'sql_ascii', encoding'sql_ascii');
6969
COPY xfrom stdin (on_error ignore, on_error ignore);
70+
COPY xfrom 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 (formatTEXT, 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_errFROM STDIN WITH (on_error ignore);
513+
514+
-- want context for notices
515+
\set SHOW_CONTEXT always
516+
517+
COPY check_ign_errFROM 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+
CREATEDOMAINdcheck_ign_err2varchar(15)NOT NULL;
531+
CREATETABLEcheck_ign_err2 (nint, mint[], kint, l dcheck_ign_err2);
532+
COPY check_ign_err2FROM 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
CREATETABLEhard_err(foo widget);
526546
COPY hard_errFROM STDIN WITH (on_error ignore);
@@ -552,6 +572,8 @@ DROP VIEW instead_of_insert_tbl_view;
552572
DROPVIEW instead_of_insert_tbl_view_2;
553573
DROPFUNCTION fun_instead_of_insert_tbl();
554574
DROPTABLE check_ign_err;
575+
DROPTABLE check_ign_err2;
576+
DROPDOMAIN dcheck_ign_err2;
555577
DROPTABLE 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)
[8]ページ先頭
©2009-2025 Movatter.jp