Re: Push down more UPDATEs/DELETEs in postgres_fdw

On 2016/09/08 19:55, Etsuro Fujita wrote:
> On 2016/09/07 13:21, Ashutosh Bapat wrote:
>> * with the patch:
>> postgres=# explain verbose delete from ft1 using ft2 where ft1.a =
>> ft2.a;
>> QUERY PLAN
>>
>> -----------------------------------------------------------------------------------------------------------------------------
>>
>> Delete on public.ft1 (cost=100.00..102.04 rows=1 width=38)
>> -> Foreign Delete (cost=100.00..102.04 rows=1 width=38)
>> Remote SQL: DELETE FROM public.t1 r1 USING (SELECT ROW(a,
>> b), a FROM public.t2) ss1(c1, c2) WHERE ((r1.a = ss1.c2))
>> (3 rows)
>
>> The underlying scan on t2 requires ROW(a,b) for locking the row for
>> update/share. But clearly it's not required if the full query is being
>> pushed down.

>> Is there a way we can detect that ROW(a,b) is useless
>> column (not used anywhere in the other parts of the query like
>> RETURNING, DELETE clause etc.) and eliminate it?

> I don't have a clear solution for that yet, but I'll try to remove that
> in the next version.

>> Similarly for a, it's
>> part of the targetlist of the underlying scan so that the WHERE clause
>> can be applied on it. But it's not needed if we are pushing down the
>> query. If we eliminate the targetlist of the query, we could construct a
>> remote query without having subquery in it, making it more readable.

> Will try to do so also.

I addressed this by improving the deparse logic so that a remote query
for performing an UPDATE/DELETE on a join directly on the remote can be
created as proposed if possible. Attached is an updated version of the
patch, which is created on top of the patch set [1]. The patch is still
WIP (ie, needs more comments and regression tests, at least), but any
comments would be gratefully appreciated.

Best regards,
Etsuro Fujita

[1]
https://www.postgresql.org/message-id/11eafd10-d3f8-ac8a-b642-b0e65037c76b%40lab.ntt.co.jp