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

Commitdc203dc

Browse files
committed
postgres_fdw: Allow fetch_size to be set per-table or per-server.
The default fetch size of 100 rows might not be right in everyenvironment, so allow users to configure it.Corey Huinker, reviewed by Kyotaro Horiguchi, Andres Freund, and me.
1 parente6ecc93 commitdc203dc

File tree

6 files changed

+180
-17
lines changed

6 files changed

+180
-17
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3951,3 +3951,63 @@ QUERY: CREATE FOREIGN TABLE t5 (
39513951
OPTIONS (schema_name 'import_source', table_name 't5');
39523952
CONTEXT: importing foreign table "t5"
39533953
ROLLBACK;
3954+
BEGIN;
3955+
CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
3956+
SELECT count(*)
3957+
FROM pg_foreign_server
3958+
WHERE srvname = 'fetch101'
3959+
AND srvoptions @> array['fetch_size=101'];
3960+
count
3961+
-------
3962+
1
3963+
(1 row)
3964+
3965+
ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' );
3966+
SELECT count(*)
3967+
FROM pg_foreign_server
3968+
WHERE srvname = 'fetch101'
3969+
AND srvoptions @> array['fetch_size=101'];
3970+
count
3971+
-------
3972+
0
3973+
(1 row)
3974+
3975+
SELECT count(*)
3976+
FROM pg_foreign_server
3977+
WHERE srvname = 'fetch101'
3978+
AND srvoptions @> array['fetch_size=202'];
3979+
count
3980+
-------
3981+
1
3982+
(1 row)
3983+
3984+
CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' );
3985+
SELECT COUNT(*)
3986+
FROM pg_foreign_table
3987+
WHERE ftrelid = 'table30000'::regclass
3988+
AND ftoptions @> array['fetch_size=30000'];
3989+
count
3990+
-------
3991+
1
3992+
(1 row)
3993+
3994+
ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000');
3995+
SELECT COUNT(*)
3996+
FROM pg_foreign_table
3997+
WHERE ftrelid = 'table30000'::regclass
3998+
AND ftoptions @> array['fetch_size=30000'];
3999+
count
4000+
-------
4001+
0
4002+
(1 row)
4003+
4004+
SELECT COUNT(*)
4005+
FROM pg_foreign_table
4006+
WHERE ftrelid = 'table30000'::regclass
4007+
AND ftoptions @> array['fetch_size=60000'];
4008+
count
4009+
-------
4010+
1
4011+
(1 row)
4012+
4013+
ROLLBACK;

‎contrib/postgres_fdw/option.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -131,6 +131,17 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
131131
/* check list syntax, warn about uninstalled extensions */
132132
(void)ExtractExtensionList(defGetString(def), true);
133133
}
134+
elseif (strcmp(def->defname,"fetch_size")==0)
135+
{
136+
intfetch_size;
137+
138+
fetch_size=strtol(defGetString(def),NULL,10);
139+
if (fetch_size <=0)
140+
ereport(ERROR,
141+
(errcode(ERRCODE_SYNTAX_ERROR),
142+
errmsg("%s requires a non-negative integer value",
143+
def->defname)));
144+
}
134145
}
135146

136147
PG_RETURN_VOID();
@@ -162,6 +173,9 @@ InitPgFdwOptions(void)
162173
/* updatable is available on both server and table */
163174
{"updatable",ForeignServerRelationId, false},
164175
{"updatable",ForeignTableRelationId, false},
176+
/* fetch_size is available on both server and table */
177+
{"fetch_size",ForeignServerRelationId, false},
178+
{"fetch_size",ForeignTableRelationId, false},
165179
{NULL,InvalidOid, false}
166180
};
167181

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 40 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -68,7 +68,9 @@ enum FdwScanPrivateIndex
6868
/* SQL statement to execute remotely (as a String node) */
6969
FdwScanPrivateSelectSql,
7070
/* Integer list of attribute numbers retrieved by the SELECT */
71-
FdwScanPrivateRetrievedAttrs
71+
FdwScanPrivateRetrievedAttrs,
72+
/* Integer representing the desired fetch_size */
73+
FdwScanPrivateFetchSize
7274
};
7375

7476
/*
@@ -126,6 +128,8 @@ typedef struct PgFdwScanState
126128
/* working memory contexts */
127129
MemoryContextbatch_cxt;/* context holding current batch of tuples */
128130
MemoryContexttemp_cxt;/* context for per-tuple temporary data */
131+
132+
intfetch_size;/* number of tuples per fetch */
129133
}PgFdwScanState;
130134

131135
/*
@@ -380,6 +384,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
380384
fpinfo->fdw_startup_cost=DEFAULT_FDW_STARTUP_COST;
381385
fpinfo->fdw_tuple_cost=DEFAULT_FDW_TUPLE_COST;
382386
fpinfo->shippable_extensions=NIL;
387+
fpinfo->fetch_size=100;
383388

384389
foreach(lc,fpinfo->server->options)
385390
{
@@ -394,16 +399,17 @@ postgresGetForeignRelSize(PlannerInfo *root,
394399
elseif (strcmp(def->defname,"extensions")==0)
395400
fpinfo->shippable_extensions=
396401
ExtractExtensionList(defGetString(def), false);
402+
elseif (strcmp(def->defname,"fetch_size")==0)
403+
fpinfo->fetch_size=strtol(defGetString(def),NULL,10);
397404
}
398405
foreach(lc,fpinfo->table->options)
399406
{
400407
DefElem*def= (DefElem*)lfirst(lc);
401408

402409
if (strcmp(def->defname,"use_remote_estimate")==0)
403-
{
404410
fpinfo->use_remote_estimate=defGetBoolean(def);
405-
break;/* only need the one value */
406-
}
411+
elseif (strcmp(def->defname,"fetch_size")==0)
412+
fpinfo->fetch_size=strtol(defGetString(def),NULL,10);
407413
}
408414

409415
/*
@@ -1012,6 +1018,9 @@ postgresGetForeignPlan(PlannerInfo *root,
10121018
*/
10131019
fdw_private=list_make2(makeString(sql.data),
10141020
retrieved_attrs);
1021+
fdw_private=list_make3(makeString(sql.data),
1022+
retrieved_attrs,
1023+
makeInteger(fpinfo->fetch_size));
10151024

10161025
/*
10171026
* Create the ForeignScan node from target list, filtering expressions,
@@ -1088,6 +1097,8 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
10881097
FdwScanPrivateSelectSql));
10891098
fsstate->retrieved_attrs= (List*)list_nth(fsplan->fdw_private,
10901099
FdwScanPrivateRetrievedAttrs);
1100+
fsstate->fetch_size=intVal(list_nth(fsplan->fdw_private,
1101+
FdwScanPrivateFetchSize));
10911102

10921103
/* Create contexts for batches of tuples and per-tuple temp workspace. */
10931104
fsstate->batch_cxt=AllocSetContextCreate(estate->es_query_cxt,
@@ -2214,15 +2225,11 @@ fetch_more_data(ForeignScanState *node)
22142225
{
22152226
PGconn*conn=fsstate->conn;
22162227
charsql[64];
2217-
intfetch_size;
22182228
intnumrows;
22192229
inti;
22202230

2221-
/* The fetch size is arbitrary, but shouldn't be enormous. */
2222-
fetch_size=100;
2223-
22242231
snprintf(sql,sizeof(sql),"FETCH %d FROM c%u",
2225-
fetch_size,fsstate->cursor_number);
2232+
fsstate->fetch_size,fsstate->cursor_number);
22262233

22272234
res=PQexec(conn,sql);
22282235
/* On error, report the original query, not the FETCH. */
@@ -2250,7 +2257,7 @@ fetch_more_data(ForeignScanState *node)
22502257
fsstate->fetch_ct_2++;
22512258

22522259
/* Must be EOF if we didn't get as many tuples as we asked for. */
2253-
fsstate->eof_reached= (numrows<fetch_size);
2260+
fsstate->eof_reached= (numrows<fsstate->fetch_size);
22542261

22552262
PQclear(res);
22562263
res=NULL;
@@ -2563,6 +2570,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
25632570
{
25642571
PgFdwAnalyzeStateastate;
25652572
ForeignTable*table;
2573+
ForeignServer*server;
25662574
UserMapping*user;
25672575
PGconn*conn;
25682576
unsignedintcursor_number;
@@ -2593,6 +2601,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
25932601
* owner, even if the ANALYZE was started by some other user.
25942602
*/
25952603
table=GetForeignTable(RelationGetRelid(relation));
2604+
server=GetForeignServer(table->serverid);
25962605
user=GetUserMapping(relation->rd_rel->relowner,table->serverid);
25972606
conn=GetConnection(user, false);
25982607

@@ -2620,6 +2629,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
26202629
intfetch_size;
26212630
intnumrows;
26222631
inti;
2632+
ListCell*lc;
26232633

26242634
/* Allow users to cancel long query */
26252635
CHECK_FOR_INTERRUPTS();
@@ -2632,6 +2642,26 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
26322642

26332643
/* The fetch size is arbitrary, but shouldn't be enormous. */
26342644
fetch_size=100;
2645+
foreach(lc,server->options)
2646+
{
2647+
DefElem*def= (DefElem*)lfirst(lc);
2648+
2649+
if (strcmp(def->defname,"fetch_size")==0)
2650+
{
2651+
fetch_size=strtol(defGetString(def),NULL,10);
2652+
break;
2653+
}
2654+
}
2655+
foreach(lc,table->options)
2656+
{
2657+
DefElem*def= (DefElem*)lfirst(lc);
2658+
2659+
if (strcmp(def->defname,"fetch_size")==0)
2660+
{
2661+
fetch_size=strtol(defGetString(def),NULL,10);
2662+
break;
2663+
}
2664+
}
26352665

26362666
/* Fetch some rows */
26372667
snprintf(fetch_sql,sizeof(fetch_sql),"FETCH %d FROM c%u",

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,8 @@ typedef struct PgFdwRelationInfo
5353
ForeignTable*table;
5454
ForeignServer*server;
5555
UserMapping*user;/* only set in use_remote_estimate mode */
56+
57+
intfetch_size;/* fetch size for this remote table */
5658
}PgFdwRelationInfo;
5759

5860
/* in postgres_fdw.c */

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -919,4 +919,48 @@ BEGIN;
919919
DROPTYPE"Colors" CASCADE;
920920
IMPORT FOREIGN SCHEMA import_sourceLIMIT TO (t5)
921921
FROM SERVER loopback INTO import_dest5;-- ERROR
922+
923+
ROLLBACK;
924+
925+
BEGIN;
926+
927+
928+
CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size'101' );
929+
930+
SELECTcount(*)
931+
FROM pg_foreign_server
932+
WHERE srvname='fetch101'
933+
AND srvoptions @> array['fetch_size=101'];
934+
935+
ALTER SERVER fetch101 OPTIONS(SET fetch_size'202' );
936+
937+
SELECTcount(*)
938+
FROM pg_foreign_server
939+
WHERE srvname='fetch101'
940+
AND srvoptions @> array['fetch_size=101'];
941+
942+
SELECTcount(*)
943+
FROM pg_foreign_server
944+
WHERE srvname='fetch101'
945+
AND srvoptions @> array['fetch_size=202'];
946+
947+
CREATE FOREIGN TABLE table30000 ( xint ) SERVER fetch101 OPTIONS ( fetch_size'30000' );
948+
949+
SELECTCOUNT(*)
950+
FROM pg_foreign_table
951+
WHERE ftrelid='table30000'::regclass
952+
AND ftoptions @> array['fetch_size=30000'];
953+
954+
ALTER FOREIGN TABLE table30000 OPTIONS (SET fetch_size'60000');
955+
956+
SELECTCOUNT(*)
957+
FROM pg_foreign_table
958+
WHERE ftrelid='table30000'::regclass
959+
AND ftoptions @> array['fetch_size=30000'];
960+
961+
SELECTCOUNT(*)
962+
FROM pg_foreign_table
963+
WHERE ftrelid='table30000'::regclass
964+
AND ftoptions @> array['fetch_size=60000'];
965+
922966
ROLLBACK;

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

Lines changed: 20 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -290,17 +290,30 @@
290290
be considered shippable to the remote server.
291291
This option can only be specified for foreign servers, not per-table.
292292
</para>
293+
294+
<para>
295+
When using the <literal>extensions</literal> option, <emphasis>it is the
296+
user's responsibility</> that the listed extensions exist and behave
297+
identically on both the local and remote servers. Otherwise, remote
298+
queries may fail or behave unexpectedly.
299+
</para>
293300
</listitem>
294301
</varlistentry>
295302

296-
</variablelist>
303+
<varlistentry>
304+
<term><literal>fetch_size</literal></term>
305+
<listitem>
306+
<para>
307+
This option specifies the number of rows <filename>postgres_fdw</>
308+
should get in each fetch operation. It can be specified for a foreign
309+
table or a foreign server. The option specified on a table overrides
310+
an option specified for the server.
311+
The default is <literal>100</>.
312+
</para>
313+
</listitem>
314+
</varlistentry>
297315

298-
<para>
299-
When using the <literal>extensions</literal> option, <emphasis>it is the
300-
user's responsibility</> that the listed extensions exist and behave
301-
identically on both the local and remote servers. Otherwise, remote
302-
queries may fail or behave unexpectedly.
303-
</para>
316+
</variablelist>
304317

305318
</sect3>
306319

0 commit comments

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