Re: foreign table batch inserts

On 2016/05/18 7:08, Michael Paquier wrote:
> On Wed, May 18, 2016 at 6:00 AM, Manuel Kniep <m(dot)kniep(at)web(dot)de> wrote:
>> I realized that inserts into foreign tables are only done row by row.
>> Consider copying data from one local table to a foreign table with
>>
>> INSERT INTO foreign_table(a,b,c) SELECT a,b,c FROM local_table;
>>
>> When the foreign server is for example in another datacenter with long latency,
>> this as an enormous performance trade off.

> I am adding Fujita-san in the loop here, he is
> quite involved with postgres_fdw these days so perhaps he has some
> input to offer.

Honestly, I didn't have any idea for executing such an insert
efficiently, but I was thinking to execute an insert into a foreign
table efficiently, by sending the whole insert to the remote server, if
possible. For example, if the insert is of the form:

INSERT INTO foreign_table(a,b,c) VALUES (1, 2, 3), (4, 5, 6) or
INSERT INTO foreign_table(a,b,c) SELECT a,b,c FROM foreign_table2

where foreign_table and foreign_table2 belong to the same foreign
server, then we could send the whole insert to the remote server.

Wouldn't that make sense?

Best regards,
Etsuro Fujita