Re: postgres_fdw join pushdown (was Re: Custom/Foreign-Join-APIs)

On 2016/02/10 4:16, Robert Haas wrote:
> On Tue, Feb 9, 2016 at 8:39 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> Thanks Jeevan for your review and comments. PFA the patch which fixes those.

> Committed with a couple more small adjustments.

Thanks for working on this, Robert, Ashutosh, and everyone involved!

I happened to notice that this code in foreign_join_ok():

     switch (jointype)
     {
         case JOIN_INNER:
             fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                fpinfo_i->remote_conds);
             fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                fpinfo_o->remote_conds);
             break;

         case JOIN_LEFT:
             fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                               fpinfo_i->remote_conds);
             fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                fpinfo_o->remote_conds);
             break;

         case JOIN_RIGHT:
             fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                               fpinfo_o->remote_conds);
             fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                fpinfo_i->remote_conds);
             break;

         case JOIN_FULL:
             fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                               fpinfo_i->remote_conds);
             fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                               fpinfo_o->remote_conds);
             break;

         default:
             /* Should not happen, we have just check this above */
             elog(ERROR, "unsupported join type %d", jointype);
     }

would break the list fpinfo_i->remote_conds in the case of INNER JOIN or
FULL JOIN.  You can see the list breakage from e.g., the following
queries on an Assert-enabled build:

postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create server myserver foreign data wrapper postgres_fdw
options (dbname 'mydatabase');
CREATE SERVER
postgres=# create user mapping for current_user server myserver;
CREATE USER MAPPING
postgres=# create foreign table foo (a int) server myserver options
(table_name 'foo');
CREATE FOREIGN TABLE
postgres=# create foreign table bar (a int) server myserver options
(table_name 'bar');
CREATE FOREIGN TABLE
postgres=# create foreign table baz (a int) server myserver options
(table_name 'baz');
CREATE FOREIGN TABLE
postgres=# select * from foo, bar, baz where foo.a = bar.a and bar.a =
baz.a and foo.a < 10 and bar.a < 10 and baz.a < 10;

Attached is a patch to avoid the breakage.

Best regards,
Etsuro Fujita