pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Etsuro Fujita <efujita@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Allow batch insertion during COPY into a foreign table.
Date: Thu, 13 Oct 2022 09:51:51 +0000
Message-ID: <E1oiusZ-002Au4-A5@gemulon.postgresql.org> (raw)

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(-)



Message-ID: <E1oiusZ-002Au4-A5@gemulon.postgresql.org>
Permalink:  ../E1oiusZ-002Au4-A5@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1oiusZ-002Au4-A5@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: efujita@postgresql.org
  Subject: Re: pgsql: Allow batch insertion during COPY into a foreign table.
  In-Reply-To: <E1oiusZ-002Au4-A5@gemulon.postgresql.org>
  References: <E1oiusZ-002Au4-A5@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox