Re: Optimization for updating foreign tables in Postgres FDW

On 2016/04/15 14:31, Michael Paquier wrote:
> On Thu, Apr 14, 2016 at 10:44 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> On 2016/04/13 21:50, Michael Paquier wrote:
>>> On Wed, Apr 13, 2016 at 9:46 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
>>> wrote:
>>>> On Tue, Apr 12, 2016 at 10:24 PM, Etsuro Fujita
>>>> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>>>>
>>>>>> How about we encapsulate the while (PQisBusy(...)) loop into a new
>>>>>> function pgfdw_get_result(), which can be called after first calling
>>>>>> PQsendQueryParams()? So then this code will say dmstate->result =
>>>>>> pgfdw_get_result(dmstate->conn). And we can do something similar for
>>>>>> the other call to PQexecParams() in create_cursor(). Then let's also
>>>>>> add something like pgfdw_exec_query() which calls PQsendQuery() and
>>>>>> then pgfdw_get_result, and use that to replace all of the existing
>>>>>> calls to PQexec().
>>>>>>
>>>>>> Then all the SQL postgres_fdw executes would be interruptible, not
>>>>>> just the new stuff.

>> I would be happy if you work on that.

> OK, so I have finished with the attached.

Thank you for working on that!

> One thing that I noticed in
> the previous patch version is that it completely ignored cases where
> multiple PGresult could be returned by server, say when multiple
> queries are sent in the same string: PQexec gets always the last one,
> so I think that we had better do the same here.

Seems reasonable.

> so I think that we had better do the same here. I have switched all
> the PQexec calls to a custom routine that combines
> PQsendQuery/PQgetResult, and PQexecParams is switched to
> PQsendQueryParams/PQgetResult. This structure allows all queries run
> though postgres_fdw.c to be interruptible.

How about doing something similar for PQprepare/PQexecPrepared in
postgresExecForeignInsert, postgresExecForeignUpdate, and
postgresExecForeignDelete? Also, how about doing that for PQexec in
connection.c?

Best regards,
Etsuro Fujita