diff options
Diffstat (limited to 'contrib')
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 17 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 423 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 129 |
3 files changed, 289 insertions, 280 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 6faf499f9a6..5aa3455e30b 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -1275,7 +1275,7 @@ deparseLockingClause(deparse_expr_cxt *context) * that DECLARE CURSOR ... FOR UPDATE is supported, which it isn't * before 8.3. */ - if (relid == root->parse->resultRelation && + if (bms_is_member(relid, root->all_result_relids) && (root->parse->commandType == CMD_UPDATE || root->parse->commandType == CMD_DELETE)) { @@ -1867,6 +1867,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, * 'foreignrel' is the RelOptInfo for the target relation or the join relation * containing all base relations in the query * 'targetlist' is the tlist of the underlying foreign-scan plan node + * (note that this only contains new-value expressions and junk attrs) * 'targetAttrs' is the target columns of the UPDATE * 'remote_conds' is the qual clauses that must be evaluated remotely * '*params_list' is an output list of exprs that will become remote Params @@ -1888,8 +1889,9 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root, deparse_expr_cxt context; int nestlevel; bool first; - ListCell *lc; RangeTblEntry *rte = planner_rt_fetch(rtindex, root); + ListCell *lc, + *lc2; /* Set up context struct for recursion */ context.root = root; @@ -1908,14 +1910,13 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root, nestlevel = set_transmission_modes(); first = true; - foreach(lc, targetAttrs) + forboth(lc, targetlist, lc2, targetAttrs) { - int attnum = lfirst_int(lc); - TargetEntry *tle = get_tle_by_resno(targetlist, attnum); + TargetEntry *tle = lfirst_node(TargetEntry, lc); + int attnum = lfirst_int(lc2); - if (!tle) - elog(ERROR, "attribute number %d not found in UPDATE targetlist", - attnum); + /* update's new-value expressions shouldn't be resjunk */ + Assert(!tle->resjunk); if (!first) appendStringInfoString(buf, ", "); diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f61e59cd200..eff7b04f11a 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -5503,13 +5503,13 @@ UPDATE ft2 AS target SET (c2, c7) = ( FROM ft2 AS src WHERE target.c1 = src.c1 ) WHERE c1 > 1100; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Update on public.ft2 target Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1 -> Foreign Scan on public.ft2 target - Output: target.c1, $1, NULL::integer, target.c3, target.c4, target.c5, target.c6, $2, target.c8, (SubPlan 1 (returns $1,$2)), target.ctid - Remote SQL: SELECT "C 1", c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE + Output: $1, $2, (SubPlan 1 (returns $1,$2)), target.ctid, target.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE SubPlan 1 (returns $1,$2) -> Foreign Scan on public.ft2 src Output: (src.c2 * 10), src.c7 @@ -5539,9 +5539,9 @@ UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 -> Foreign Scan on public.ft2 - Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid + Output: 'bar'::text, ctid, ft2.* Filter: (postgres_fdw_abs(ft2.c1) > 2000) - Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE (7 rows) UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; @@ -5570,11 +5570,11 @@ UPDATE ft2 SET c3 = 'baz' Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 -> Nested Loop - Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 + Output: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 Join Filter: (ft2.c2 === ft4.c1) -> Foreign Scan on public.ft2 - Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid - Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE + Output: ft2.ctid, ft2.*, ft2.c2 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE -> Foreign Scan Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3 Relations: (public.ft4) INNER JOIN (public.ft5) @@ -6266,7 +6266,7 @@ UPDATE rw_view SET b = b + 5; Update on public.foreign_tbl Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + Output: (foreign_tbl.b + 5), foreign_tbl.ctid, foreign_tbl.* Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE (5 rows) @@ -6280,7 +6280,7 @@ UPDATE rw_view SET b = b + 15; Update on public.foreign_tbl Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + Output: (foreign_tbl.b + 15), foreign_tbl.ctid, foreign_tbl.* Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE (5 rows) @@ -6348,13 +6348,13 @@ SELECT * FROM foreign_tbl; EXPLAIN (VERBOSE, COSTS OFF) UPDATE rw_view SET b = b + 5; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.parent_tbl Foreign Update on public.foreign_tbl parent_tbl_1 Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: parent_tbl_1.a, (parent_tbl_1.b + 5), parent_tbl_1.ctid + Output: (parent_tbl_1.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE (6 rows) @@ -6363,13 +6363,13 @@ ERROR: new row violates check option for view "rw_view" DETAIL: Failing row contains (20, 20). EXPLAIN (VERBOSE, COSTS OFF) UPDATE rw_view SET b = b + 15; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Update on public.parent_tbl Foreign Update on public.foreign_tbl parent_tbl_1 Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: parent_tbl_1.a, (parent_tbl_1.b + 15), parent_tbl_1.ctid + Output: (parent_tbl_1.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE (6 rows) @@ -6686,7 +6686,7 @@ UPDATE rem1 set f1 = 10; -- all columns should be transmitted Update on public.rem1 Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 -> Foreign Scan on public.rem1 - Output: 10, f2, ctid, rem1.* + Output: 10, ctid, rem1.* Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE (5 rows) @@ -6919,7 +6919,7 @@ UPDATE rem1 set f2 = ''; -- can't be pushed down Update on public.rem1 Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 -> Foreign Scan on public.rem1 - Output: f1, ''::text, ctid, rem1.* + Output: ''::text, ctid, rem1.* Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE (5 rows) @@ -6943,7 +6943,7 @@ UPDATE rem1 set f2 = ''; -- can't be pushed down Update on public.rem1 Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2 -> Foreign Scan on public.rem1 - Output: f1, ''::text, ctid, rem1.* + Output: ''::text, ctid, rem1.* Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE (5 rows) @@ -7253,36 +7253,22 @@ select * from bar where f1 in (select f1 from foo) for share; -- Check UPDATE with inherited target and an inherited source table explain (verbose, costs off) update bar set f2 = f2 + 100 where f1 in (select f1 from foo); - QUERY PLAN -------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------- Update on public.bar - Update on public.bar - Foreign Update on public.bar2 bar_1 + Update on public.bar bar_1 + Foreign Update on public.bar2 bar_2 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 -> Hash Join - Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid + Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, (NULL::record), foo.*, foo.tableoid Inner Unique: true Hash Cond: (bar.f1 = foo.f1) - -> Seq Scan on public.bar - Output: bar.f1, bar.f2, bar.ctid - -> Hash - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> HashAggregate - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - Group Key: foo.f1 - -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 - -> Hash Join - Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, foo.ctid, foo.*, foo.tableoid - Inner Unique: true - Hash Cond: (bar_1.f1 = foo.f1) - -> Foreign Scan on public.bar2 bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash Output: foo.ctid, foo.f1, foo.*, foo.tableoid -> HashAggregate @@ -7294,7 +7280,7 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -> Foreign Scan on public.foo2 foo_2 Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(39 rows) +(25 rows) update bar set f2 = f2 + 100 where f1 in (select f1 from foo); select tableoid::regclass, * from bar order by 1,2; @@ -7314,39 +7300,24 @@ update bar set f2 = f2 + 100 from ( select f1 from foo union all select f1+3 from foo ) ss where bar.f1 = ss.f1; - QUERY PLAN --------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.bar - Update on public.bar - Foreign Update on public.bar2 bar_1 + Update on public.bar bar_1 + Foreign Update on public.bar2 bar_2 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 - -> Hash Join - Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1)) - Hash Cond: (foo.f1 = bar.f1) - -> Append - -> Seq Scan on public.foo - Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 foo_1 - Output: ROW(foo_1.f1), foo_1.f1 - Remote SQL: SELECT f1 FROM public.loct1 - -> Seq Scan on public.foo foo_2 - Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) - -> Foreign Scan on public.foo2 foo_3 - Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) - Remote SQL: SELECT f1 FROM public.loct1 - -> Hash - Output: bar.f1, bar.f2, bar.ctid - -> Seq Scan on public.bar - Output: bar.f1, bar.f2, bar.ctid -> Merge Join - Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, (ROW(foo.f1)) - Merge Cond: (bar_1.f1 = foo.f1) + Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, (NULL::record) + Merge Cond: (bar.f1 = foo.f1) -> Sort - Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid - Sort Key: bar_1.f1 - -> Foreign Scan on public.bar2 bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record) + Sort Key: bar.f1 + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Sort Output: (ROW(foo.f1)), foo.f1 Sort Key: foo.f1 @@ -7361,7 +7332,7 @@ where bar.f1 = ss.f1; -> Foreign Scan on public.foo2 foo_3 Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) Remote SQL: SELECT f1 FROM public.loct1 -(45 rows) +(30 rows) update bar set f2 = f2 + 100 from @@ -7487,18 +7458,19 @@ ERROR: WHERE CURRENT OF is not supported for this table type rollback; explain (verbose, costs off) delete from foo where f1 < 5 returning *; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- Delete on public.foo - Output: foo.f1, foo.f2 - Delete on public.foo - Foreign Delete on public.foo2 foo_1 - -> Index Scan using i_foo_f1 on public.foo - Output: foo.ctid - Index Cond: (foo.f1 < 5) - -> Foreign Delete on public.foo2 foo_1 - Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 -(9 rows) + Output: foo_1.f1, foo_1.f2 + Delete on public.foo foo_1 + Foreign Delete on public.foo2 foo_2 + -> Append + -> Index Scan using i_foo_f1 on public.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Index Cond: (foo_1.f1 < 5) + -> Foreign Delete on public.foo2 foo_2 + Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 +(10 rows) delete from foo where f1 < 5 returning *; f1 | f2 @@ -7512,17 +7484,20 @@ delete from foo where f1 < 5 returning *; explain (verbose, costs off) update bar set f2 = f2 + 100 returning *; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------ Update on public.bar - Output: bar.f1, bar.f2 - Update on public.bar - Foreign Update on public.bar2 bar_1 - -> Seq Scan on public.bar - Output: bar.f1, (bar.f2 + 100), bar.ctid - -> Foreign Update on public.bar2 bar_1 - Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 -(8 rows) + Output: bar_1.f1, bar_1.f2 + Update on public.bar bar_1 + Foreign Update on public.bar2 bar_2 + -> Result + Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record + -> Foreign Update on public.bar2 bar_2 + Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 +(11 rows) update bar set f2 = f2 + 100 returning *; f1 | f2 @@ -7547,15 +7522,18 @@ update bar set f2 = f2 + 100; QUERY PLAN -------------------------------------------------------------------------------------------------------- Update on public.bar - Update on public.bar - Foreign Update on public.bar2 bar_1 + Update on public.bar bar_1 + Foreign Update on public.bar2 bar_2 Remote SQL: UPDATE public.loct2 SET f1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3 - -> Seq Scan on public.bar - Output: bar.f1, (bar.f2 + 100), bar.ctid - -> Foreign Scan on public.bar2 bar_1 - Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, bar_1.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -(9 rows) + -> Result + Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE +(12 rows) update bar set f2 = f2 + 100; NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2 @@ -7572,19 +7550,20 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2 NOTICE: OLD: (7,277,77),NEW: (7,377,77) explain (verbose, costs off) delete from bar where f2 < 400; - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------- Delete on public.bar - Delete on public.bar - Foreign Delete on public.bar2 bar_1 + Delete on public.bar bar_1 + Foreign Delete on public.bar2 bar_2 Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3 - -> Seq Scan on public.bar - Output: bar.ctid - Filter: (bar.f2 < 400) - -> Foreign Scan on public.bar2 bar_1 - Output: bar_1.ctid, bar_1.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE -(10 rows) + -> Append + -> Seq Scan on public.bar bar_1 + Output: bar_1.tableoid, bar_1.ctid, NULL::record + Filter: (bar_1.f2 < 400) + -> Foreign Scan on public.bar2 bar_2 + Output: bar_2.tableoid, bar_2.ctid, bar_2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE +(11 rows) delete from bar where f2 < 400; NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON bar2 @@ -7615,23 +7594,28 @@ analyze remt1; analyze remt2; explain (verbose, costs off) update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- Update on public.parent - Output: parent.a, parent.b, remt2.a, remt2.b - Update on public.parent - Foreign Update on public.remt1 parent_1 + Output: parent_1.a, parent_1.b, remt2.a, remt2.b + Update on public.parent parent_1 + Foreign Update on public.remt1 parent_2 + Remote SQL: UPDATE public.loct1 SET b = $2 WHERE ctid = $1 RETURNING a, b -> Nested Loop - Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.*, remt2.a, remt2.b + Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, (NULL::record) Join Filter: (parent.a = remt2.a) - -> Seq Scan on public.parent - Output: parent.a, parent.b, parent.ctid - -> Foreign Scan on public.remt2 + -> Append + -> Seq Scan on public.parent parent_1 + Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::record + -> Foreign Scan on public.remt1 parent_2 + Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.* + Remote SQL: SELECT a, b, ctid FROM public.loct1 FOR UPDATE + -> Materialize Output: remt2.b, remt2.*, remt2.a - Remote SQL: SELECT a, b FROM public.loct2 - -> Foreign Update - Remote SQL: UPDATE public.loct1 r4 SET b = (r4.b || r2.b) FROM public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b, r2.a, r2.b -(14 rows) + -> Foreign Scan on public.remt2 + Output: remt2.b, remt2.*, remt2.a + Remote SQL: SELECT a, b FROM public.loct2 +(19 rows) update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; a | b | a | b @@ -7642,23 +7626,28 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re explain (verbose, costs off) delete from parent using remt2 where parent.a = remt2.a returning parent; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------- Delete on public.parent - Output: parent.* - Delete on public.parent - Foreign Delete on public.remt1 parent_1 + Output: parent_1.* + Delete on public.parent parent_1 + Foreign Delete on public.remt1 parent_2 + Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 RETURNING a, b -> Nested Loop - Output: parent.ctid, remt2.* + Output: remt2.*, parent.tableoid, parent.ctid Join Filter: (parent.a = remt2.a) - -> Seq Scan on public.parent - Output: parent.ctid, parent.a - -> Foreign Scan on public.remt2 + -> Append + -> Seq Scan on public.parent parent_1 + Output: parent_1.a, parent_1.tableoid, parent_1.ctid + -> Foreign Scan on public.remt1 parent_2 + Output: parent_2.a, parent_2.tableoid, parent_2.ctid + Remote SQL: SELECT a, ctid FROM public.loct1 FOR UPDATE + -> Materialize Output: remt2.*, remt2.a - Remote SQL: SELECT a, b FROM public.loct2 - -> Foreign Delete - Remote SQL: DELETE FROM public.loct1 r4 USING public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b -(14 rows) + -> Foreign Scan on public.remt2 + Output: remt2.*, remt2.a + Remote SQL: SELECT a, b FROM public.loct2 +(19 rows) delete from parent using remt2 where parent.a = remt2.a returning parent; parent @@ -7837,29 +7826,25 @@ DETAIL: Failing row contains (2, foo). CONTEXT: remote SQL command: UPDATE public.loct SET a = 2 WHERE ((b = 'foo'::text)) RETURNING a, b -- But the reverse is allowed update utrtest set a = 1 where b = 'qux' returning *; - a | b ----+----- - 1 | qux -(1 row) - +ERROR: cannot route tuples into foreign table to be updated "remp" select tableoid::regclass, * FROM utrtest; tableoid | a | b ----------+---+----- remp | 1 | foo - remp | 1 | qux + locp | 2 | qux (2 rows) select tableoid::regclass, * FROM remp; tableoid | a | b ----------+---+----- remp | 1 | foo - remp | 1 | qux -(2 rows) +(1 row) select tableoid::regclass, * FROM locp; - tableoid | a | b -----------+---+--- -(0 rows) + tableoid | a | b +----------+---+----- + locp | 2 | qux +(1 row) -- The executor should not let unexercised FDWs shut down update utrtest set a = 1 where b = 'foo'; @@ -7871,38 +7856,35 @@ insert into utrtest values (2, 'qux'); -- Check case where the foreign partition is a subplan target rel explain (verbose, costs off) update utrtest set a = 1 where a = 1 or a = 2 returning *; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Foreign Update on public.remp utrtest_1 Update on public.locp utrtest_2 - -> Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b - -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.b, utrtest_2.ctid - Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) -(9 rows) + -> Append + -> Foreign Update on public.remp utrtest_1 + Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b + -> Seq Scan on public.locp utrtest_2 + Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record + Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) +(10 rows) -- The new values are concatenated with ' triggered !' update utrtest set a = 1 where a = 1 or a = 2 returning *; - a | b ----+----------------- - 1 | qux triggered ! -(1 row) - +ERROR: cannot route tuples into foreign table to be updated "remp" delete from utrtest; insert into utrtest values (2, 'qux'); -- Check case where the foreign partition isn't a subplan target rel explain (verbose, costs off) update utrtest set a = 1 where a = 2 returning *; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Update on public.locp utrtest_1 -> Seq Scan on public.locp utrtest_1 - Output: 1, utrtest_1.b, utrtest_1.ctid + Output: 1, utrtest_1.tableoid, utrtest_1.ctid Filter: (utrtest_1.a = 2) (6 rows) @@ -7923,66 +7905,51 @@ insert into utrtest values (2, 'qux'); -- with a direct modification plan explain (verbose, costs off) update utrtest set a = 1 returning *; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Foreign Update on public.remp utrtest_1 Update on public.locp utrtest_2 - -> Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b - -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.b, utrtest_2.ctid -(8 rows) + -> Append + -> Foreign Update on public.remp utrtest_1 + Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b + -> Seq Scan on public.locp utrtest_2 + Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record +(9 rows) update utrtest set a = 1 returning *; - a | b ----+----- - 1 | foo - 1 | qux -(2 rows) - +ERROR: cannot route tuples into foreign table to be updated "remp" delete from utrtest; insert into utrtest values (1, 'foo'); insert into utrtest values (2, 'qux'); -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.utrtest Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b Update on public.locp utrtest_2 -> Hash Join - Output: 1, utrtest_1.b, utrtest_1.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (utrtest_1.a = "*VALUES*".column1) - -> Foreign Scan on public.remp utrtest_1 - Output: utrtest_1.b, utrtest_1.ctid, utrtest_1.a - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE - -> Hash - Output: "*VALUES*".*, "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".*, "*VALUES*".column1 - -> Hash Join - Output: 1, utrtest_2.b, utrtest_2.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (utrtest_2.a = "*VALUES*".column1) - -> Seq Scan on public.locp utrtest_2 - Output: utrtest_2.b, utrtest_2.ctid, utrtest_2.a + Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.* + Hash Cond: (utrtest.a = "*VALUES*".column1) + -> Append + -> Foreign Scan on public.remp utrtest_1 + Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.* + Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE + -> Seq Scan on public.locp utrtest_2 + Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::record -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" Output: "*VALUES*".*, "*VALUES*".column1 -(24 rows) +(18 rows) update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; - a | b | x ----+-----+--- - 1 | foo | 1 - 1 | qux | 2 -(2 rows) - +ERROR: cannot route tuples into foreign table to be updated "remp" -- Change the definition of utrtest so that the foreign partition get updated -- after the local partition delete from utrtest; @@ -7998,50 +7965,45 @@ insert into utrtest values (3, 'xyzzy'); -- with a direct modification plan explain (verbose, costs off) update utrtest set a = 3 returning *; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b Update on public.locp utrtest_1 Foreign Update on public.remp utrtest_2 - -> Seq Scan on public.locp utrtest_1 - Output: 3, utrtest_1.b, utrtest_1.ctid - -> Foreign Update on public.remp utrtest_2 - Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b -(8 rows) + -> Append + -> Seq Scan on public.locp utrtest_1 + Output: 3, utrtest_1.tableoid, utrtest_1.ctid, NULL::record + -> Foreign Update on public.remp utrtest_2 + Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b +(9 rows) update utrtest set a = 3 returning *; -- ERROR ERROR: cannot route tuples into foreign table to be updated "remp" -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------- Update on public.utrtest Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 Update on public.locp utrtest_1 Foreign Update on public.remp utrtest_2 Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b -> Hash Join - Output: 3, utrtest_1.b, utrtest_1.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (utrtest_1.a = "*VALUES*".column1) - -> Seq Scan on public.locp utrtest_1 - Output: utrtest_1.b, utrtest_1.ctid, utrtest_1.a - -> Hash - Output: "*VALUES*".*, "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".*, "*VALUES*".column1 - -> Hash Join - Output: 3, utrtest_2.b, utrtest_2.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (utrtest_2.a = "*VALUES*".column1) - -> Foreign Scan on public.remp utrtest_2 - Output: utrtest_2.b, utrtest_2.ctid, utrtest_2.a - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE + Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record) + Hash Cond: (utrtest.a = "*VALUES*".column1) + -> Append + -> Seq Scan on public.locp utrtest_1 + Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::record + -> Foreign Scan on public.remp utrtest_2 + Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.* + Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" Output: "*VALUES*".*, "*VALUES*".column1 -(24 rows) +(18 rows) update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR ERROR: cannot route tuples into foreign table to be updated "remp" @@ -9428,11 +9390,12 @@ CREATE TABLE batch_cp_up_test1 PARTITION OF batch_cp_upd_test INSERT INTO batch_cp_upd_test VALUES (1), (2); -- The following moves a row from the local partition to the foreign one UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a; +ERROR: cannot route tuples into foreign table to be updated "batch_cp_upd_test1_f" SELECT tableoid::regclass, * FROM batch_cp_upd_test; tableoid | a ----------------------+--- batch_cp_upd_test1_f | 1 - batch_cp_upd_test1_f | 1 + batch_cp_up_test1 | 2 (2 rows) -- Clean up diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index cc73a6902f5..649f15113d0 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -27,6 +27,7 @@ #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "optimizer/appendinfo.h" #include "optimizer/clauses.h" #include "optimizer/cost.h" #include "optimizer/optimizer.h" @@ -345,7 +346,8 @@ static void postgresBeginForeignScan(ForeignScanState *node, int eflags); static TupleTableSlot *postgresIterateForeignScan(ForeignScanState *node); static void postgresReScanForeignScan(ForeignScanState *node); static void postgresEndForeignScan(ForeignScanState *node); -static void postgresAddForeignUpdateTargets(Query *parsetree, +static void postgresAddForeignUpdateTargets(PlannerInfo *root, + Index rtindex, RangeTblEntry *target_rte, Relation target_relation); static List *postgresPlanForeignModify(PlannerInfo *root, @@ -1669,36 +1671,27 @@ postgresEndForeignScan(ForeignScanState *node) * Add resjunk column(s) needed for update/delete on a foreign table */ static void -postgresAddForeignUpdateTargets(Query *parsetree, +postgresAddForeignUpdateTargets(PlannerInfo *root, + Index rtindex, RangeTblEntry *target_rte, Relation target_relation) { Var *var; - const char *attrname; - TargetEntry *tle; /* * In postgres_fdw, what we need is the ctid, same as for a regular table. */ /* Make a Var representing the desired value */ - var = makeVar(parsetree->resultRelation, + var = makeVar(rtindex, SelfItemPointerAttributeNumber, TIDOID, -1, InvalidOid, 0); - /* Wrap it in a resjunk TLE with the right name ... */ - attrname = "ctid"; - - tle = makeTargetEntry((Expr *) var, - list_length(parsetree->targetList) + 1, - pstrdup(attrname), - true); - - /* ... and add it to the query's targetlist */ - parsetree->targetList = lappend(parsetree->targetList, tle); + /* Register it as a row-identity column needed by this target rel */ + add_row_identity_var(root, var, rtindex, "ctid"); } /* @@ -1886,7 +1879,7 @@ postgresBeginForeignModify(ModifyTableState *mtstate, rte, resultRelInfo, mtstate->operation, - mtstate->mt_plans[subplan_index]->plan, + outerPlanState(mtstate)->plan, query, target_attrs, values_end_len, @@ -2086,8 +2079,7 @@ postgresBeginForeignInsert(ModifyTableState *mtstate, */ if (plan && plan->operation == CMD_UPDATE && (resultRelInfo->ri_usesFdwDirectModify || - resultRelInfo->ri_FdwState) && - resultRelInfo > mtstate->resultRelInfo + mtstate->mt_whichplan) + resultRelInfo->ri_FdwState)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot route tuples into foreign table to be updated \"%s\"", @@ -2284,6 +2276,65 @@ postgresRecheckForeignScan(ForeignScanState *node, TupleTableSlot *slot) } /* + * find_modifytable_subplan + * Helper routine for postgresPlanDirectModify to find the + * ModifyTable subplan node that scans the specified RTI. + * + * Returns NULL if the subplan couldn't be identified. That's not a fatal + * error condition, we just abandon trying to do the update directly. + */ +static ForeignScan * +find_modifytable_subplan(PlannerInfo *root, + ModifyTable *plan, + Index rtindex, + int subplan_index) +{ + Plan *subplan = outerPlan(plan); + + /* + * The cases we support are (1) the desired ForeignScan is the immediate + * child of ModifyTable, or (2) it is the subplan_index'th child of an + * Append node that is the immediate child of ModifyTable. There is no + * point in looking further down, as that would mean that local joins are + * involved, so we can't do the update directly. + * + * There could be a Result atop the Append too, acting to compute the + * UPDATE targetlist values. We ignore that here; the tlist will be + * checked by our caller. + * + * In principle we could examine all the children of the Append, but it's + * currently unlikely that the core planner would generate such a plan + * with the children out-of-order. Moreover, such a search risks costing + * O(N^2) time when there are a lot of children. + */ + if (IsA(subplan, Append)) + { + Append *appendplan = (Append *) subplan; + + if (subplan_index < list_length(appendplan->appendplans)) + subplan = (Plan *) list_nth(appendplan->appendplans, subplan_index); + } + else if (IsA(subplan, Result) && IsA(outerPlan(subplan), Append)) + { + Append *appendplan = (Append *) outerPlan(subplan); + + if (subplan_index < list_length(appendplan->appendplans)) + subplan = (Plan *) list_nth(appendplan->appendplans, subplan_index); + } + + /* Now, have we got a ForeignScan on the desired rel? */ + if (IsA(subplan, ForeignScan)) + { + ForeignScan *fscan = (ForeignScan *) subplan; + + if (bms_is_member(rtindex, fscan->fs_relids)) + return fscan; + } + + return NULL; +} + +/* * postgresPlanDirectModify * Consider a direct foreign table modification * @@ -2297,13 +2348,13 @@ postgresPlanDirectModify(PlannerInfo *root, int subplan_index) { CmdType operation = plan->operation; - Plan *subplan; RelOptInfo *foreignrel; RangeTblEntry *rte; PgFdwRelationInfo *fpinfo; Relation rel; StringInfoData sql; ForeignScan *fscan; + List *processed_tlist = NIL; List *targetAttrs = NIL; List *remote_exprs; List *params_list = NIL; @@ -2321,19 +2372,17 @@ postgresPlanDirectModify(PlannerInfo *root, return false; /* - * It's unsafe to modify a foreign table directly if there are any local - * joins needed. + * Try to locate the ForeignScan subplan that's scanning resultRelation. */ - subplan = (Plan *) list_nth(plan->plans, subplan_index); - if (!IsA(subplan, ForeignScan)) + fscan = find_modifytable_subplan(root, plan, resultRelation, subplan_index); + if (!fscan) return false; - fscan = (ForeignScan *) subplan; /* * It's unsafe to modify a foreign table directly if there are any quals * that should be evaluated locally. */ - if (subplan->qual != NIL) + if (fscan->scan.plan.qual != NIL) return false; /* Safe to fetch data about the target foreign rel */ @@ -2354,32 +2403,28 @@ postgresPlanDirectModify(PlannerInfo *root, */ if (operation == CMD_UPDATE) { - int col; + ListCell *lc, + *lc2; /* - * We transmit only columns that were explicitly targets of the - * UPDATE, so as to avoid unnecessary data transmission. + * The expressions of concern are the first N columns of the processed + * targetlist, where N is the length of the rel's update_colnos. */ - col = -1; - while ((col = bms_next_member(rte->updatedCols, col)) >= 0) + get_translated_update_targetlist(root, resultRelation, + &processed_tlist, &targetAttrs); + forboth(lc, processed_tlist, lc2, targetAttrs) { - /* bit numbers are offset by FirstLowInvalidHeapAttributeNumber */ - AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber; - TargetEntry *tle; + TargetEntry *tle = lfirst_node(TargetEntry, lc); + AttrNumber attno = lfirst_int(lc2); + + /* update's new-value expressions shouldn't be resjunk */ + Assert(!tle->resjunk); if (attno <= InvalidAttrNumber) /* shouldn't happen */ elog(ERROR, "system-column update is not supported"); - tle = get_tle_by_resno(subplan->targetlist, attno); - - if (!tle) - elog(ERROR, "attribute number %d not found in subplan targetlist", - attno); - if (!is_foreign_expr(root, foreignrel, (Expr *) tle->expr)) return false; - - targetAttrs = lappend_int(targetAttrs, attno); } } @@ -2430,7 +2475,7 @@ postgresPlanDirectModify(PlannerInfo *root, case CMD_UPDATE: deparseDirectUpdateSql(&sql, root, resultRelation, rel, foreignrel, - ((Plan *) fscan)->targetlist, + processed_tlist, targetAttrs, remote_exprs, ¶ms_list, returningList, &retrieved_attrs); |