From cb92703384e2bb3fa0a690e5dbb95ad333c2b44c Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Tue, 8 Jun 2021 20:22:18 +0200 Subject: 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 Reviewed-by: Bharath Rupireddy Discussion: https://postgr.es/m/OS0PR01MB571603973C0AC2874AD6BF2594299%40OS0PR01MB5716.jpnprd01.prod.outlook.com --- contrib/postgres_fdw/expected/postgres_fdw.out | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'contrib/postgres_fdw/expected') diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7b7c0db16cf..1fb26639fcb 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -9680,6 +9680,17 @@ SELECT COUNT(*) FROM ftable; 34 (1 row) +TRUNCATE batch_table; +DROP FOREIGN TABLE ftable; +-- try if large batches exceed max number of bind parameters +CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' ); +INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i; +SELECT COUNT(*) FROM ftable; + count +------- + 70000 +(1 row) + TRUNCATE batch_table; DROP FOREIGN TABLE ftable; -- Disable batch insert -- cgit v1.2.3