Rework planning and execution of UPDATE and DELETE.

This patch makes two closely related sets of changes:

1. For UPDATE, the subplan of the ModifyTable node now only delivers
the new values of the changed columns (i.e., the expressions computed
in the query's SET clause) plus row identity information such as CTID.
ModifyTable must re-fetch the original tuple to merge in the old
values of any unchanged columns.  The core advantage of this is that
the changed columns are uniform across all tables of an inherited or
partitioned target relation, whereas the other columns might not be.
A secondary advantage, when the UPDATE involves joins, is that less
data needs to pass through the plan tree.  The disadvantage of course
is an extra fetch of each tuple to be updated.  However, that seems to
be very nearly free in context; even worst-case tests don't show it to
add more than a couple percent to the total query cost.  At some point
it might be interesting to combine the re-fetch with the tuple access
that ModifyTable must do anyway to mark the old tuple dead; but that
would require a good deal of refactoring and it seems it wouldn't buy
all that much, so this patch doesn't attempt it.

2. For inherited UPDATE/DELETE, instead of generating a separate
subplan for each target relation, we now generate a single subplan
that is just exactly like a SELECT's plan, then stick ModifyTable
on top of that.  To let ModifyTable know which target relation a
given incoming row refers to, a tableoid junk column is added to
the row identity information.  This gets rid of the horrid hack
that was inheritance_planner(), eliminating O(N^2) planning cost
and memory consumption in cases where there were many unprunable
target relations.

Point 2 of course requires point 1, so that there is a uniform
definition of the non-junk columns to be returned by the subplan.
We can't insist on uniform definition of the row identity junk
columns however, if we want to keep the ability to have both
plain and foreign tables in a partitioning hierarchy.  Since
it wouldn't scale very far to have every child table have its
own row identity column, this patch includes provisions to merge
similar row identity columns into one column of the subplan result.
In particular, we can merge the whole-row Vars typically used as
row identity by FDWs into one column by pretending they are type
RECORD.  (It's still okay for the actual composite Datums to be
labeled with the table's rowtype OID, though.)

There is more that can be done to file down residual inefficiencies
in this patch, but it seems to be committable now.

FDW authors should note several API changes:

* The argument list for AddForeignUpdateTargets() has changed, and so
has the method it must use for adding junk columns to the query.  Call
add_row_identity_var() instead of manipulating the parse tree directly.
You might want to reconsider exactly what you're adding, too.

* PlanDirectModify() must now work a little harder to find the
ForeignScan plan node; if the foreign table is part of a partitioning
hierarchy then the ForeignScan might not be the direct child of
ModifyTable.  See postgres_fdw for sample code.

* To check whether a relation is a target relation, it's no
longer sufficient to compare its relid to root->parse->resultRelation.
Instead, check it against all_result_relids or leaf_result_relids,
as appropriate.

Amit Langote and Tom Lane

Discussion: https://postgr.es/m/CA+HiwqHpHdqdDn48yCEhynnniahH78rwcrv1rEX65-fsZGBOLQ@mail.gmail.com

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