From: | Etsuro Fujita <efujita(at)postgresql(dot)org> |
---|---|
To: | pgsql-committers(at)lists(dot)postgresql(dot)org |
Subject: | pgsql: Allow batch insertion during COPY into a foreign table. |
Date: | 2022-10-13 09:51:51 |
Message-ID: | E1oiusZ-002Au4-A5@gemulon.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/97da48246d34807196b404626f019c767b7af0df
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 105 +++++++++
contrib/postgres_fdw/postgres_fdw.c | 9 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 102 ++++++++
doc/src/sgml/fdwhandler.sgml | 4 +-
doc/src/sgml/postgres-fdw.sgml | 4 +
src/backend/commands/copyfrom.c | 313 ++++++++++++++++++-------
src/include/commands/copyfrom_internal.h | 12 +-
7 files changed, 460 insertions(+), 89 deletions(-)
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2022-10-13 10:25:38 | pgsql: Put tests of md5() function into separate test file |
Previous Message | Michael Paquier | 2022-10-13 07:03:15 | pgsql: Add missing isolation test for test_decoding in meson build |