public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Langote <[email protected]>
To: Tender Wang <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: jian he <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: PostgreSQL mailing lists <[email protected]>
Subject: Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error
Date: Wed, 14 Jan 2026 21:30:37 +0900
Message-ID: <CA+HiwqEEX8DsHOtcD4JkWcj4QsyY-BjVhRfc69pUq+7Czqn58w@mail.gmail.com> (raw)
In-Reply-To: <CAHewXNnD0744Vykj6ujE5c=rRP=61sh7K154uNdgEaTmJrRegQ@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>
<CAHewXNnD0744Vykj6ujE5c=rRP=61sh7K154uNdgEaTmJrRegQ@mail.gmail.com>
Hi,
On Sun, Nov 30, 2025 at 3:00 PM Tender Wang <[email protected]> wrote:
> 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.
Oops.
> 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 for taking a look.
Attached is an updated version with improved comments and simplified test cases.
Regarding back-patch safety (to v14 where the bug was introduced):
* EXPLAIN VERBOSE output order changes (ctid now appears before tableoid)
* AddForeignUpdateTargets is no longer called when the FDW doesn't
support the command
Does anyone see issues with back-patching these changes?
--
Thanks, Amit Langote
Attachments:
[application/octet-stream] v3-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch (41.0K, 2-v3-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch)
download | inline diff:
From b9386b4df596cfaf5ba6ab5c88b3095698be33a3 Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Wed, 14 Jan 2026 21:27:48 +0900
Subject: [PATCH v3] Fix row-identity handling for dummy partitioned resultrels
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.
This changes the order of ctid and tableoid columns in EXPLAIN
VERBOSE output for UPDATE/DELETE/MERGE on inheritance trees;
ctid now appears before tableoid.
Additionally, AddForeignUpdateTargets is no longer called for
foreign table children whose FDW does not support the current
command (e.g., missing ExecForeignDelete for DELETE). FDWs
that rely on this callback being invoked unconditionally may
need adjustment.
Back-patch to v14 where the bug was introduced.
Bug: #19099
Reported-by: Alexander Lakhin <[email protected]>
Author: Amit Langote <[email protected]>
Reviewed-by: Tender Wang <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Discussion: https://postgr.es/m/19099-e05dcfa022fe553d%40postgresql.org
Backpatch-through: 14
---
contrib/file_fdw/expected/file_fdw.out | 26 ++++++
contrib/file_fdw/sql/file_fdw.sql | 10 +++
.../postgres_fdw/expected/postgres_fdw.out | 86 +++++++++----------
src/backend/optimizer/prep/preptlist.c | 4 +-
src/backend/optimizer/util/appendinfo.c | 28 +++++-
src/backend/optimizer/util/inherit.c | 11 ++-
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, 155 insertions(+), 94 deletions(-)
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 5121e27dce5..e26f786403c 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -457,6 +457,32 @@ SELECT tableoid::regclass, * FROM p2;
p2 | 2 | xyzzy
(3 rows)
+-- Verify that DELETE/UPDATE on a partitioned table with a foreign partition
+-- that doesn't support the operation works when all partitions are excluded
+-- (by pruning or constraint exclusion). The dummy root should get ctid added.
+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)
+
+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)
+
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..e8a8c8430af 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -242,6 +242,16 @@ 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 DELETE/UPDATE on a partitioned table with a foreign partition
+-- that doesn't support the operation works when all partitions are excluded
+-- (by pruning or constraint exclusion). The dummy root should get ctid added.
+DROP TABLE p2;
+SET enable_partition_pruning TO off;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE false;
+SET enable_partition_pruning TO on;
+EXPLAIN (COSTS OFF, VERBOSE) DELETE FROM pt WHERE 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 6066510c7c0..04da4c171eb 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 ff9c7c4fb96..b32ae387560 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 689840d6564..043aa8373a4 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -949,8 +949,12 @@ add_row_identity_var(PlannerInfo *root, Var *orig_var,
* This function adds the row identity columns needed by the core code.
* FDWs might call add_row_identity_var() for themselves to add nonstandard
* columns. (Duplicate requests are fine.)
+ *
+ * Returns true if any row-identity columns were added, false if not.
+ * For foreign tables whose FDW does not support the current command,
+ * does nothing and returns false.
*/
-void
+bool
add_row_identity_columns(PlannerInfo *root, Index rtindex,
RangeTblEntry *target_rte,
Relation target_relation)
@@ -976,6 +980,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)
{
@@ -986,6 +991,19 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
fdwroutine = GetFdwRoutineForRelation(target_relation, false);
+ /*
+ * If the FDW doesn't support the current command, skip adding
+ * row-identity columns. This allows distribute_row_identity_vars()
+ * to detect when all children lack row identity and add ctid for
+ * the dummy result relation.
+ */
+ 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);
@@ -1016,7 +1034,11 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex,
0);
add_row_identity_var(root, var, rtindex, "wholerow");
}
+
+ return true;
}
+
+ return false;
}
/*
@@ -1074,8 +1096,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 48b5d0aac4c..56cecc2d5c0 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -634,11 +634,14 @@ 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);
+ /*
+ * Register any row-identity columns needed by this child.
+ * Add tableoid only if row-identity columns were added.
+ */
+ 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 b59a6218853..5fa8aad6df8 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 77ded01b046..417514ffc31 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3638,21 +3638,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.47.3
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: <CA+HiwqEEX8DsHOtcD4JkWcj4QsyY-BjVhRfc69pUq+7Czqn58w@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