diff options
Diffstat (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 423 |
1 files changed, 193 insertions, 230 deletions
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 |