From 97da48246d34807196b404626f019c767b7af0df Mon Sep 17 00:00:00 2001 From: Etsuro Fujita Date: Thu, 13 Oct 2022 18:45:00 +0900 Subject: Allow batch insertion during COPY into a foreign table. Commit 3d956d956 allowed the COPY, but it's done by inserting individual rows to the foreign table, so it can be inefficient due to the overhead caused by each round-trip to the foreign server. To improve performance of the COPY in such a case, this patch allows batch insertion, by extending the multi-insert machinery in CopyFrom() to the foreign-table case so that we insert multiple rows to the foreign table at once using the FDW callback routine added by commit b663a4136. This patch also allows this for postgres_fdw. It is enabled by the "batch_size" option added by commit b663a4136, which is disabled by default. When doing batch insertion, we update progress of the COPY command after performing the FDW callback routine, to count rows not suppressed by the FDW as well as a BEFORE ROW INSERT trigger. For consistency, this patch changes the timing of updating it for plain tables: previously, we updated it immediately after adding each row to the multi-insert buffer, but we do so only after writing the rows stored in the buffer out to the table using table_multi_insert(), which I think would be consistent even with non-batching mode, because in that mode we update it after writing each row out to the table using table_tuple_insert(). Andrey Lepikhov, heavily revised by me, with review from Ian Barwick, Andrey Lepikhov, and Zhihong Yu. Discussion: https://postgr.es/m/bc489202-9855-7550-d64c-ad2d83c24867%40postgrespro.ru --- contrib/postgres_fdw/postgres_fdw.c | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'contrib/postgres_fdw/postgres_fdw.c') diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 8d013f5b1af..d98709e5e88 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -2057,6 +2057,15 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) resultRelInfo->ri_TrigDesc->trig_insert_after_row))) return 1; + /* + * If the foreign table has no columns, disable batching as the INSERT + * syntax doesn't allow batching multiple empty rows into a zero-column + * table in a single statement. This is needed for COPY FROM, in which + * case fmstate must be non-NULL. + */ + if (fmstate && list_length(fmstate->target_attrs) == 0) + return 1; + /* * Otherwise use the batch size specified for server/table. The number of * parameters in a batch is limited to 65535 (uint16), so make sure we -- cgit v1.2.3