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.etsuro@lab.ntt.co.jp> wrote:
>> On 2016/04/13 21:50, Michael Paquier wrote:
>>> On Wed, Apr 13, 2016 at 9:46 PM, Robert Haas <robertmhaas@gmail.com>
>>> wrote:
>>>> On Tue, Apr 12, 2016 at 10:24 PM, Etsuro Fujita
>>>> <fujita.etsuro@lab.ntt.co.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