Re: Join push-down support for foreign tables

Here is v4 patch of Join push-down support for foreign tables.  This
patch requires Custom/Foreign join patch v7 posted by Kaigai-san.

In this version I added check about query type which gives up pushing
down joins when the join is a part of an underlying query of
UPDATE/DELETE.



As of now postgres_fdw builds a proper remote query but it can't bring
ctid value up to postgresExecForeignUpdate()...

I'm still working on supporting such query, but I'm not sure that
supporting UPDATE/DELETE is required in the first version.  I attached
a patch foreign_join_update.patch to sure WIP for supporting
update/delete as top of foreign joins.

How to reproduce the error, please execute query below after running
attached init_fdw.sql for building test environment.  Note that the
script drops "user1", and creates database "fdw" and "pgbench".

fdw=# explain (verbose) update pgbench_branches b set filler = 'foo'
from pgbench_tellers t where t.bid = b.bid and t.tid < 10;

                                                                 QUERY
PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
 Update on public.pgbench_branches b  (cost=100.00..100.67 rows=67 width=390)
   Remote SQL: UPDATE public.pgbench_branches SET filler = $2 WHERE ctid = $1
   ->  Foreign Scan  (cost=100.00..100.67 rows=67 width=390)
         Output: b.bid, b.bbalance, 'foo

'::character(88), b.ctid, *
         Remote SQL: SELECT r.a_0, r.a_1, r.a_2, l FROM (SELECT tid,
bid, tbalance, filler FROM public.pgbench_tellers WHERE ((tid < 10)))
l (a_0, a_1) INNER JOIN (SELECT b
id, bbalance, NULL, ctid FROM public.pgbench_branches FOR UPDATE) r
(a_0, a_1, a_2, a_3)  ON ((r.a_0 = l.a_1))
(5 rows)
fdw=# explain (analyze, verbose) update pgbench_branches b set filler
= 'foo' from pgbench_tellers t where t.bid = b.bid and t.tid < 10;
ERROR:  ctid is NULL


2015-03-03 21:34 GMT+09:00 Shigeru Hanada <shigeru.hanada@gmail.com>:
> I rebased "join push-down" patch onto Kaigai-san's Custom/Foreign Join
> v6 patch.  I posted some comments to v6 patch in this post:
>
> http://www.postgresql.org/message-id/CAEZqfEcNvjqq-P=jxnW1Pb4T9wvpcPoRCN7G6cc46JGuB7dY8w@mail.gmail.com
>
> Before applying my v3 patch, please apply Kaigai-san's v6 patch and my
> mod_cjv6.patch.
> Sorry for complex patch combination.  Those patches will be arranged
> soon by Kaigai-san and me.
>
> I fixed the issues pointed out by Thom and Kohei, but still the patch
> has an issue about joins underlying UPDATE or DELETE.  Now I'm working
> on fixing this issue.  Besides this issue, existing regression test
> passed.
>
> 2015-03-03 19:48 GMT+09:00 Kouhei Kaigai <kaigai@ak.jp.nec.com>:
>>> > * Bug reported by Thom Brown
>>> > -----------------------------
>>> > # EXPLAIN VERBOSE SELECT NULL FROM (SELECT people.id FROM people INNER JOIN
>>> countries ON people.country_id = countries.id LIMIT 3) x;
>>> > ERROR:  could not open relation with OID 0
>>> >
>>> > Sorry, it was a problem caused by my portion. The patched setrefs.c
>>> > checks fdw_/custom_ps_tlist to determine whether Foreign/CustomScan
>>> > node is associated with a certain base relation. If *_ps_tlist is
>>> > valid, it also expects scanrelid == 0.
>>> > However, things we should check is incorrect. We may have a case
>>> > with empty *_ps_tlist if remote join expects no columns.
>>> > So, I adjusted the condition to check scanrelid instead.
>>>
>>> Is this issue fixed by v5 custom/foreign join patch?
>>>
>> Yes, please rebase it.
>>
>> --
>> NEC OSS Promotion Center / PG-Strom Project
>> KaiGai Kohei <kaigai@ak.jp.nec.com>
>>
>
>
>
> --
> Shigeru HANADA



--
Shigeru HANADA