Re: foreign table batch inserts

On Thu, May 26, 2016 at 4:25 AM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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?

Query strings have a limited length, and this assumption is true for
many code paths in the backend code, so doing that with a long string
would introduce more pain in the logic than anything else, as this
would become more data type sensitive.
--
Michael