On Fri, Mar 18, 2016 at 7:22 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> Hi,
>
> I am testing postgres_fdw join pushdown feature for PostgreSQL 9.6 DB, and I
> observed below issue.
>
> Observation: If do a left outer join on foreign tables more than two times.
> It is causing the server crash.
>
> Added below statement in contrib/postgres_fdw/postgres_fdw.sql and ran make
> check, did a server crash
>
> -- left outer join three tables
> EXPLAIN (COSTS false, VERBOSE)
> SELECT t1.c1,t2.c1,t3.c1 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1)
> LEFT JOIN ft2 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> connection to server was lost
>
> Facing the same crash while doing left outer join, right outer join or
> combination of left-right outer joins for three tables and one local and two
> foreign tables.
In get_useful_ecs_for_relation, it seems to me that this assertion
should be removed and replaces by an actual check because even if
right_ec and left_ec are initialized, we cannot be sure that ec_relids
contains the relations specified:
/*
* restrictinfo->mergeopfamilies != NIL is sufficient to guarantee
* that left_ec and right_ec will be initialized, per comments in
* distribute_qual_to_rels, and rel->joininfo should only contain ECs
* where this relation appears on one side or the other.
*/
if (bms_is_subset(relids, restrictinfo->right_ec->ec_relids))
useful_eclass_list = list_append_unique_ptr(useful_eclass_list,
restrictinfo->right_ec);
else
{
Assert(bms_is_subset(relids, restrictinfo->left_ec->ec_relids));
useful_eclass_list = list_append_unique_ptr(useful_eclass_list,
restrictinfo->left_ec);
}
See for example the attached (with more tests including combinations
of joins, and three-table joins). I have added an open item for 9.6 on
the wiki.
--
Michael