Re: postgres_fdw: Use COPY to speed up batch inserts

Hi,

Attached is a new version of the patch taking a different approach from
the earlier attempts.

Off-list I discussed with Masahiko the idea of adding a dedicated COPY
API to the FDW handler, instead of trying to bolt COPY onto the existing
insert callbacks, and the attached patch implements that. The core
addition is a single new optional FdwRoutine callback:

void
ExecForeignBatchCopy(EState *estate,
ResultRelInfo *rinfo,
TupleTableSlot **slots,
int numSlots);

When a foreign table (or a foreign-table partition) is the target of
COPY FROM and batching is enabled, CopyFrom() hands each buffered batch
of tuples to this callback instead of ExecForeignInsert /
ExecForeignBatchInsert. postgres_fdw implements it by running a
COPY ... FROM STDIN on the remote connection and streaming the rows in
text format.

I first tried the more obvious design of splitting this into
BeginForeignCopy / ExecForeignCopy / EndForeignCopy, keeping a single
COPY ... FROM STDIN open across the entire COPY command and closing it
only in EndForeignCopy. That doesn't work in general because of
connection sharing. postgres_fdw caches one connection per user
mapping, so:

- when COPY routes tuples into several foreign partitions that map to
the same remote server (the sharding case), or

- when local code runs another query on the same connection in the
middle of the COPY (e.g. a trigger or a volatile default expression
that reads a foreign table on the same server),

we would need a second command in flight on a connection that is stuck
in COPY_IN mode, which fails. Making each call self-contained avoids
this entirely, the connection is always left idle between calls, so
partitions can share it and any interleaved query just works.

The trade-off is that COPY is only used when batching is enabled
(batch_size > 1). With batching disabled we would be starting and
finishing a COPY protocol for every single row, which is slower than a
plain INSERT, so in that case COPY FROM keeps inserting rows one at a
time, exactly as today. The new callback is also independent of
ExecForeignBatchInsert, a FDW may implement either or both. COPY is
not used when the target has AFTER ROW triggers, because COPY has no
RETURNING to feed them, such cases fall back to the batch-insert path.

This is a PoC patch to experiment the idea of having a dedicated API,
but I think that the main advantage of this is that we can involve the
API to be more flexible and e.g use some user custom COPY options when
writing the remote SQL for the remote server (although this patch still
doesn't implement this) which would be hard using the ExecForeignInsert
API. Also, I think that we can have a BeginForeignCopy and
EndForeignCopy to initialize states used on ExecForeignBatchCopy (e.g
the sql COPY command).

The patch is split into three parts:

0001 - Extract CopyEscapeText() so it can be reused outside COPY TO.
0002 - Add the ExecForeignBatchCopy callback to the FDW API (copyfrom.c,
fdwapi.h and the FDW documentation).
0003 - Implement the callback in postgres_fdw.

Thoughts on the API and the self-contained approach are very welcome.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com