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

Commitcb92703

Browse files
committed
Adjust batch size in postgres_fdw to not use too many parameters
The FE/BE protocol identifies parameters with an Int16 index, whichlimits the maximum number of parameters per query to 65535. Withbatching added to postges_fdw this limit is much easier to hit, asthe whole batch is essentially a single query, making this error mucheasier to hit.The failures are a bit unpredictable, because it also depends on thenumber of columns in the query. So instead of just failing, this patchtweaks 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 parentd1f0aa7 commitcb92703

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
return1;
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
returnbatch_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 ( xint ) SERVER loopback OPTIONS ( table_name'batch_table', batch_size'100000' );
3031+
INSERT INTO ftableSELECT*FROM generate_series(1,70000) i;
3032+
SELECTCOUNT(*)FROM ftable;
3033+
TRUNCATE batch_table;
3034+
DROP FOREIGN TABLE ftable;
3035+
30293036
-- Disable batch insert
30303037
CREATE FOREIGN TABLE ftable ( xint ) SERVER loopback OPTIONS ( table_name'batch_table', batch_size'1' );
30313038
EXPLAIN (VERBOSE, COSTS OFF)INSERT INTO ftableVALUES (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
return0;
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
return0;
14111412
}
14121413

@@ -1451,10 +1452,11 @@ PQsendPrepare(PGconn *conn,
14511452
libpq_gettext("command string is a null pointer\n"));
14521453
return0;
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
return0;
14591461
}
14601462

@@ -1548,10 +1550,11 @@ PQsendQueryPrepared(PGconn *conn,
15481550
libpq_gettext("statement name is a null pointer\n"));
15491551
return0;
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
return0;
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
intresultFormat);
430430

431431
/* Interface for multiple-result or asynchronous queries */
432+
#definePQ_QUERY_PARAM_MAX_LIMIT 65535
433+
432434
externintPQsendQuery(PGconn*conn,constchar*query);
433435
externintPQsendQueryParams(PGconn*conn,
434436
constchar*command,

0 commit comments

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