Re: foreign table batch inserts

On 30 May 2016 at 16:17, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

>
> That's a good point, but the basic idea is to send the local query
> almost-as-is to the remote server if possible. For example, if the local
> query is "INSERT INTO foreign_table(a,b,c) VALUES (1, 2, 3), (4, 5, 6)",
> send the remote query "INSERT INTO remote_table(a,b,c) VALUES (1, 2, 3),
> (4, 5, 6)" to the remote server where remote_table is the table name for
> the foreign table on the remote server. So, wouldn't the query string
> length be a problem in many cases? Maybe I'm missing something, though.
> <http://www.postgresql.org/mailpref/pgsql-hackers>
>

FDWs don't operate at that level. They don't see the original query string.
They're plan nodes that operate with a row-by-row push/pull model. The
foreign table node in question has no idea you're doing a multivalued
insert and doesn't care if it's INSERT INTO ... SELECT, INSERT INTO ...
VALUES, or COPY.

That's why I think using batching is the way to go here. Each operation
remains isolated, but you don't force a round trip for each one, you just
queue them up on the wire and you flush only at end-of-statement. A failure
will cause the statement to ERROR and abort the tx, so the effect is the
same, though the failure might be a bit later than if you forced a flush
each time.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services