Skip to content

Commit cb92703

Browse files
tvondratvondra
committed
Adjust batch size in postgres_fdw to not use too many parameters
The FE/BE protocol identifies parameters with an Int16 index, which limits the maximum number of parameters per query to 65535. With batching added to postges_fdw this limit is much easier to hit, as the whole batch is essentially a single query, making this error much easier to hit. The failures are a bit unpredictable, because it also depends on the number of columns in the query. So instead of just failing, this patch tweaks the batch_size to not exceed the maximum number of parameters. Reported-by: Hou Zhijie <houzj.fnst@cn.fujitsu.com> Reviewed-by: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> Discussion: https://postgr.es/m/OS0PR01MB571603973C0AC2874AD6BF2594299%40OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parent d1f0aa7 commit cb92703

File tree

6 files changed

+52
-11
lines changed

6 files changed

+52
-11
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9680,6 +9680,17 @@ SELECT COUNT(*) FROM ftable;
96809680
34
96819681
(1 row)
96829682

9683+
TRUNCATE batch_table;
9684+
DROP FOREIGN TABLE ftable;
9685+
-- try if large batches exceed max number of bind parameters
9686+
CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' );
9687+
INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i;
9688+
SELECT COUNT(*) FROM ftable;
9689+
count
9690+
-------
9691+
70000
9692+
(1 row)
9693+
96839694
TRUNCATE batch_table;
96849695
DROP FOREIGN TABLE ftable;
96859696
-- Disable batch insert

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2030,7 +2030,7 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20302030
Assert(fmstate == NULL || fmstate->aux_fmstate == NULL);
20312031

20322032
/*
2033-
* In EXPLAIN without ANALYZE, ri_fdwstate is NULL, so we have to lookup
2033+
* In EXPLAIN without ANALYZE, ri_FdwState is NULL, so we have to lookup
20342034
* the option directly in server/table options. Otherwise just use the
20352035
* value we determined earlier.
20362036
*/
@@ -2045,7 +2045,14 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20452045
resultRelInfo->ri_TrigDesc->trig_insert_after_row))
20462046
return 1;
20472047

2048-
/* Otherwise use the batch size specified for server/table. */
2048+
/*
2049+
* Otherwise use the batch size specified for server/table. The number of
2050+
* parameters in a batch is limited to 65535 (uint16), so make sure we
2051+
* don't exceed this limit by using the maximum batch_size possible.
2052+
*/
2053+
if (fmstate && fmstate->p_nums > 0)
2054+
batch_size = Min(batch_size, PQ_QUERY_PARAM_MAX_LIMIT / fmstate->p_nums);
2055+
20492056
return batch_size;
20502057
}
20512058

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3026,6 +3026,13 @@ SELECT COUNT(*) FROM ftable;
30263026
TRUNCATE batch_table;
30273027
DROP FOREIGN TABLE ftable;
30283028

3029+
-- try if large batches exceed max number of bind parameters
3030+
CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' );
3031+
INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i;
3032+
SELECT COUNT(*) FROM ftable;
3033+
TRUNCATE batch_table;
3034+
DROP FOREIGN TABLE ftable;
3035+
30293036
-- Disable batch insert
30303037
CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '1' );
30313038
EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (1), (2);

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -372,6 +372,17 @@ OPTIONS (ADD password_required 'false');
372372
overrides an option specified for the server.
373373
The default is <literal>1</literal>.
374374
</para>
375+
376+
<para>
377+
Note the actual number of rows <filename>postgres_fdw</filename> inserts at
378+
once depends on the number of columns and the provided
379+
<literal>batch_size</literal> value. The batch is executed as a single
380+
query, and the libpq protocol (which <filename>postgres_fdw</filename>
381+
uses to connect to a remote server) limits the number of parameters in a
382+
single query to 65535. When the number of columns * <literal>batch_size</literal>
383+
exceeds the limit, the <literal>batch_size</literal> will be adjusted to
384+
avoid an error.
385+
</para>
375386
</listitem>
376387
</varlistentry>
377388

‎src/interfaces/libpq/fe-exec.c

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1403,10 +1403,11 @@ PQsendQueryParams(PGconn *conn,
14031403
libpq_gettext("command string is a null pointer\n"));
14041404
return 0;
14051405
}
1406-
if (nParams < 0 || nParams > 65535)
1406+
if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT)
14071407
{
1408-
appendPQExpBufferStr(&conn->errorMessage,
1409-
libpq_gettext("number of parameters must be between 0 and 65535\n"));
1408+
appendPQExpBuffer(&conn->errorMessage,
1409+
libpq_gettext("number of parameters must be between 0 and %d\n"),
1410+
PQ_QUERY_PARAM_MAX_LIMIT);
14101411
return 0;
14111412
}
14121413

@@ -1451,10 +1452,11 @@ PQsendPrepare(PGconn *conn,
14511452
libpq_gettext("command string is a null pointer\n"));
14521453
return 0;
14531454
}
1454-
if (nParams < 0 || nParams > 65535)
1455+
if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT)
14551456
{
1456-
appendPQExpBufferStr(&conn->errorMessage,
1457-
libpq_gettext("number of parameters must be between 0 and 65535\n"));
1457+
appendPQExpBuffer(&conn->errorMessage,
1458+
libpq_gettext("number of parameters must be between 0 and %d\n"),
1459+
PQ_QUERY_PARAM_MAX_LIMIT);
14581460
return 0;
14591461
}
14601462

@@ -1548,10 +1550,11 @@ PQsendQueryPrepared(PGconn *conn,
15481550
libpq_gettext("statement name is a null pointer\n"));
15491551
return 0;
15501552
}
1551-
if (nParams < 0 || nParams > 65535)
1553+
if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT)
15521554
{
1553-
appendPQExpBufferStr(&conn->errorMessage,
1554-
libpq_gettext("number of parameters must be between 0 and 65535\n"));
1555+
appendPQExpBuffer(&conn->errorMessage,
1556+
libpq_gettext("number of parameters must be between 0 and %d\n"),
1557+
PQ_QUERY_PARAM_MAX_LIMIT);
15551558
return 0;
15561559
}
15571560

‎src/interfaces/libpq/libpq-fe.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,8 @@ extern PGresult *PQexecPrepared(PGconn *conn,
429429
int resultFormat);
430430

431431
/* Interface for multiple-result or asynchronous queries */
432+
#define PQ_QUERY_PARAM_MAX_LIMIT 65535
433+
432434
extern int PQsendQuery(PGconn *conn, const char *query);
433435
extern int PQsendQueryParams(PGconn *conn,
434436
const char *command,

0 commit comments

Comments
 (0)