Re: Foreign join pushdown vs EvalPlanQual - Mailing list pgsql-hackers

Hi,

At Fri, 9 Oct 2015 18:18:52 +0530, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote in
<CAM2+6=XsXhMw_owFiKJP9syUx9eFc0x5U9jGOtO9v34G5epd8g@mail.gmail.com>
> I have noticed that, this thread started saying we are getting a crash
> with the given steps with foreign_join_v16.patch, I am correct?

Your're correct. The immediate cause of the crash is an assertion
failure that EvalPlanQualNext doesn't find a tuple to examine for
a "foreign join" changed into a ForeignScan as the result of
foreign join pushdown.

> Then there are various patches which trying to fix this,
> fdw-eval-plan-qual-*.patch
> 
> I have tried applying foreign_join_v16.patch, which was good. And tried
> reproducing the crash. But instead of crash I am getting following error.
> 
> ERROR:  could not serialize access due to concurrent update
> CONTEXT:  Remote SQL command: SELECT a FROM public.foo FOR UPDATE
> Remote SQL command: SELECT a FROM public.tab FOR UPDATE

It is because you took wrong steps.

FDW runs a transaction in the isolation level above REPEATABLE
READ. You updated a value locally while the fdw is locking the
same tuple in REPEATABLE READ transaction. You should map
different table as the foreign tables from the locally-modified
table.

- create table tab (a int, b int);
- create foreign table foo (a int) server myserver options(table_name 'tab');
- create foreign table bar (a int) server myserver options(table_name 'tab');
+ create table tab (a int, b int);
+ create table lfb (a int, b int);
+ create foreign table foo (a int) server myserver options(table_name 'lfb);
+ create foreign table bar (a int) server myserver options(table_name 'lfb');

And you'll get the following assertion failure.

| TRAP: FailedAssertion("!(scanrelid > 0)", File: "execScan.c", Line: 52)
| LOG:  unexpected EOF on client connection with an open transaction
| LOG:  server process (PID 16738) was terminated by signal 6: Aborted
| DETAIL:  Failed process was running: explain (verbose, analyze) select t1.* from t1, ft2, ft2_2 where t1.a = ft2.a
andft2.a = ft2_2.a for update;
 
| LOG:  terminating any other active server proces

> Then I have applied fdw-eval-plan-qual-3.0.patch on top of it. It was not
> getting applied cleanly (may be due to some other changes meanwhile).
> I fixed the conflicts and the warnings to make it compile.

The combination won't work because the patch requires
postgres_fdw to put alternative path as subpath to
create_foreignscan_path. AFAICS no corresponding forign-join
patch has shown in this thread. This thread continues to discuss
the desirable join pushdown API for FDW.

> When I run same sql sequence, I am getting crash in terminal 2 at EXPLAIN
> it self.
> 
> 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.
> !>
> 
> Following sql statement I am using:
> 
> create table tab (a int, b int);
> create foreign table foo (a int) server myserver options(table_name 'tab');
> create foreign table bar (a int) server myserver options(table_name 'tab');
> 
> insert into tab values (1, 1);
> insert into foo values (1);
> insert into bar values (1);
> 
> analyze tab;
> analyze foo;
> analyze bar;
> 
> 
> Run the example:
> 
> [Terminal 1]
> begin;
> update tab set b = b + 1 where a = 1;
> 
> [Terminal 2]
> explain verbose select tab.* from tab, foo, bar where tab.a =
> foo.a and foo.a = bar.a for update;
> 
> 
> Am I missing something here?
> Do I need to apply any other patch from other mail-threads?
> 
> Do you have sample test-case explaining the issue and fix?
> 
> With these simple questions, I might have taking the thread slightly off
> from the design considerations, please excuse me for that.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center