Skip to content

Commit dc203dc

Browse files
robertmhaasrobertmhaas
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 every environment, so allow users to configure it. Corey Huinker, reviewed by Kyotaro Horiguchi, Andres Freund, and me.
1 parent e6ecc93 commit dc203dc

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+
else if (strcmp(def->defname, "fetch_size") == 0)
135+
{
136+
int fetch_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
MemoryContext batch_cxt; /* context holding current batch of tuples */
128130
MemoryContext temp_cxt; /* context for per-tuple temporary data */
131+
132+
int fetch_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
else if (strcmp(def->defname, "extensions") == 0)
395400
fpinfo->shippable_extensions =
396401
ExtractExtensionList(defGetString(def), false);
402+
else if (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+
else if (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
char sql[64];
2217-
int fetch_size;
22182228
int numrows;
22192229
int i;
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
PgFdwAnalyzeState astate;
25652572
ForeignTable *table;
2573+
ForeignServer *server;
25662574
UserMapping *user;
25672575
PGconn *conn;
25682576
unsigned int cursor_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
int fetch_size;
26212630
int numrows;
26222631
int i;
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+
int fetch_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
DROP TYPE "Colors" CASCADE;
920920
IMPORT FOREIGN SCHEMA import_source LIMIT 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+
SELECT count(*)
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+
SELECT count(*)
938+
FROM pg_foreign_server
939+
WHERE srvname = 'fetch101'
940+
AND srvoptions @> array['fetch_size=101'];
941+
942+
SELECT count(*)
943+
FROM pg_foreign_server
944+
WHERE srvname = 'fetch101'
945+
AND srvoptions @> array['fetch_size=202'];
946+
947+
CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' );
948+
949+
SELECT COUNT(*)
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+
SELECT COUNT(*)
957+
FROM pg_foreign_table
958+
WHERE ftrelid = 'table30000'::regclass
959+
AND ftoptions @> array['fetch_size=30000'];
960+
961+
SELECT COUNT(*)
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)