public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tender Wang <[email protected]>
To: Amit Langote <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: jian he <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error
Date: Sun, 30 Nov 2025 13:59:59 +0800
Message-ID: <CAHewXNnD0744Vykj6ujE5c=rRP=61sh7K154uNdgEaTmJrRegQ@mail.gmail.com> (raw)
In-Reply-To: <CA+HiwqGk1X-EiVL5kJjHD7V=a3JVDQodt2pwb9SK7q+cYQnpTg@mail.gmail.com>
References: <[email protected]>
<CACJufxF9FcuYe8XOuWLgWK77HCUHpOc6+7+NkktFFNmzw15jKg@mail.gmail.com>
<CAHewXN=vF5d9O4R3+iUwLqEaP7pb8iYAN_e3vEE_p5sJHofn7w@mail.gmail.com>
<[email protected]>
<CALdSSPi7udsgQg3PUG=Z4+-9pRg8wT3HkDvTgYvtg30xNWQ9OA@mail.gmail.com>
<CALdSSPi9n2KGzKQn2Egqz3H8Nx0cgnZ8UeB5gk-KVdE3uBCj6Q@mail.gmail.com>
<CA+HiwqFcejrmS_H8YB-AMB7sujB7wdJXFPdAVfDC6-19FXUjgg@mail.gmail.com>
<CAHewXNmx+UXg46+WUrbPca91bmVipRTpe+SRm19GtxG6mArRhg@mail.gmail.com>
<CALdSSPi6xR1tG2kLvpwNLnAjG9e0wmaY62r2_MF81ZYg5in+qQ@mail.gmail.com>
<[email protected]>
<CAApHDvpYEqJ6h-3NWi_4S19RY9NARpJ3h8CRmWYbz5MJFqE-sg@mail.gmail.com>
<CA+HiwqEHHTG5_TKuNw1M0dCrgUd6SauJ5dcdicz7xozMJip0SA@mail.gmail.com>
<CA+HiwqGk1X-EiVL5kJjHD7V=a3JVDQodt2pwb9SK7q+cYQnpTg@mail.gmail.com>
Amit Langote <[email protected]> 于2025年11月26日周三 19:27写道：
> On Thu, Nov 6, 2025 at 7:00 PM Amit Langote <[email protected]>
> wrote:
> > Among those options, I considered the following block, which adds a
> > ctid for the partitioned root table when it’s the only target in the
> > query after partition pruning removes all child tables due to the
> > WHERE false condition in the problematic case:
> >
> > /*
> > * Ordinarily, we expect that leaf result relation(s) will have
> added some
> > * ROWID_VAR Vars to the query. However, it's possible that
> constraint
> > * exclusion suppressed every leaf relation. The executor will get
> upset
> > * if the plan has no row identity columns at all, even though it
> will
> > * certainly process no rows. Handle this edge case by re-opening
> the top
> > * result relation and adding the row identity columns it would have
> used,
> > * as preprocess_targetlist() would have done if it weren't marked
> "inh".
> > * Then re-run build_base_rel_tlists() to ensure that the added
> columns
> > * get propagated to the relation's reltarget. (This is a bit ugly,
> but
> > * it seems better to confine the ugliness and extra cycles to this
> > * unusual corner case.)
> > */
> > if (root->row_identity_vars == NIL)
> > {
> > Relation target_relation;
> >
> > target_relation = table_open(target_rte->relid, NoLock);
> > add_row_identity_columns(root, result_relation,
> > target_rte, target_relation);
> > table_close(target_relation, NoLock);
> > build_base_rel_tlists(root, root->processed_tlist);
> > /* There are no ROWID_VAR Vars in this case, so we're done. */
> > return;
> > }
> >
> > If enable_partition_pruning is off, root->row_identity_vars already
> > contains a RowIdentityVarInfo entry for the tableoid Var that was
> > added while processing the foreign-table child partition. Because of
> > that, the if (root->row_identity_vars == NIL) block doesn’t run in
> > this case, so it won’t add any row identity columns such as ctid for
> > the partitioned root table.
> >
> > In theory, we could prevent the planner from adding tableoid in the
> > first place when the child table doesn’t support any row identity
> > column -- or worse, doesn’t support the UPDATE/DELETE/MERGE command at
> > all -- but doing so would require changing the order in which tableoid
> > appears in root->processed_tlist. That would be too invasive for a
> > back-patch.
>
> I’ve implemented this alternative as well -- the version that prevents
> adding tableoid when no other row-identity columns are added for the
> child. That allows to keep root->row_identity_vars empty so the
> dummy-root path can add ctid as intended by the above code block of
> distribute_row_identity_vars().
>
> This provides an alternative approach to compare against the other patch.
>
> --
> Thanks, Amit Langote
>
I apply the patch, and I find it forgets to update the diff for
postgres_fdw.
So I add it in the v2 patch.
With this patch, the targetlists are identical whether or not
enable_partition_pruning is on.
In my first email on this thread, to avoid adding "tableoid", I tried to
add the following codes:
"(childrte->relkind != RELKIND_PARTITIONED_TABLE && childrte->relkind
!= RELKIND_FOREIGN_TABLE)"
in expand_single_inheritance_child().
But this didn't work for all test cases. It would trigger an assert failure
in fix_scan_expr_walker():
Assert(!(IsA(node, Var) && ((Var *) node)->varno == ROWID_VAR));
Your patch is much better than mine.
--
Thanks,
Tender Wang
From 1033e207b10b3d3dbb38c3eff49261ecbdffd16e Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Sun, 30 Nov 2025 13:47:23 +0800
Subject: [PATCH v2] Fix row-identity handling for dummy partitioned
resultrels.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Stop adding tableoid for child relations that do not have any
row-identity columns. In cases where all partitions are excluded
by pruning or constraint exclusion, this allows
distribute_row_identity_vars() to detect the empty state
(root->row_identity_vars == NIL) and add the appropriate ctid
column for the dummy partitioned result relation, satisfying the
executor’s requirement that a resultrel always have a row identity.
As part of this, make add_row_identity_columns() return a boolean
to report whether any row-identity columns were added, and skip FDW
children that cannot support the current command. Adjust expected
EXPLAIN output accordingly and extend file_fdw tests to cover
dummy-root plans with and without pruning.
---
contrib/file_fdw/expected/file_fdw.out | 75 ++++++++++++++++
contrib/file_fdw/sql/file_fdw.sql | 34 ++++++++
.../postgres_fdw/expected/postgres_fdw.out | 86 +++++++++----------
src/backend/optimizer/prep/preptlist.c | 4 +-
src/backend/optimizer/util/appendinfo.c | 18 +++-
src/backend/optimizer/util/inherit.c | 6 +-
src/include/optimizer/appendinfo.h | 2 +-
src/test/regress/expected/inherit.out | 14 +--
src/test/regress/expected/merge.out | 6 +-
src/test/regress/expected/partition_prune.out | 4 +-
src/test/regress/expected/returning.out | 24 +++---
src/test/regress/expected/updatable_views.out | 20 ++---
src/test/regress/expected/with.out | 14 +--
13 files changed, 214 insertions(+), 93 deletions(-)
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 5121e27dce5..e60177af8c8 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -457,6 +457,81 @@ SELECT tableoid::regclass, * FROM p2;
p2 | 2 | xyzzy
(3 rows)
+-- Verify that a dummy root partitioned-table result relation works without
+-- error when all child partitions are excluded from the plan (for example,
+-- by constraint exclusion or pruning). In this case, the executor accepts
+-- a missing ctid for the root result relation since no rows can be produced.
+-- When a foreign-table child is processed before exclusion, a tableoid junk
+-- column may still appear in the targetlist and also wholerow for update.
+-- Dummy-root cases where all children are excluded.
+-- With pruning off, the foreign child is processed first, then excluded
+-- by constraint exclusion. EXPLAIN shows tableoid (rewritten to NULL),
+-- and for UPDATE also wholerow as NULL::record. No ctid.
+DROP TABLE p2;
+SET enable_partition_pruning TO off;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+ QUERY PLAN
+--------------------------------
+ Delete on public.pt
+ -> Result
+ Output: pt.ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+ QUERY PLAN
+------------------------------------
+ Update on public.pt
+ -> Result
+ Output: 'x'::text, pt.ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- MERGE behaves the same here; expect NULL::oid
+EXPLAIN (COSTS OFF, VERBOSE) MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b)
+ ON false WHEN MATCHED THEN UPDATE SET b = s.b;
+ QUERY PLAN
+--------------------------------
+ Merge on public.pt t
+ -> Result
+ Output: t.ctid
+ Replaces: Scan on t
+ One-Time Filter: false
+(5 rows)
+
+-- With pruning on, the foreign child is pruned entirely. The plan has only
+-- the dummy root, and EXPLAIN shows ctid (and for UPDATE, ctid plus target).
+SET enable_partition_pruning TO on;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+ QUERY PLAN
+--------------------------------
+ Delete on public.pt
+ -> Result
+ Output: ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+ QUERY PLAN
+---------------------------------
+ Update on public.pt
+ -> Result
+ Output: 'x'::text, ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- Foreign child not pruned and it does not support DELETE: error.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE a = 1;
+ERROR: cannot delete from foreign table "p1"
+-- Runtime pruning includes the foreign child in the plan; executor errors
+-- since the foreign child does not support the command.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE (SELECT false);
+ERROR: cannot delete from foreign table "p1"
DROP TABLE pt;
-- generated column tests
\set filename :abs_srcdir '/data/list1.csv'
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 1a397ad4bd1..25658b1f2dc 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -242,6 +242,40 @@ UPDATE pt set a = 1 where a = 2; -- ERROR
SELECT tableoid::regclass, * FROM pt;
SELECT tableoid::regclass, * FROM p1;
SELECT tableoid::regclass, * FROM p2;
+
+-- Verify that a dummy root partitioned-table result relation works without
+-- error when all child partitions are excluded from the plan (for example,
+-- by constraint exclusion or pruning). In this case, the executor accepts
+-- a missing ctid for the root result relation since no rows can be produced.
+-- When a foreign-table child is processed before exclusion, a tableoid junk
+-- column may still appear in the targetlist and also wholerow for update.
+
+-- Dummy-root cases where all children are excluded.
+-- With pruning off, the foreign child is processed first, then excluded
+-- by constraint exclusion. EXPLAIN shows tableoid (rewritten to NULL),
+-- and for UPDATE also wholerow as NULL::record. No ctid.
+DROP TABLE p2;
+SET enable_partition_pruning TO off;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+-- MERGE behaves the same here; expect NULL::oid
+EXPLAIN (COSTS OFF, VERBOSE) MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b)
+ ON false WHEN MATCHED THEN UPDATE SET b = s.b;
+
+-- With pruning on, the foreign child is pruned entirely. The plan has only
+-- the dummy root, and EXPLAIN shows ctid (and for UPDATE, ctid plus target).
+SET enable_partition_pruning TO on;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+
+-- Foreign child not pruned and it does not support DELETE: error.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE a = 1;
+
+-- Runtime pruning includes the foreign child in the plan; executor errors
+-- since the foreign child does not support the command.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE (SELECT false);
+
DROP TABLE pt;
-- generated column tests
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48e3185b227..7a20ee06027 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7399,7 +7399,7 @@ UPDATE rw_view SET b = b + 5;
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.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 5), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
@@ -7414,7 +7414,7 @@ UPDATE rw_view SET b = b + 15;
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.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 15), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
@@ -7470,7 +7470,7 @@ UPDATE rw_view SET b = 'text', c = 123.456;
Foreign Update on public.child_foreign parent_tbl_1
Remote SQL: UPDATE public.child_local SET b = $2, c = $3 WHERE ctid = $1 RETURNING a
-> Foreign Scan on public.child_foreign parent_tbl_1
- Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: 'text'::text, 123.456, parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT b, c, a, ctid FROM public.child_local WHERE ((a < 5)) FOR UPDATE
(6 rows)
@@ -8268,7 +8268,7 @@ UPDATE parent_tbl SET b = b + 1;
Foreign Update on public.foreign_tbl parent_tbl_1
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
-> Foreign Scan on public.foreign_tbl parent_tbl_1
- Output: (parent_tbl_1.b + 1), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 1), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
(6 rows)
@@ -8282,7 +8282,7 @@ DELETE FROM parent_tbl;
Foreign Delete on public.foreign_tbl parent_tbl_1
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
-> Foreign Scan on public.foreign_tbl parent_tbl_1
- Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
+ Output: parent_tbl_1.ctid, parent_tbl_1.tableoid
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
(6 rows)
@@ -8308,12 +8308,12 @@ UPDATE parent_tbl SET b = b + 1;
Foreign Update on public.foreign_tbl parent_tbl_2
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
-> Result
- Output: (parent_tbl.b + 1), parent_tbl.tableoid, parent_tbl.ctid, (NULL::record)
+ Output: (parent_tbl.b + 1), parent_tbl.ctid, parent_tbl.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.parent_tbl parent_tbl_1
- Output: parent_tbl_1.b, parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::record
+ Output: parent_tbl_1.b, parent_tbl_1.ctid, parent_tbl_1.tableoid, NULL::record
-> Foreign Scan on public.foreign_tbl parent_tbl_2
- Output: parent_tbl_2.b, parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.*
+ Output: parent_tbl_2.b, parent_tbl_2.ctid, parent_tbl_2.tableoid, parent_tbl_2.*
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
(12 rows)
@@ -8329,9 +8329,9 @@ DELETE FROM parent_tbl;
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
-> Append
-> Seq Scan on public.parent_tbl parent_tbl_1
- Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
+ Output: parent_tbl_1.ctid, parent_tbl_1.tableoid
-> Foreign Scan on public.foreign_tbl parent_tbl_2
- Output: parent_tbl_2.tableoid, parent_tbl_2.ctid
+ Output: parent_tbl_2.ctid, parent_tbl_2.tableoid
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
(10 rows)
@@ -8682,14 +8682,14 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Hash Join
- Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, (NULL::record), foo.*, foo.tableoid
+ Output: (bar.f2 + 100), foo.ctid, bar.ctid, bar.tableoid, (NULL::record), foo.*, foo.tableoid
Inner Unique: true
Hash Cond: (bar.f1 = foo.f1)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.f1, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.f1, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
@@ -8729,16 +8729,16 @@ where bar.f1 = ss.f1;
Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Merge Join
- Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), (ROW(foo.f1)), bar.ctid, bar.tableoid, (NULL::record)
Merge Cond: (bar.f1 = foo.f1)
-> Sort
- Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record)
+ Output: bar.f2, bar.f1, bar.ctid, bar.tableoid, (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
+ Output: bar_1.f2, bar_1.f1, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.f1, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
@@ -8888,7 +8888,7 @@ delete from foo where f1 < 5 returning *;
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
+ Output: foo_1.ctid, foo_1.tableoid
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
@@ -8913,10 +8913,10 @@ update bar set f2 = f2 + 100 returning *;
Update on public.bar bar_1
Foreign Update on public.bar2 bar_2
-> Result
- Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), bar.ctid, bar.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2
(11 rows)
@@ -8948,12 +8948,12 @@ update bar set f2 = f2 + 100;
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
-> Result
- Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), bar.ctid, bar.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
(12 rows)
@@ -8980,10 +8980,10 @@ delete from bar where f2 < 400;
Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.ctid, bar_1.tableoid, NULL::record
Filter: (bar_1.f2 < 400)
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(11 rows)
@@ -9024,13 +9024,13 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re
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.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, (NULL::record)
+ Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.ctid, parent.tableoid, (NULL::record)
Join Filter: (parent.a = remt2.a)
-> Append
-> Seq Scan on public.parent parent_1
- Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::record
+ Output: parent_1.b, parent_1.a, parent_1.ctid, parent_1.tableoid, NULL::record
-> Foreign Scan on public.remt1 parent_2
- Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.*
+ Output: parent_2.b, parent_2.a, parent_2.ctid, parent_2.tableoid, parent_2.*
Remote SQL: SELECT a, b, ctid FROM public.loct1 FOR UPDATE
-> Materialize
Output: remt2.b, remt2.*, remt2.a
@@ -9056,13 +9056,13 @@ delete from parent using remt2 where parent.a = remt2.a returning parent;
Foreign Delete on public.remt1 parent_2
Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 RETURNING a, b
-> Nested Loop
- Output: remt2.*, parent.tableoid, parent.ctid
+ Output: remt2.*, parent.ctid, parent.tableoid
Join Filter: (parent.a = remt2.a)
-> Append
-> Seq Scan on public.parent parent_1
- Output: parent_1.a, parent_1.tableoid, parent_1.ctid
+ Output: parent_1.a, parent_1.ctid, parent_1.tableoid
-> Foreign Scan on public.remt1 parent_2
- Output: parent_2.a, parent_2.tableoid, parent_2.ctid
+ Output: parent_2.a, parent_2.ctid, parent_2.tableoid
Remote SQL: SELECT a, ctid FROM public.loct1 FOR UPDATE
-> Materialize
Output: remt2.*, remt2.a
@@ -9293,7 +9293,7 @@ update utrtest set a = 1 where a = 1 or a = 2 returning *;
-> 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
+ Output: 1, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
(10 rows)
@@ -9311,7 +9311,7 @@ update utrtest set a = 1 where a = 2 returning *;
Output: utrtest_1.a, utrtest_1.b
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
- Output: 1, utrtest_1.tableoid, utrtest_1.ctid
+ Output: 1, utrtest_1.ctid, utrtest_1.tableoid
Filter: (utrtest_1.a = 2)
(6 rows)
@@ -9342,7 +9342,7 @@ update utrtest set a = 1 returning *;
-> 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
+ Output: 1, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
(9 rows)
update utrtest set a = 1 returning *;
@@ -9361,14 +9361,14 @@ update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
Update on public.locp utrtest_2
-> Hash Join
- Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.*
+ Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.ctid, utrtest.*, utrtest.tableoid
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.*
+ Output: utrtest_1.a, utrtest_1.ctid, utrtest_1.*, utrtest_1.tableoid
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
+ Output: utrtest_2.a, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
-> Hash
Output: "*VALUES*".*, "*VALUES*".column1
-> Values Scan on "*VALUES*"
@@ -9400,7 +9400,7 @@ update utrtest set a = 3 returning *;
Foreign Update on public.remp utrtest_2
-> Append
-> Seq Scan on public.locp utrtest_1
- Output: 3, utrtest_1.tableoid, utrtest_1.ctid, NULL::record
+ Output: 3, utrtest_1.ctid, utrtest_1.tableoid, NULL::record
-> Foreign Update on public.remp utrtest_2
Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b
(9 rows)
@@ -9418,13 +9418,13 @@ update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
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, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record)
+ Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.ctid, utrtest.tableoid, (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
+ Output: utrtest_1.a, utrtest_1.ctid, utrtest_1.tableoid, NULL::record
-> Foreign Scan on public.remp utrtest_2
- Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.*
+ Output: utrtest_2.a, utrtest_2.ctid, utrtest_2.tableoid, utrtest_2.*
Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE
-> Hash
Output: "*VALUES*".*, "*VALUES*".column1
@@ -12325,7 +12325,7 @@ UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
-> Foreign Update on public.async_p2 async_pt_2
Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
-> Seq Scan on public.async_p3 async_pt_3
- Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record
+ Output: (async_pt_3.c || async_pt_3.c), async_pt_3.ctid, NULL::record, async_pt_3.tableoid
Filter: (async_pt_3.b = 0)
(13 rows)
@@ -12352,7 +12352,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *;
-> Foreign Delete on public.async_p2 async_pt_2
Remote SQL: DELETE FROM public.base_tbl2 WHERE ((b = 0)) RETURNING a, b, c
-> Seq Scan on public.async_p3 async_pt_3
- Output: async_pt_3.tableoid, async_pt_3.ctid
+ Output: async_pt_3.ctid, async_pt_3.tableoid
Filter: (async_pt_3.b = 0)
(13 rows)
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index ffc9d6c3f30..26090d71dfd 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -122,8 +122,8 @@ preprocess_targetlist(PlannerInfo *root)
{
/* row-identity logic expects to add stuff to processed_tlist */
root->processed_tlist = tlist;
- add_row_identity_columns(root, result_relation,
- target_rte, target_relation);
+ (void) add_row_identity_columns(root, result_relation,
+ target_rte, target_relation);
tlist = root->processed_tlist;
}
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
index 69b8b0c2ae0..f977dfda208 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -951,7 +951,7 @@ add_row_identity_var(PlannerInfo *root, Var *orig_var,
* FDWs might call add_row_identity_var() for themselves to add nonstandard
* columns. (Duplicate requests are fine.)
*/
-void
+bool
add_row_identity_columns(PlannerInfo *root, Index rtindex,
RangeTblEntry *target_rte,
Relation target_relation)
@@ -977,6 +977,7 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
InvalidOid,
0);
add_row_identity_var(root, var, rtindex, "ctid");
+ return true;
}
else if (relkind == RELKIND_FOREIGN_TABLE)
{
@@ -987,6 +988,13 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
fdwroutine = GetFdwRoutineForRelation(target_relation, false);
+ if (commandType == CMD_MERGE ||
+ (commandType == CMD_UPDATE &&
+ fdwroutine->ExecForeignUpdate == NULL) ||
+ (commandType == CMD_DELETE &&
+ fdwroutine->ExecForeignDelete == NULL))
+ return false;
+
if (fdwroutine->AddForeignUpdateTargets != NULL)
fdwroutine->AddForeignUpdateTargets(root, rtindex,
target_rte, target_relation);
@@ -1017,7 +1025,11 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
0);
add_row_identity_var(root, var, rtindex, "wholerow");
}
+
+ return true;
}
+
+ return false;
}
/*
@@ -1075,8 +1087,8 @@ distribute_row_identity_vars(PlannerInfo *root)
Relation target_relation;
target_relation = table_open(target_rte->relid, NoLock);
- add_row_identity_columns(root, result_relation,
- target_rte, target_relation);
+ (void) add_row_identity_columns(root, result_relation,
+ target_rte, target_relation);
table_close(target_relation, NoLock);
build_base_rel_tlists(root, root->processed_tlist);
/* There are no ROWID_VAR Vars in this case, so we're done. */
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index 6d5225079f8..96c24a8a552 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -634,11 +634,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
-1,
InvalidOid,
0);
- add_row_identity_var(root, rrvar, childRTindex, "tableoid");
/* Register any row-identity columns needed by this child. */
- add_row_identity_columns(root, childRTindex,
- childrte, childrel);
+ if (add_row_identity_columns(root, childRTindex,
+ childrte, childrel))
+ add_row_identity_var(root, rrvar, childRTindex, "tableoid");
}
}
}
diff --git a/src/include/optimizer/appendinfo.h b/src/include/optimizer/appendinfo.h
index d06f93b7266..5f3168d612f 100644
--- a/src/include/optimizer/appendinfo.h
+++ b/src/include/optimizer/appendinfo.h
@@ -42,7 +42,7 @@ extern AppendRelInfo **find_appinfos_by_relids(PlannerInfo *root,
Relids relids, int *nappinfos);
extern void add_row_identity_var(PlannerInfo *root, Var *orig_var,
Index rtindex, const char *rowid_name);
-extern void add_row_identity_columns(PlannerInfo *root, Index rtindex,
+extern bool add_row_identity_columns(PlannerInfo *root, Index rtindex,
RangeTblEntry *target_rte,
Relation target_relation);
extern void distribute_row_identity_vars(PlannerInfo *root);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..e8fcae6514f 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -579,7 +579,7 @@ update some_tab set a = a + 1 where false;
--------------------------------------------------------
Update on public.some_tab
-> Result
- Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Output: (some_tab.a + 1), NULL::tid, NULL::oid
Replaces: Scan on some_tab
One-Time Filter: false
(5 rows)
@@ -592,7 +592,7 @@ update some_tab set a = a + 1 where false returning b, a;
Update on public.some_tab
Output: some_tab.b, some_tab.a
-> Result
- Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Output: (some_tab.a + 1), NULL::tid, NULL::oid
Replaces: Scan on some_tab
One-Time Filter: false
(6 rows)
@@ -2054,12 +2054,12 @@ update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
Update on public.inhpar i_1
Update on public.inhcld i_2
-> Result
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i.tableoid, i.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i.ctid, i.tableoid
-> Append
-> Seq Scan on public.inhpar i_1
- Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid
+ Output: i_1.f1, i_1.f2, i_1.ctid, i_1.tableoid
-> Seq Scan on public.inhcld i_2
- Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid
+ Output: i_2.f1, i_2.f2, i_2.ctid, i_2.tableoid
SubPlan multiexpr_1
-> Limit
Output: (i.f1), (((i.f2)::text || '-'::text))
@@ -2103,14 +2103,14 @@ update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
Update on public.inhcld2 i_2
-> Append
-> Seq Scan on public.inhcld1 i_1
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.tableoid, i_1.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.ctid, i_1.tableoid
SubPlan multiexpr_1
-> Limit
Output: (i_1.f1), (((i_1.f2)::text || '-'::text))
-> Seq Scan on public.int4_tbl
Output: i_1.f1, ((i_1.f2)::text || '-'::text)
-> Seq Scan on public.inhcld2 i_2
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.tableoid, i_2.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.ctid, i_2.tableoid
(13 rows)
update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 9cb1d87066a..10b27b01532 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -2387,15 +2387,15 @@ MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
Merge on public.pa_target t
Merge on public.pa_targetp t_1
-> Hash Left Join
- Output: s.sid, s.ctid, t_1.tableoid, t_1.ctid
+ Output: s.sid, s.ctid, t_1.ctid, t_1.tableoid
Inner Unique: true
Hash Cond: (s.sid = t_1.tid)
-> Seq Scan on public.pa_source s
Output: s.sid, s.ctid
-> Hash
- Output: t_1.tid, t_1.tableoid, t_1.ctid
+ Output: t_1.tid, t_1.ctid, t_1.tableoid
-> Seq Scan on public.pa_targetp t_1
- Output: t_1.tid, t_1.tableoid, t_1.ctid
+ Output: t_1.tid, t_1.ctid, t_1.tableoid
(12 rows)
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index deacdd75807..24c6ac408f3 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4585,7 +4585,7 @@ explain (verbose, costs off) execute update_part_abc_view (1, 'd');
-> Append
Subplans Removed: 1
-> Seq Scan on public.part_abc_1
- Output: $2, part_abc_1.tableoid, part_abc_1.ctid
+ Output: $2, part_abc_1.ctid, part_abc_1.tableoid
Filter: ((part_abc_1.b <> 'a'::text) AND (part_abc_1.a = $1))
(8 rows)
@@ -4604,7 +4604,7 @@ explain (verbose, costs off) execute update_part_abc_view (2, 'a');
-> Append
Subplans Removed: 1
-> Seq Scan on public.part_abc_2
- Output: $2, part_abc_2.tableoid, part_abc_2.ctid
+ Output: $2, part_abc_2.ctid, part_abc_2.tableoid
Filter: ((part_abc_2.b <> 'a'::text) AND (part_abc_2.a = $1))
(8 rows)
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index cfaaf015bb3..2b2161f245c 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -504,9 +504,9 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text)
Update on pg_temp.foo foo_1
-> Result
- Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ Output: '100'::bigint, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
(8 rows)
@@ -530,7 +530,7 @@ DELETE FROM foo WHERE f1 = 5
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4
Delete on pg_temp.foo foo_1
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
(6 rows)
@@ -586,9 +586,9 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3)
Update on pg_temp.foo foo_1
-> Result
- Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ Output: '100'::bigint, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
-> Result
@@ -626,7 +626,7 @@ DELETE FROM foo WHERE f1 = 5
Output: (SubPlan expr_1), (SubPlan expr_2)
Delete on pg_temp.foo foo_1
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
-> Aggregate
@@ -662,9 +662,9 @@ DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4
Update on pg_temp.foo foo_2
-> Nested Loop
- Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid
+ Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.ctid, foo_2.tableoid
-> Seq Scan on pg_temp.foo foo_2
- Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid
+ Output: foo_2.f2, foo_2.f1, foo_2.ctid, foo_2.tableoid
Filter: (foo_2.f1 = 4)
-> Seq Scan on pg_temp.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.tableoid
@@ -687,17 +687,17 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3)
Update on pg_temp.foo foo_1
-> Hash Join
- Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid
+ Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.ctid, foo_1.tableoid, foo_2.tableoid
Hash Cond: (foo_1.f2 = joinme.f2j)
-> Hash Join
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid, joinme_1.ctid, joinme_1.f2j
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid, joinme_1.ctid, joinme_1.f2j
Hash Cond: (joinme_1.f2j = foo_1.f2)
-> Seq Scan on pg_temp.joinme joinme_1
Output: joinme_1.ctid, joinme_1.f2j
-> Hash
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid
-> Hash
Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
-> Hash Join
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 03df7e75b7b..d059e70e0c5 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3248,10 +3248,10 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
Update on public.t12 t1_3
Update on public.t111 t1_4
-> Result
- Output: 100, t1.tableoid, t1.ctid
+ Output: 100, t1.ctid, t1.tableoid
-> Append
-> Index Scan using t1_a_idx on public.t1 t1_1
- Output: t1_1.tableoid, t1_1.ctid
+ Output: t1_1.ctid, t1_1.tableoid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan exists_1
@@ -3261,15 +3261,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1_1.a)
-> Index Scan using t11_a_idx on public.t11 t1_2
- Output: t1_2.tableoid, t1_2.ctid
+ Output: t1_2.ctid, t1_2.tableoid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
- Output: t1_3.tableoid, t1_3.ctid
+ Output: t1_3.ctid, t1_3.tableoid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
- Output: t1_4.tableoid, t1_4.ctid
+ Output: t1_4.ctid, t1_4.tableoid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
@@ -3295,10 +3295,10 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
Update on public.t12 t1_3
Update on public.t111 t1_4
-> Result
- Output: (t1.a + 1), t1.tableoid, t1.ctid
+ Output: (t1.a + 1), t1.ctid, t1.tableoid
-> Append
-> Index Scan using t1_a_idx on public.t1 t1_1
- Output: t1_1.a, t1_1.tableoid, t1_1.ctid
+ Output: t1_1.a, t1_1.ctid, t1_1.tableoid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan exists_1
@@ -3308,15 +3308,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1_1.a)
-> Index Scan using t11_a_idx on public.t11 t1_2
- Output: t1_2.a, t1_2.tableoid, t1_2.ctid
+ Output: t1_2.a, t1_2.ctid, t1_2.tableoid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
- Output: t1_3.a, t1_3.tableoid, t1_3.ctid
+ Output: t1_3.a, t1_3.ctid, t1_3.tableoid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
- Output: t1_4.a, t1_4.tableoid, t1_4.ctid
+ Output: t1_4.a, t1_4.ctid, t1_4.tableoid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index f4caedf272f..b949c95ae58 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3597,21 +3597,21 @@ DELETE FROM a_star USING wcte WHERE aa = q2;
-> Result
Output: '42'::bigint, '47'::bigint
-> Hash Join
- Output: wcte.*, a_star.tableoid, a_star.ctid
+ Output: wcte.*, a_star.ctid, a_star.tableoid
Hash Cond: (a_star.aa = wcte.q2)
-> Append
-> Seq Scan on public.a_star a_star_1
- Output: a_star_1.aa, a_star_1.tableoid, a_star_1.ctid
+ Output: a_star_1.aa, a_star_1.ctid, a_star_1.tableoid
-> Seq Scan on public.b_star a_star_2
- Output: a_star_2.aa, a_star_2.tableoid, a_star_2.ctid
+ Output: a_star_2.aa, a_star_2.ctid, a_star_2.tableoid
-> Seq Scan on public.c_star a_star_3
- Output: a_star_3.aa, a_star_3.tableoid, a_star_3.ctid
+ Output: a_star_3.aa, a_star_3.ctid, a_star_3.tableoid
-> Seq Scan on public.d_star a_star_4
- Output: a_star_4.aa, a_star_4.tableoid, a_star_4.ctid
+ Output: a_star_4.aa, a_star_4.ctid, a_star_4.tableoid
-> Seq Scan on public.e_star a_star_5
- Output: a_star_5.aa, a_star_5.tableoid, a_star_5.ctid
+ Output: a_star_5.aa, a_star_5.ctid, a_star_5.tableoid
-> Seq Scan on public.f_star a_star_6
- Output: a_star_6.aa, a_star_6.tableoid, a_star_6.ctid
+ Output: a_star_6.aa, a_star_6.ctid, a_star_6.tableoid
-> Hash
Output: wcte.*, wcte.q2
-> CTE Scan on wcte
--
2.34.1
Attachments:
[text/plain] v2-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch (43.1K, 3-v2-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch)
download | inline diff:
From 1033e207b10b3d3dbb38c3eff49261ecbdffd16e Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Sun, 30 Nov 2025 13:47:23 +0800
Subject: [PATCH v2] Fix row-identity handling for dummy partitioned
resultrels.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Stop adding tableoid for child relations that do not have any
row-identity columns. In cases where all partitions are excluded
by pruning or constraint exclusion, this allows
distribute_row_identity_vars() to detect the empty state
(root->row_identity_vars == NIL) and add the appropriate ctid
column for the dummy partitioned result relation, satisfying the
executor’s requirement that a resultrel always have a row identity.
As part of this, make add_row_identity_columns() return a boolean
to report whether any row-identity columns were added, and skip FDW
children that cannot support the current command. Adjust expected
EXPLAIN output accordingly and extend file_fdw tests to cover
dummy-root plans with and without pruning.
---
contrib/file_fdw/expected/file_fdw.out | 75 ++++++++++++++++
contrib/file_fdw/sql/file_fdw.sql | 34 ++++++++
.../postgres_fdw/expected/postgres_fdw.out | 86 +++++++++----------
src/backend/optimizer/prep/preptlist.c | 4 +-
src/backend/optimizer/util/appendinfo.c | 18 +++-
src/backend/optimizer/util/inherit.c | 6 +-
src/include/optimizer/appendinfo.h | 2 +-
src/test/regress/expected/inherit.out | 14 +--
src/test/regress/expected/merge.out | 6 +-
src/test/regress/expected/partition_prune.out | 4 +-
src/test/regress/expected/returning.out | 24 +++---
src/test/regress/expected/updatable_views.out | 20 ++---
src/test/regress/expected/with.out | 14 +--
13 files changed, 214 insertions(+), 93 deletions(-)
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 5121e27dce5..e60177af8c8 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -457,6 +457,81 @@ SELECT tableoid::regclass, * FROM p2;
p2 | 2 | xyzzy
(3 rows)
+-- Verify that a dummy root partitioned-table result relation works without
+-- error when all child partitions are excluded from the plan (for example,
+-- by constraint exclusion or pruning). In this case, the executor accepts
+-- a missing ctid for the root result relation since no rows can be produced.
+-- When a foreign-table child is processed before exclusion, a tableoid junk
+-- column may still appear in the targetlist and also wholerow for update.
+-- Dummy-root cases where all children are excluded.
+-- With pruning off, the foreign child is processed first, then excluded
+-- by constraint exclusion. EXPLAIN shows tableoid (rewritten to NULL),
+-- and for UPDATE also wholerow as NULL::record. No ctid.
+DROP TABLE p2;
+SET enable_partition_pruning TO off;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+ QUERY PLAN
+--------------------------------
+ Delete on public.pt
+ -> Result
+ Output: pt.ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+ QUERY PLAN
+------------------------------------
+ Update on public.pt
+ -> Result
+ Output: 'x'::text, pt.ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- MERGE behaves the same here; expect NULL::oid
+EXPLAIN (COSTS OFF, VERBOSE) MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b)
+ ON false WHEN MATCHED THEN UPDATE SET b = s.b;
+ QUERY PLAN
+--------------------------------
+ Merge on public.pt t
+ -> Result
+ Output: t.ctid
+ Replaces: Scan on t
+ One-Time Filter: false
+(5 rows)
+
+-- With pruning on, the foreign child is pruned entirely. The plan has only
+-- the dummy root, and EXPLAIN shows ctid (and for UPDATE, ctid plus target).
+SET enable_partition_pruning TO on;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+ QUERY PLAN
+--------------------------------
+ Delete on public.pt
+ -> Result
+ Output: ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+ QUERY PLAN
+---------------------------------
+ Update on public.pt
+ -> Result
+ Output: 'x'::text, ctid
+ Replaces: Scan on pt
+ One-Time Filter: false
+(5 rows)
+
+-- Foreign child not pruned and it does not support DELETE: error.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE a = 1;
+ERROR: cannot delete from foreign table "p1"
+-- Runtime pruning includes the foreign child in the plan; executor errors
+-- since the foreign child does not support the command.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE (SELECT false);
+ERROR: cannot delete from foreign table "p1"
DROP TABLE pt;
-- generated column tests
\set filename :abs_srcdir '/data/list1.csv'
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 1a397ad4bd1..25658b1f2dc 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -242,6 +242,40 @@ UPDATE pt set a = 1 where a = 2; -- ERROR
SELECT tableoid::regclass, * FROM pt;
SELECT tableoid::regclass, * FROM p1;
SELECT tableoid::regclass, * FROM p2;
+
+-- Verify that a dummy root partitioned-table result relation works without
+-- error when all child partitions are excluded from the plan (for example,
+-- by constraint exclusion or pruning). In this case, the executor accepts
+-- a missing ctid for the root result relation since no rows can be produced.
+-- When a foreign-table child is processed before exclusion, a tableoid junk
+-- column may still appear in the targetlist and also wholerow for update.
+
+-- Dummy-root cases where all children are excluded.
+-- With pruning off, the foreign child is processed first, then excluded
+-- by constraint exclusion. EXPLAIN shows tableoid (rewritten to NULL),
+-- and for UPDATE also wholerow as NULL::record. No ctid.
+DROP TABLE p2;
+SET enable_partition_pruning TO off;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+-- MERGE behaves the same here; expect NULL::oid
+EXPLAIN (COSTS OFF, VERBOSE) MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b)
+ ON false WHEN MATCHED THEN UPDATE SET b = s.b;
+
+-- With pruning on, the foreign child is pruned entirely. The plan has only
+-- the dummy root, and EXPLAIN shows ctid (and for UPDATE, ctid plus target).
+SET enable_partition_pruning TO on;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+EXPLAIN (COSTS OFF, VERBOSE) UPDATE pt SET b = 'x' WHERE false;
+
+-- Foreign child not pruned and it does not support DELETE: error.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE a = 1;
+
+-- Runtime pruning includes the foreign child in the plan; executor errors
+-- since the foreign child does not support the command.
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE (SELECT false);
+
DROP TABLE pt;
-- generated column tests
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48e3185b227..7a20ee06027 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7399,7 +7399,7 @@ UPDATE rw_view SET b = b + 5;
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.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 5), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
@@ -7414,7 +7414,7 @@ UPDATE rw_view SET b = b + 15;
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.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 15), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
@@ -7470,7 +7470,7 @@ UPDATE rw_view SET b = 'text', c = 123.456;
Foreign Update on public.child_foreign parent_tbl_1
Remote SQL: UPDATE public.child_local SET b = $2, c = $3 WHERE ctid = $1 RETURNING a
-> Foreign Scan on public.child_foreign parent_tbl_1
- Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: 'text'::text, 123.456, parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT b, c, a, ctid FROM public.child_local WHERE ((a < 5)) FOR UPDATE
(6 rows)
@@ -8268,7 +8268,7 @@ UPDATE parent_tbl SET b = b + 1;
Foreign Update on public.foreign_tbl parent_tbl_1
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
-> Foreign Scan on public.foreign_tbl parent_tbl_1
- Output: (parent_tbl_1.b + 1), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
+ Output: (parent_tbl_1.b + 1), parent_tbl_1.ctid, parent_tbl_1.*, parent_tbl_1.tableoid
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
(6 rows)
@@ -8282,7 +8282,7 @@ DELETE FROM parent_tbl;
Foreign Delete on public.foreign_tbl parent_tbl_1
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
-> Foreign Scan on public.foreign_tbl parent_tbl_1
- Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
+ Output: parent_tbl_1.ctid, parent_tbl_1.tableoid
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
(6 rows)
@@ -8308,12 +8308,12 @@ UPDATE parent_tbl SET b = b + 1;
Foreign Update on public.foreign_tbl parent_tbl_2
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
-> Result
- Output: (parent_tbl.b + 1), parent_tbl.tableoid, parent_tbl.ctid, (NULL::record)
+ Output: (parent_tbl.b + 1), parent_tbl.ctid, parent_tbl.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.parent_tbl parent_tbl_1
- Output: parent_tbl_1.b, parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::record
+ Output: parent_tbl_1.b, parent_tbl_1.ctid, parent_tbl_1.tableoid, NULL::record
-> Foreign Scan on public.foreign_tbl parent_tbl_2
- Output: parent_tbl_2.b, parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.*
+ Output: parent_tbl_2.b, parent_tbl_2.ctid, parent_tbl_2.tableoid, parent_tbl_2.*
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
(12 rows)
@@ -8329,9 +8329,9 @@ DELETE FROM parent_tbl;
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
-> Append
-> Seq Scan on public.parent_tbl parent_tbl_1
- Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
+ Output: parent_tbl_1.ctid, parent_tbl_1.tableoid
-> Foreign Scan on public.foreign_tbl parent_tbl_2
- Output: parent_tbl_2.tableoid, parent_tbl_2.ctid
+ Output: parent_tbl_2.ctid, parent_tbl_2.tableoid
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
(10 rows)
@@ -8682,14 +8682,14 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Hash Join
- Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, (NULL::record), foo.*, foo.tableoid
+ Output: (bar.f2 + 100), foo.ctid, bar.ctid, bar.tableoid, (NULL::record), foo.*, foo.tableoid
Inner Unique: true
Hash Cond: (bar.f1 = foo.f1)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.f1, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.f1, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
@@ -8729,16 +8729,16 @@ where bar.f1 = ss.f1;
Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
-> Merge Join
- Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), (ROW(foo.f1)), bar.ctid, bar.tableoid, (NULL::record)
Merge Cond: (bar.f1 = foo.f1)
-> Sort
- Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record)
+ Output: bar.f2, bar.f1, bar.ctid, bar.tableoid, (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
+ Output: bar_1.f2, bar_1.f1, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.f1, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
@@ -8888,7 +8888,7 @@ delete from foo where f1 < 5 returning *;
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
+ Output: foo_1.ctid, foo_1.tableoid
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
@@ -8913,10 +8913,10 @@ update bar set f2 = f2 + 100 returning *;
Update on public.bar bar_1
Foreign Update on public.bar2 bar_2
-> Result
- Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), bar.ctid, bar.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Update on public.bar2 bar_2
Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2
(11 rows)
@@ -8948,12 +8948,12 @@ update bar set f2 = f2 + 100;
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
-> Result
- Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record)
+ Output: (bar.f2 + 100), bar.ctid, bar.tableoid, (NULL::record)
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.f2, bar_1.ctid, bar_1.tableoid, NULL::record
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.f2, bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
(12 rows)
@@ -8980,10 +8980,10 @@ delete from bar where f2 < 400;
Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3
-> Append
-> Seq Scan on public.bar bar_1
- Output: bar_1.tableoid, bar_1.ctid, NULL::record
+ Output: bar_1.ctid, bar_1.tableoid, NULL::record
Filter: (bar_1.f2 < 400)
-> Foreign Scan on public.bar2 bar_2
- Output: bar_2.tableoid, bar_2.ctid, bar_2.*
+ Output: bar_2.ctid, bar_2.tableoid, bar_2.*
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(11 rows)
@@ -9024,13 +9024,13 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re
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.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, (NULL::record)
+ Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.ctid, parent.tableoid, (NULL::record)
Join Filter: (parent.a = remt2.a)
-> Append
-> Seq Scan on public.parent parent_1
- Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::record
+ Output: parent_1.b, parent_1.a, parent_1.ctid, parent_1.tableoid, NULL::record
-> Foreign Scan on public.remt1 parent_2
- Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.*
+ Output: parent_2.b, parent_2.a, parent_2.ctid, parent_2.tableoid, parent_2.*
Remote SQL: SELECT a, b, ctid FROM public.loct1 FOR UPDATE
-> Materialize
Output: remt2.b, remt2.*, remt2.a
@@ -9056,13 +9056,13 @@ delete from parent using remt2 where parent.a = remt2.a returning parent;
Foreign Delete on public.remt1 parent_2
Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 RETURNING a, b
-> Nested Loop
- Output: remt2.*, parent.tableoid, parent.ctid
+ Output: remt2.*, parent.ctid, parent.tableoid
Join Filter: (parent.a = remt2.a)
-> Append
-> Seq Scan on public.parent parent_1
- Output: parent_1.a, parent_1.tableoid, parent_1.ctid
+ Output: parent_1.a, parent_1.ctid, parent_1.tableoid
-> Foreign Scan on public.remt1 parent_2
- Output: parent_2.a, parent_2.tableoid, parent_2.ctid
+ Output: parent_2.a, parent_2.ctid, parent_2.tableoid
Remote SQL: SELECT a, ctid FROM public.loct1 FOR UPDATE
-> Materialize
Output: remt2.*, remt2.a
@@ -9293,7 +9293,7 @@ update utrtest set a = 1 where a = 1 or a = 2 returning *;
-> 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
+ Output: 1, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
(10 rows)
@@ -9311,7 +9311,7 @@ update utrtest set a = 1 where a = 2 returning *;
Output: utrtest_1.a, utrtest_1.b
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
- Output: 1, utrtest_1.tableoid, utrtest_1.ctid
+ Output: 1, utrtest_1.ctid, utrtest_1.tableoid
Filter: (utrtest_1.a = 2)
(6 rows)
@@ -9342,7 +9342,7 @@ update utrtest set a = 1 returning *;
-> 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
+ Output: 1, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
(9 rows)
update utrtest set a = 1 returning *;
@@ -9361,14 +9361,14 @@ update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b
Update on public.locp utrtest_2
-> Hash Join
- Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.*
+ Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.ctid, utrtest.*, utrtest.tableoid
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.*
+ Output: utrtest_1.a, utrtest_1.ctid, utrtest_1.*, utrtest_1.tableoid
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
+ Output: utrtest_2.a, utrtest_2.ctid, NULL::record, utrtest_2.tableoid
-> Hash
Output: "*VALUES*".*, "*VALUES*".column1
-> Values Scan on "*VALUES*"
@@ -9400,7 +9400,7 @@ update utrtest set a = 3 returning *;
Foreign Update on public.remp utrtest_2
-> Append
-> Seq Scan on public.locp utrtest_1
- Output: 3, utrtest_1.tableoid, utrtest_1.ctid, NULL::record
+ Output: 3, utrtest_1.ctid, utrtest_1.tableoid, NULL::record
-> Foreign Update on public.remp utrtest_2
Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b
(9 rows)
@@ -9418,13 +9418,13 @@ update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
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, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record)
+ Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.ctid, utrtest.tableoid, (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
+ Output: utrtest_1.a, utrtest_1.ctid, utrtest_1.tableoid, NULL::record
-> Foreign Scan on public.remp utrtest_2
- Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.*
+ Output: utrtest_2.a, utrtest_2.ctid, utrtest_2.tableoid, utrtest_2.*
Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE
-> Hash
Output: "*VALUES*".*, "*VALUES*".column1
@@ -12325,7 +12325,7 @@ UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
-> Foreign Update on public.async_p2 async_pt_2
Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
-> Seq Scan on public.async_p3 async_pt_3
- Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record
+ Output: (async_pt_3.c || async_pt_3.c), async_pt_3.ctid, NULL::record, async_pt_3.tableoid
Filter: (async_pt_3.b = 0)
(13 rows)
@@ -12352,7 +12352,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *;
-> Foreign Delete on public.async_p2 async_pt_2
Remote SQL: DELETE FROM public.base_tbl2 WHERE ((b = 0)) RETURNING a, b, c
-> Seq Scan on public.async_p3 async_pt_3
- Output: async_pt_3.tableoid, async_pt_3.ctid
+ Output: async_pt_3.ctid, async_pt_3.tableoid
Filter: (async_pt_3.b = 0)
(13 rows)
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index ffc9d6c3f30..26090d71dfd 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -122,8 +122,8 @@ preprocess_targetlist(PlannerInfo *root)
{
/* row-identity logic expects to add stuff to processed_tlist */
root->processed_tlist = tlist;
- add_row_identity_columns(root, result_relation,
- target_rte, target_relation);
+ (void) add_row_identity_columns(root, result_relation,
+ target_rte, target_relation);
tlist = root->processed_tlist;
}
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
index 69b8b0c2ae0..f977dfda208 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -951,7 +951,7 @@ add_row_identity_var(PlannerInfo *root, Var *orig_var,
* FDWs might call add_row_identity_var() for themselves to add nonstandard
* columns. (Duplicate requests are fine.)
*/
-void
+bool
add_row_identity_columns(PlannerInfo *root, Index rtindex,
RangeTblEntry *target_rte,
Relation target_relation)
@@ -977,6 +977,7 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
InvalidOid,
0);
add_row_identity_var(root, var, rtindex, "ctid");
+ return true;
}
else if (relkind == RELKIND_FOREIGN_TABLE)
{
@@ -987,6 +988,13 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
fdwroutine = GetFdwRoutineForRelation(target_relation, false);
+ if (commandType == CMD_MERGE ||
+ (commandType == CMD_UPDATE &&
+ fdwroutine->ExecForeignUpdate == NULL) ||
+ (commandType == CMD_DELETE &&
+ fdwroutine->ExecForeignDelete == NULL))
+ return false;
+
if (fdwroutine->AddForeignUpdateTargets != NULL)
fdwroutine->AddForeignUpdateTargets(root, rtindex,
target_rte, target_relation);
@@ -1017,7 +1025,11 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
0);
add_row_identity_var(root, var, rtindex, "wholerow");
}
+
+ return true;
}
+
+ return false;
}
/*
@@ -1075,8 +1087,8 @@ distribute_row_identity_vars(PlannerInfo *root)
Relation target_relation;
target_relation = table_open(target_rte->relid, NoLock);
- add_row_identity_columns(root, result_relation,
- target_rte, target_relation);
+ (void) add_row_identity_columns(root, result_relation,
+ target_rte, target_relation);
table_close(target_relation, NoLock);
build_base_rel_tlists(root, root->processed_tlist);
/* There are no ROWID_VAR Vars in this case, so we're done. */
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index 6d5225079f8..96c24a8a552 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -634,11 +634,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
-1,
InvalidOid,
0);
- add_row_identity_var(root, rrvar, childRTindex, "tableoid");
/* Register any row-identity columns needed by this child. */
- add_row_identity_columns(root, childRTindex,
- childrte, childrel);
+ if (add_row_identity_columns(root, childRTindex,
+ childrte, childrel))
+ add_row_identity_var(root, rrvar, childRTindex, "tableoid");
}
}
}
diff --git a/src/include/optimizer/appendinfo.h b/src/include/optimizer/appendinfo.h
index d06f93b7266..5f3168d612f 100644
--- a/src/include/optimizer/appendinfo.h
+++ b/src/include/optimizer/appendinfo.h
@@ -42,7 +42,7 @@ extern AppendRelInfo **find_appinfos_by_relids(PlannerInfo *root,
Relids relids, int *nappinfos);
extern void add_row_identity_var(PlannerInfo *root, Var *orig_var,
Index rtindex, const char *rowid_name);
-extern void add_row_identity_columns(PlannerInfo *root, Index rtindex,
+extern bool add_row_identity_columns(PlannerInfo *root, Index rtindex,
RangeTblEntry *target_rte,
Relation target_relation);
extern void distribute_row_identity_vars(PlannerInfo *root);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..e8fcae6514f 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -579,7 +579,7 @@ update some_tab set a = a + 1 where false;
--------------------------------------------------------
Update on public.some_tab
-> Result
- Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Output: (some_tab.a + 1), NULL::tid, NULL::oid
Replaces: Scan on some_tab
One-Time Filter: false
(5 rows)
@@ -592,7 +592,7 @@ update some_tab set a = a + 1 where false returning b, a;
Update on public.some_tab
Output: some_tab.b, some_tab.a
-> Result
- Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Output: (some_tab.a + 1), NULL::tid, NULL::oid
Replaces: Scan on some_tab
One-Time Filter: false
(6 rows)
@@ -2054,12 +2054,12 @@ update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
Update on public.inhpar i_1
Update on public.inhcld i_2
-> Result
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i.tableoid, i.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i.ctid, i.tableoid
-> Append
-> Seq Scan on public.inhpar i_1
- Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid
+ Output: i_1.f1, i_1.f2, i_1.ctid, i_1.tableoid
-> Seq Scan on public.inhcld i_2
- Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid
+ Output: i_2.f1, i_2.f2, i_2.ctid, i_2.tableoid
SubPlan multiexpr_1
-> Limit
Output: (i.f1), (((i.f2)::text || '-'::text))
@@ -2103,14 +2103,14 @@ update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
Update on public.inhcld2 i_2
-> Append
-> Seq Scan on public.inhcld1 i_1
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.tableoid, i_1.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.ctid, i_1.tableoid
SubPlan multiexpr_1
-> Limit
Output: (i_1.f1), (((i_1.f2)::text || '-'::text))
-> Seq Scan on public.int4_tbl
Output: i_1.f1, ((i_1.f2)::text || '-'::text)
-> Seq Scan on public.inhcld2 i_2
- Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.tableoid, i_2.ctid
+ Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.ctid, i_2.tableoid
(13 rows)
update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 9cb1d87066a..10b27b01532 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -2387,15 +2387,15 @@ MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
Merge on public.pa_target t
Merge on public.pa_targetp t_1
-> Hash Left Join
- Output: s.sid, s.ctid, t_1.tableoid, t_1.ctid
+ Output: s.sid, s.ctid, t_1.ctid, t_1.tableoid
Inner Unique: true
Hash Cond: (s.sid = t_1.tid)
-> Seq Scan on public.pa_source s
Output: s.sid, s.ctid
-> Hash
- Output: t_1.tid, t_1.tableoid, t_1.ctid
+ Output: t_1.tid, t_1.ctid, t_1.tableoid
-> Seq Scan on public.pa_targetp t_1
- Output: t_1.tid, t_1.tableoid, t_1.ctid
+ Output: t_1.tid, t_1.ctid, t_1.tableoid
(12 rows)
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index deacdd75807..24c6ac408f3 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4585,7 +4585,7 @@ explain (verbose, costs off) execute update_part_abc_view (1, 'd');
-> Append
Subplans Removed: 1
-> Seq Scan on public.part_abc_1
- Output: $2, part_abc_1.tableoid, part_abc_1.ctid
+ Output: $2, part_abc_1.ctid, part_abc_1.tableoid
Filter: ((part_abc_1.b <> 'a'::text) AND (part_abc_1.a = $1))
(8 rows)
@@ -4604,7 +4604,7 @@ explain (verbose, costs off) execute update_part_abc_view (2, 'a');
-> Append
Subplans Removed: 1
-> Seq Scan on public.part_abc_2
- Output: $2, part_abc_2.tableoid, part_abc_2.ctid
+ Output: $2, part_abc_2.ctid, part_abc_2.tableoid
Filter: ((part_abc_2.b <> 'a'::text) AND (part_abc_2.a = $1))
(8 rows)
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index cfaaf015bb3..2b2161f245c 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -504,9 +504,9 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text)
Update on pg_temp.foo foo_1
-> Result
- Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ Output: '100'::bigint, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
(8 rows)
@@ -530,7 +530,7 @@ DELETE FROM foo WHERE f1 = 5
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4
Delete on pg_temp.foo foo_1
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
(6 rows)
@@ -586,9 +586,9 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3)
Update on pg_temp.foo foo_1
-> Result
- Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ Output: '100'::bigint, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
-> Result
@@ -626,7 +626,7 @@ DELETE FROM foo WHERE f1 = 5
Output: (SubPlan expr_1), (SubPlan expr_2)
Delete on pg_temp.foo foo_1
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.tableoid, foo_1.ctid
+ Output: foo_1.ctid, foo_1.tableoid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
-> Aggregate
@@ -662,9 +662,9 @@ DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4
Update on pg_temp.foo foo_2
-> Nested Loop
- Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid
+ Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.ctid, foo_2.tableoid
-> Seq Scan on pg_temp.foo foo_2
- Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid
+ Output: foo_2.f2, foo_2.f1, foo_2.ctid, foo_2.tableoid
Filter: (foo_2.f1 = 4)
-> Seq Scan on pg_temp.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.tableoid
@@ -687,17 +687,17 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3)
Update on pg_temp.foo foo_1
-> Hash Join
- Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid
+ Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.ctid, foo_1.tableoid, foo_2.tableoid
Hash Cond: (foo_1.f2 = joinme.f2j)
-> Hash Join
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid, joinme_1.ctid, joinme_1.f2j
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid, joinme_1.ctid, joinme_1.f2j
Hash Cond: (joinme_1.f2j = foo_1.f2)
-> Seq Scan on pg_temp.joinme joinme_1
Output: joinme_1.ctid, joinme_1.f2j
-> Hash
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid
-> Seq Scan on pg_temp.foo foo_1
- Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ Output: foo_1.f2, foo_1.ctid, foo_1.tableoid
-> Hash
Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
-> Hash Join
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 03df7e75b7b..d059e70e0c5 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3248,10 +3248,10 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
Update on public.t12 t1_3
Update on public.t111 t1_4
-> Result
- Output: 100, t1.tableoid, t1.ctid
+ Output: 100, t1.ctid, t1.tableoid
-> Append
-> Index Scan using t1_a_idx on public.t1 t1_1
- Output: t1_1.tableoid, t1_1.ctid
+ Output: t1_1.ctid, t1_1.tableoid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan exists_1
@@ -3261,15 +3261,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1_1.a)
-> Index Scan using t11_a_idx on public.t11 t1_2
- Output: t1_2.tableoid, t1_2.ctid
+ Output: t1_2.ctid, t1_2.tableoid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
- Output: t1_3.tableoid, t1_3.ctid
+ Output: t1_3.ctid, t1_3.tableoid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
- Output: t1_4.tableoid, t1_4.ctid
+ Output: t1_4.ctid, t1_4.tableoid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
@@ -3295,10 +3295,10 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
Update on public.t12 t1_3
Update on public.t111 t1_4
-> Result
- Output: (t1.a + 1), t1.tableoid, t1.ctid
+ Output: (t1.a + 1), t1.ctid, t1.tableoid
-> Append
-> Index Scan using t1_a_idx on public.t1 t1_1
- Output: t1_1.a, t1_1.tableoid, t1_1.ctid
+ Output: t1_1.a, t1_1.ctid, t1_1.tableoid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan exists_1
@@ -3308,15 +3308,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Seq Scan on public.t111 t12_2
Filter: (t12_2.a = t1_1.a)
-> Index Scan using t11_a_idx on public.t11 t1_2
- Output: t1_2.a, t1_2.tableoid, t1_2.ctid
+ Output: t1_2.a, t1_2.ctid, t1_2.tableoid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
- Output: t1_3.a, t1_3.tableoid, t1_3.ctid
+ Output: t1_3.a, t1_3.ctid, t1_3.tableoid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
- Output: t1_4.a, t1_4.tableoid, t1_4.ctid
+ Output: t1_4.a, t1_4.ctid, t1_4.tableoid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index f4caedf272f..b949c95ae58 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3597,21 +3597,21 @@ DELETE FROM a_star USING wcte WHERE aa = q2;
-> Result
Output: '42'::bigint, '47'::bigint
-> Hash Join
- Output: wcte.*, a_star.tableoid, a_star.ctid
+ Output: wcte.*, a_star.ctid, a_star.tableoid
Hash Cond: (a_star.aa = wcte.q2)
-> Append
-> Seq Scan on public.a_star a_star_1
- Output: a_star_1.aa, a_star_1.tableoid, a_star_1.ctid
+ Output: a_star_1.aa, a_star_1.ctid, a_star_1.tableoid
-> Seq Scan on public.b_star a_star_2
- Output: a_star_2.aa, a_star_2.tableoid, a_star_2.ctid
+ Output: a_star_2.aa, a_star_2.ctid, a_star_2.tableoid
-> Seq Scan on public.c_star a_star_3
- Output: a_star_3.aa, a_star_3.tableoid, a_star_3.ctid
+ Output: a_star_3.aa, a_star_3.ctid, a_star_3.tableoid
-> Seq Scan on public.d_star a_star_4
- Output: a_star_4.aa, a_star_4.tableoid, a_star_4.ctid
+ Output: a_star_4.aa, a_star_4.ctid, a_star_4.tableoid
-> Seq Scan on public.e_star a_star_5
- Output: a_star_5.aa, a_star_5.tableoid, a_star_5.ctid
+ Output: a_star_5.aa, a_star_5.ctid, a_star_5.tableoid
-> Seq Scan on public.f_star a_star_6
- Output: a_star_6.aa, a_star_6.tableoid, a_star_6.ctid
+ Output: a_star_6.aa, a_star_6.ctid, a_star_6.tableoid
-> Hash
Output: wcte.*, wcte.q2
-> CTE Scan on wcte
--
2.34.1
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error
In-Reply-To: <CAHewXNnD0744Vykj6ujE5c=rRP=61sh7K154uNdgEaTmJrRegQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox