Re: foreign table batch inserts

On 2016/05/27 8:49, Michael Paquier wrote:
> On Thu, May 26, 2016 at 4:25 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

>> 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.

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.

Best regards,
Etsuro Fujita