Re: Foreign join pushdown vs EvalPlanQual

Does it make sense to put the result tuple of remote join on evety
estate->es_epqTupleSet[] slot represented by this ForeignScan if
scanrelid==0?

It allows to recheck qualifier for each LockRow that intends to lock
base foreign table underlying the remote join.
ForeignScan->fdw_relids tells us which rtindexes are represented
by this ForeignScan, so infrastructure side may be able to handle.

Thanks,

2015-06-24 11:40 GMT+09:00 Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>:
> Hi,
>
> While reviewing the foreign join pushdown core patch, I noticed that the
> patch doesn't perform an EvalPlanQual recheck properly. The example
> that crashes the server will be shown below (it uses the postgres_fdw
> patch [1]). I think the reason for that is because the ForeignScan node
> performing the foreign join remotely has scanrelid = 0 while
> ExecScanFetch assumes that its scan node has scanrelid > 0.
>
> I think this is a bug. I've not figured out how to fix this yet, but I
> thought we would also need another plan that evaluates the join locally
> for the test tuples for EvalPlanQual. Though I'm missing something though.
>
> Create an environment:
>
> postgres=# create table tab (a int, b int);
> CREATE TABLE
> 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=# insert into tab values (1, 1);
> INSERT 0 1
> postgres=# insert into foo values (1);
> INSERT 0 1
> postgres=# insert into bar values (1);
> INSERT 0 1
> postgres=# analyze tab;
> ANALYZE
> postgres=# analyze foo;
> ANALYZE
> postgres=# analyze bar;
> ANALYZE
>
> Run the example:
>
> [Terminal 1]
> postgres=# begin;
> BEGIN
> postgres=# update tab set b = b + 1 where a = 1;
> UPDATE 1
>
> [Terminal 2]
> postgres=# explain verbose select tab.* from tab, foo, bar where tab.a =
> foo.a and foo.a = bar.a for update;
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------------------------
> LockRows (cost=100.00..101.18 rows=4 width=70)
> Output: tab.a, tab.b, tab.ctid, foo.*, bar.*
> -> Nested Loop (cost=100.00..101.14 rows=4 width=70)
> Output: tab.a, tab.b, tab.ctid, foo.*, bar.*
> Join Filter: (foo.a = tab.a)
> -> Seq Scan on public.tab (cost=0.00..1.01 rows=1 width=14)
> Output: tab.a, tab.b, tab.ctid
> -> Foreign Scan (cost=100.00..100.08 rows=4 width=64)
> Output: foo.*, foo.a, bar.*, bar.a
> Relations: (public.foo) INNER JOIN (public.bar)
> Remote SQL: SELECT l.a1, l.a2, r.a1, r.a2 FROM (SELECT
> ROW(l.a9), l.a9 FROM (SELECT a a9 FROM public.foo FOR UPDATE) l) l (a1,
> a2) INNER
> JOIN (SELECT ROW(r.a9), r.a9 FROM (SELECT a a9 FROM public.bar FOR
> UPDATE) r) r (a1, a2) ON ((l.a2 = r.a2))
> (11 rows)
>
> postgres=# select tab.* from tab, foo, bar where tab.a = foo.a and foo.a
> = bar.a for update;
>
> [Terminal 1]
> postgres=# commit;
> COMMIT
>
> [Terminal 2]
> (After the commit in Terminal 1, Terminal 2 will show the following.)
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !>
>
> Best regards,
> Etsuro Fujita
>
> [1]
> http://www.postgresql.org/message-id/CAEZqfEe9KGy=1_waGh2rgZPg0o4pqgD+iauYaj8wTze+CYJUHg@mail.gmail.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>