Horton

Fdw batch insert error out when set batch_size > 65535

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>

View thread View on postgr.es
From: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-05-21T02:48:05Z
Lists: pgsql-hackers

Attachments

Hi,

When reading the code, I noticed some possible issue about fdw batch insert.
When I set batch_size > 65535 and insert more than 65535 rows into foreign table, 
It will throw an error:

For example:

------------------
CREATE FOREIGN TABLE vzdalena_tabulka2(a int, b varchar)
  SERVER omega_db
  OPTIONS (table_name 'tabulka', batch_size '65536');

INSERT INTO vzdalena_tabulka2 SELECT i, 'AHOJ' || i FROM generate_series(1,65536) g(i);

ERROR:  number of parameters must be between 0 and 65535
CONTEXT:  remote SQL command: INSERT INTO public.tabulka(a, b) VALUES ($1, $2), ($3, $4)...
------------------

Actually, I think if the (number of columns) * (number of rows) > 65535, then we can
get this error. But, I think almost no one will set such a large value, so I think adjust the
batch_size automatically when doing INSERT seems an acceptable solution.

In the postgresGetForeignModifyBatchSize(), if we found the (num of param) * batch_size
Is greater than the limit(65535), then we set it to 65535 / (num of param).

Thoughts ?

Best regards,
houzj

Commits

  1. Remove expensive test of postgres_fdw batch inserts

  2. Adjust batch size in postgres_fdw to not use too many parameters

  3. Implement support for bulk inserts in postgres_fdw