postgres_fdw: Oddity in pushing down inherited UPDATE/DELETE joins to remote servers

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: postgres_fdw: Oddity in pushing down inherited UPDATE/DELETE joins to remote servers
Date: 2018-05-10 12:41:38
Message-ID: 5AF43E02.30000@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While doing a bit more review of the partitionwise-join-fix patch, I
noticed $SUBJECT. Here is an example that causes an assertion failure
"TRAP: FailedAssertion("!(foreignrel)", File: "postgres_fdw.c", Line:
2213)":

postgres=# create table t1 (a int, b text);
CREATE TABLE
postgres=# create table t2 (a int, b text);
CREATE TABLE
postgres=# create foreign table ft1 (a int, b text) server loopback
options (table_name 't1');
CREATE FOREIGN TABLE
postgres=# create foreign table ft2 (a int, b text) server loopback
options (table_name 't2');
CREATE FOREIGN TABLE
postgres=# insert into ft1 values (1, 'foo');
INSERT 0 1
postgres=# insert into ft1 values (2, 'bar');
INSERT 0 1
postgres=# insert into ft2 values (1, 'test1');
INSERT 0 1
postgres=# insert into ft2 values (2, 'test2');
INSERT 0 1
postgres=# analyze ft1;
ANALYZE
postgres=# analyze ft2;
ANALYZE
postgres=# create table parent (a int, b text);
CREATE TABLE
postgres=# alter foreign table ft1 inherit parent;
ALTER FOREIGN TABLE
postgres=# update parent set b = ft2.b from ft2 where parent.a = ft2.a;
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.
!>

I think the reason for that is: in that case we try to find the target
foreign-join RelOptInfo using find_join_rel in postgresPlanDirectModify,
but can't find it, because the given root is the *parent* root and
doesn't have join RelOptInfos with it. To fix this, I'd like to propose
to modify make_modifytable so that in case of an inherited
UPDATE/DELETE, it passes to PlanDirectModify the per-child modified
subroot, not the parent root, for the FDW to get the foreign-join
RelOptInfo from the given root in PlanDirectModify. I think that that
would be more consistent with the non-inherited UPDATE/DELETE case in
that the FDW can look at any join RelOptInfos in the given root in
PlanDirectModify, which I think would be a good thing because some FDWs
might need to do that for some reason. For the same reason, I'd also
like to propose to pass to PlanForeignModify the per-child modified
subroot, not the parent root, as for PlanDirectModify. Attached is a
proposed patch for that. I'll add this to the open items list for PG11.

Best regards,
Etsuro Fujita

Attachment Content-Type Size
make_modifytable.patch text/x-diff 10.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2018-05-10 12:53:22 Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled.
Previous Message Pavel Raiskup 2018-05-10 10:41:40 Re: Shared PostgreSQL libraries and symbol versioning