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

Commit6c8f670

Browse files
committed
file_fdw: Add REJECT_LIMIT option to file_fdw.
Commit4ac2a9b introduced the REJECT_LIMIT option for the COPYcommand. This commit extends the support for this option to file_fdw.As well as REJECT_LIMIT option for COPY, this option limitsthe maximum number of erroneous rows that can be skipped.If the number of data type conversion errors exceeds this limit,accessing the file_fdw foreign table will fail with an error,even when on_error = 'ignore' is specified.Since the CREATE/ALTER FOREIGN TABLE commands require foreigntable options to be single-quoted, this commit updatesdefGetCopyRejectLimitOption() to handle also string value for them,in addition to int64 value for COPY command option.Author: Atsushi TorikoshiReviewed-by: Fujii Masao, Yugo Nagata, Kirill ReshkeDiscussion:https://postgr.es/m/bab68a9fc502b12693f0755b6f35f327@oss.nttdata.com
1 parent15afb7d commit6c8f670

File tree

6 files changed

+58
-4
lines changed

6 files changed

+58
-4
lines changed

‎contrib/file_fdw/data/agg.bad‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,3 +2,4 @@
22
100;@99.097@
33
0;@aaa@
44
42;@324.78@
5+
1;@bbb@

‎contrib/file_fdw/expected/file_fdw.out‎

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,8 @@ ERROR: COPY delimiter cannot be newline or carriage return
9090
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
9191
'); -- ERROR
9292
ERROR: COPY null representation cannot use newline or carriage return
93+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (reject_limit '1'); -- ERROR
94+
ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
9395
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
9496
ERROR: either filename or program is required for file_fdw foreign tables
9597
\set filename :abs_srcdir '/data/agg.data'
@@ -206,10 +208,10 @@ SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
206208
SELECT * FROM agg_bad; -- ERROR
207209
ERROR: invalid input syntax for type real: "aaa"
208210
CONTEXT: COPY agg_bad, line 3, column b: "aaa"
209-
-- on_errorandlog_verbosity tests
211+
-- on_error, log_verbosityandreject_limit tests
210212
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'ignore');
211213
SELECT * FROM agg_bad;
212-
NOTICE:1 row was skipped due to data type incompatibility
214+
NOTICE:2 rows were skipped due to data type incompatibility
213215
a | b
214216
-----+--------
215217
100 | 99.097
@@ -224,6 +226,18 @@ SELECT * FROM agg_bad;
224226
42 | 324.78
225227
(2 rows)
226228

229+
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD reject_limit '1'); -- ERROR
230+
SELECT * FROM agg_bad;
231+
ERROR: skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
232+
CONTEXT: COPY agg_bad, line 5, column b: "bbb"
233+
ALTER FOREIGN TABLE agg_bad OPTIONS (SET reject_limit '2');
234+
SELECT * FROM agg_bad;
235+
a | b
236+
-----+--------
237+
100 | 99.097
238+
42 | 324.78
239+
(2 rows)
240+
227241
ANALYZE agg_bad;
228242
-- misc query tests
229243
\t on

‎contrib/file_fdw/file_fdw.c‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ static const struct FileFdwOption valid_options[] = {
7777
{"encoding",ForeignTableRelationId},
7878
{"on_error",ForeignTableRelationId},
7979
{"log_verbosity",ForeignTableRelationId},
80+
{"reject_limit",ForeignTableRelationId},
8081
{"force_not_null",AttributeRelationId},
8182
{"force_null",AttributeRelationId},
8283

@@ -788,6 +789,13 @@ fileIterateForeignScan(ForeignScanState *node)
788789
*/
789790
ResetPerTupleExprContext(estate);
790791

792+
if (cstate->opts.reject_limit>0&&
793+
cstate->num_errors>cstate->opts.reject_limit)
794+
ereport(ERROR,
795+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
796+
errmsg("skipped more than REJECT_LIMIT (%lld) rows due to data type incompatibility",
797+
(long long)cstate->opts.reject_limit)));
798+
791799
/* Repeat NextCopyFrom() until no soft error occurs */
792800
gotoretry;
793801
}

‎contrib/file_fdw/sql/file_fdw.sql‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter
7777
'); -- ERROR
7878
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format'csv', null'
7979
'); -- ERROR
80+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (reject_limit'1'); -- ERROR
8081
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
8182
8283
\set filename :abs_srcdir'/data/agg.data'
@@ -150,11 +151,15 @@ SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
150151
-- error context report tests
151152
SELECT * FROM agg_bad; -- ERROR
152153
153-
-- on_errorandlog_verbosity tests
154+
-- on_error, log_verbosityandreject_limit tests
154155
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error'ignore');
155156
SELECT * FROM agg_bad;
156157
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD log_verbosity'silent');
157158
SELECT * FROM agg_bad;
159+
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD reject_limit'1'); -- ERROR
160+
SELECT * FROM agg_bad;
161+
ALTER FOREIGN TABLE agg_bad OPTIONS (SET reject_limit'2');
162+
SELECT * FROM agg_bad;
158163
ANALYZE agg_bad;
159164
160165
-- misc query tests

‎doc/src/sgml/file-fdw.sgml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,18 @@
138138
</listitem>
139139
</varlistentry>
140140

141+
<varlistentry>
142+
<term><literal>reject_limit</literal></term>
143+
144+
<listitem>
145+
<para>
146+
Specifies the maximum number of errors tolerated while converting a column's
147+
input value to its data type, the same as <command>COPY</command>'s
148+
<literal>REJECT_LIMIT</literal> option.
149+
</para>
150+
</listitem>
151+
</varlistentry>
152+
141153
<varlistentry>
142154
<term><literal>log_verbosity</literal></term>
143155

‎src/backend/commands/copy.c‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -420,11 +420,25 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
420420

421421
/*
422422
* Extract REJECT_LIMIT value from a DefElem.
423+
*
424+
* REJECT_LIMIT can be specified in two ways: as an int64 for the COPY command
425+
* option or as a single-quoted string for the foreign table option using
426+
* file_fdw. Therefore this function needs to handle both formats.
423427
*/
424428
staticint64
425429
defGetCopyRejectLimitOption(DefElem*def)
426430
{
427-
int64reject_limit=defGetInt64(def);
431+
int64reject_limit;
432+
433+
if (def->arg==NULL)
434+
ereport(ERROR,
435+
(errcode(ERRCODE_SYNTAX_ERROR),
436+
errmsg("%s requires a numeric value",
437+
def->defname)));
438+
elseif (nodeTag(def->arg)==T_String)
439+
reject_limit=pg_strtoint64(strVal(def->arg));
440+
else
441+
reject_limit=defGetInt64(def);
428442

429443
if (reject_limit <=0)
430444
ereport(ERROR,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp