public inbox for [email protected]help / color / mirror / Atom feed
Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error 22+ messages / 5 participants [nested] [flat]
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-10-30 13:48 Tom Lane <[email protected]> 0 siblings, 2 replies; 22+ messages in thread From: Tom Lane @ 2025-10-30 13:48 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: Tender Wang <[email protected]>; Amit Langote <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] Kirill Reshke <[email protected]> writes: > Tom wrote: >> It's surely pretty accidental (and arguably not desirable) >> if "DELETE FROM pt WHERE false" doesn't fail the same way. > I cannot prove to myself why failing here is actually desirable. Can > you elaborate? If we throw that failure in some cases but not others, we're exposing implementation details. The definition could have been "throw 'cannot delete from foreign table' only if the query actually attempts to delete some specific row from some foreign table", but it is not implemented that way. Instead the error is thrown during query startup if the query has a foreign table as a potential delete target. Thus, as things stand today, you might or might not get the error depending on whether the planner can prove that that partition won't be deleted from. This is not a great user experience, because we don't (and won't) make any hard promises about how smart the planner is. An analogy perhaps is that whether you get a "permission denied" error about some target table is not conditional on whether the query actually attempts to delete any rows from it. We go out of our way to make sure that that happens when required by spec, even if the planner is able to prove that no delete will happen. None of this is meant to justify throwing an internal error here; that's clearly bad. I'm just saying that there would be little wrong with fixing it by throwing "cannot delete" instead. The user has no right to expect that that won't happen in a case like this. regards, tom lane ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-10-31 00:30 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 22+ messages in thread From: Amit Langote @ 2025-10-31 00:30 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Thu, Oct 30, 2025 at 10:48 PM Tom Lane <[email protected]> wrote: > Kirill Reshke <[email protected]> writes: > > Tom wrote: > >> It's surely pretty accidental (and arguably not desirable) > >> if "DELETE FROM pt WHERE false" doesn't fail the same way. > > > I cannot prove to myself why failing here is actually desirable. Can > > you elaborate? > > If we throw that failure in some cases but not others, we're exposing > implementation details. > > The definition could have been "throw 'cannot delete from foreign > table' only if the query actually attempts to delete some specific > row from some foreign table", but it is not implemented that way. > Instead the error is thrown during query startup if the query has > a foreign table as a potential delete target. Thus, as things stand > today, you might or might not get the error depending on whether > the planner can prove that that partition won't be deleted from. > This is not a great user experience, because we don't (and won't) > make any hard promises about how smart the planner is. > > An analogy perhaps is that whether you get a "permission denied" > error about some target table is not conditional on whether the > query actually attempts to delete any rows from it. We go out > of our way to make sure that that happens when required by spec, > even if the planner is able to prove that no delete will happen. > > None of this is meant to justify throwing an internal error here; > that's clearly bad. I'm just saying that there would be little > wrong with fixing it by throwing "cannot delete" instead. The user > has no right to expect that that won't happen in a case like this. We might be able to throw the "cannot delete from foreign table" like this: @@ -987,6 +987,16 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex, fdwroutine = GetFdwRoutineForRelation(target_relation, false); + if (fdwroutine->ExecForeignDelete == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot delete from foreign table \"%s\"", + RelationGetRelationName(target_relation)))); + if (fdwroutine->ExecForeignUpdate == NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot update foreign table \"%s\"", + RelationGetRelationName(target_relation)))); if (fdwroutine->AddForeignUpdateTargets != NULL) fdwroutine->AddForeignUpdateTargets(root, rtindex, target_rte, target_relation); but I am not sure how consistent the following is after applying that: postgres=# set enable_partition_pruning to off; SET postgres=# EXPLAIN verbose DELETE FROM pt WHERE false; ERROR: cannot delete from foreign table "p1" postgres=# set enable_partition_pruning to on; SET -- we don't even hit the foreign table in the planner postgres=# EXPLAIN verbose DELETE FROM pt WHERE false; QUERY PLAN ------------------------------------------------------- Delete on public.pt (cost=0.00..0.00 rows=0 width=0) -> Result (cost=0.00..0.00 rows=0 width=0) Output: ctid Replaces: Scan on pt One-Time Filter: false (5 rows) -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-10-31 01:50 David Rowley <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 22+ messages in thread From: David Rowley @ 2025-10-31 01:50 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; Amit Langote <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Fri, 31 Oct 2025 at 02:48, Tom Lane <[email protected]> wrote: > The definition could have been "throw 'cannot delete from foreign > table' only if the query actually attempts to delete some specific > row from some foreign table", but it is not implemented that way. > Instead the error is thrown during query startup if the query has > a foreign table as a potential delete target. Thus, as things stand > today, you might or might not get the error depending on whether > the planner can prove that that partition won't be deleted from. > This is not a great user experience, because we don't (and won't) > make any hard promises about how smart the planner is. It's a good point, but I doubt we could change this fact as I expect there are people relying on pruned partitions being excluded from this check. It seems reasonable that someone might want to do something like archive ancient time-based partitioned table partitions into file_fdw stored on a compressed filesystem so that they can still at least query old data should they need to. If we were to precheck that all partitions support an UPDATE/DELETE, then it could break workloads that do updates on recent data in heap-based partitions. Things would go bad for those people if they switched off partition pruning, but I doubt that would be the only reason as that would also add a huge amount of overhead to their SELECT statements. In any case, the planner is now very efficient at not loading any metadata for pruned partitions, so I don't see how we'd do this without adding possibly large overhead to the planner. I'd say we're well beyond the point of being able to change this now. David ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-06 10:00 Amit Langote <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 3 replies; 22+ messages in thread From: Amit Langote @ 2025-11-06 10:00 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Fri, Oct 31, 2025 at 10:50 AM David Rowley <[email protected]> wrote: > On Fri, 31 Oct 2025 at 02:48, Tom Lane <[email protected]> wrote: > > The definition could have been "throw 'cannot delete from foreign > > table' only if the query actually attempts to delete some specific > > row from some foreign table", but it is not implemented that way. > > Instead the error is thrown during query startup if the query has > > a foreign table as a potential delete target. Thus, as things stand > > today, you might or might not get the error depending on whether > > the planner can prove that that partition won't be deleted from. > > This is not a great user experience, because we don't (and won't) > > make any hard promises about how smart the planner is. > > It's a good point, but I doubt we could change this fact as I expect > there are people relying on pruned partitions being excluded from this > check. It seems reasonable that someone might want to do something > like archive ancient time-based partitioned table partitions into > file_fdw stored on a compressed filesystem so that they can still at > least query old data should they need to. If we were to precheck that > all partitions support an UPDATE/DELETE, then it could break workloads > that do updates on recent data in heap-based partitions. Things would > go bad for those people if they switched off partition pruning, but I > doubt that would be the only reason as that would also add a huge > amount of overhead to their SELECT statements. > > In any case, the planner is now very efficient at not loading any > metadata for pruned partitions, so I don't see how we'd do this > without adding possibly large overhead to the planner. I'd say we're > well beyond the point of being able to change this now. I agree that we definitely shouldn’t load metadata for partitions that are excluded from the plan, especially not just to slightly improve user experience in this corner case. I looked at a few options, but none seem non-invasive enough for back-patching, apart from the first patch I posted. That one makes ExecInitModifyTable() not require a ctid to be present to set the root partitioned table’s ri_RowIdAttNo, except in the case of MERGE, which seems to need it. The corner case that triggers the internal error for UPDATE/DELETE doesn’t occur for MERGE now and likely won’t when foreign tables eventually gain MERGE support; don't mark my words though ;-). 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. So for back branches, I’d propose sticking with the smaller executor-side fix and perhaps revisiting the planner behavior separately if we ever want to refine handling of pruned partitions or dummy roots. I understand, as was reported upthread, that the EXPLAIN VERBOSE output isn’t very consistent with that patch even though the internal error goes away. Making sense of the output differences requires knowing that the targetlist population behavior differs depending on whether enable_partition_pruning is on or off as I described above. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 01:01 Tender Wang <[email protected]> parent: Amit Langote <[email protected]> 2 siblings, 1 reply; 22+ messages in thread From: Tender Wang @ 2025-11-07 01:01 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] Amit Langote <[email protected]> 于2025年11月6日周四 18:00写道： > On Fri, Oct 31, 2025 at 10:50 AM David Rowley <[email protected]> > wrote: > > On Fri, 31 Oct 2025 at 02:48, Tom Lane <[email protected]> wrote: > > > The definition could have been "throw 'cannot delete from foreign > > > table' only if the query actually attempts to delete some specific > > > row from some foreign table", but it is not implemented that way. > > > Instead the error is thrown during query startup if the query has > > > a foreign table as a potential delete target. Thus, as things stand > > > today, you might or might not get the error depending on whether > > > the planner can prove that that partition won't be deleted from. > > > This is not a great user experience, because we don't (and won't) > > > make any hard promises about how smart the planner is. > > > > It's a good point, but I doubt we could change this fact as I expect > > there are people relying on pruned partitions being excluded from this > > check. It seems reasonable that someone might want to do something > > like archive ancient time-based partitioned table partitions into > > file_fdw stored on a compressed filesystem so that they can still at > > least query old data should they need to. If we were to precheck that > > all partitions support an UPDATE/DELETE, then it could break workloads > > that do updates on recent data in heap-based partitions. Things would > > go bad for those people if they switched off partition pruning, but I > > doubt that would be the only reason as that would also add a huge > > amount of overhead to their SELECT statements. > > > > In any case, the planner is now very efficient at not loading any > > metadata for pruned partitions, so I don't see how we'd do this > > without adding possibly large overhead to the planner. I'd say we're > > well beyond the point of being able to change this now. > > I agree that we definitely shouldn’t load metadata for partitions that > are excluded from the plan, especially not just to slightly improve > user experience in this corner case. > > I looked at a few options, but none seem non-invasive enough for > back-patching, apart from the first patch I posted. That one makes > ExecInitModifyTable() not require a ctid to be present to set the root > partitioned table’s ri_RowIdAttNo, except in the case of MERGE, which > seems to need it. The corner case that triggers the internal error for > UPDATE/DELETE doesn’t occur for MERGE now and likely won’t when > foreign tables eventually gain MERGE support; don't mark my words > though ;-). > > 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. Yeah, it seems to need more work if we prevent the planner from adding tableoid in the first place. > So for back branches, I’d propose sticking with the smaller > executor-side fix and perhaps revisiting the planner behavior > separately if we ever want to refine handling of pruned partitions or > dummy roots. I understand, as was reported upthread, that the EXPLAIN > VERBOSE output isn’t very consistent with that patch even though the > internal error goes away. Making sense of the output differences > requires knowing that the targetlist population behavior differs > depending on whether enable_partition_pruning is on or off as I > described above. > The executor-side fix works for me and the test case should be added to your patch. Should we add some comments to explain the output difference in EXPLAIN VERBOSE if enable_partition_pruning is set to a different value? -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 06:02 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 2 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2025-11-07 06:02 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Thu, Nov 6, 2025 at 7:00 PM Amit Langote <[email protected]> wrote: > I looked at a few options, but none seem non-invasive enough for > back-patching, apart from the first patch I posted. That one makes > ExecInitModifyTable() not require a ctid to be present to set the root > partitioned table’s ri_RowIdAttNo, except in the case of MERGE, which > seems to need it. The corner case that triggers the internal error for > UPDATE/DELETE doesn’t occur for MERGE now and likely won’t when > foreign tables eventually gain MERGE support; don't mark my words > though ;-). Well, OK, I just had not tried hard enough to see that the same error happens for MERGE too. With my patch applied: EXPLAIN VERBOSE MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b) ON false WHEN MATCHED THEN UPDATE SET b = s.b; ERROR: could not find junk ctid column I have another idea: we can simply recognize the corner condition that throws this error in ExecInitModifyTable() by checking if ModifyTable.resultRelations contains only the root partitioned table. That can only happen for UPDATE, DELETE, or MERGE when all child relations were excluded. Patch doing that attached. Added test cases to file_fdw's suite. -- Thanks, Amit Langote Attachments: [application/octet-stream] v2-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch (8.5K, 2-v2-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch) download | inline diff: From 5a3ccaeb803b72a043e6eabf824a824021da3917 Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Fri, 7 Nov 2025 14:48:10 +0900 Subject: [PATCH v2] Fix bogus ctid requirement for dummy-root partitioned targets ExecInitModifyTable() unconditionally required a ctid junk column even when the target was a partitioned table. This led to spurious "could not find junk ctid column" errors when all children were excluded and only the dummy root result relation remained. Require ctid for heap relations as before. For partitioned tables, require it only when at least one leaf result relation remains in the plan (nrels > 1). If the plan has only the dummy root, no rows can be produced and ctid is not needed. --- contrib/file_fdw/expected/file_fdw.out | 88 ++++++++++++++++++++++++++ contrib/file_fdw/sql/file_fdw.sql | 38 +++++++++++ src/backend/executor/nodeModifyTable.c | 11 +++- 3 files changed, 136 insertions(+), 1 deletion(-) diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index 5121e27dce5..1823ca02106 100644 --- a/contrib/file_fdw/expected/file_fdw.out +++ b/contrib/file_fdw/expected/file_fdw.out @@ -457,6 +457,94 @@ 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 VERBOSE DELETE FROM pt WHERE false; + QUERY PLAN +------------------------------------------------------- + Delete on public.pt (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=0) + Output: NULL::oid + Replaces: Scan on pt + One-Time Filter: false +(5 rows) + +-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; + QUERY PLAN +------------------------------------------------------- + Update on public.pt (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=68) + Output: 'x'::text, NULL::oid, NULL::record + Replaces: Scan on pt + One-Time Filter: false +(5 rows) + +-- MERGE behaves the same here; expect NULL::oid +EXPLAIN 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 (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=0) + Output: NULL::oid + 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 VERBOSE DELETE FROM pt WHERE false; + QUERY PLAN +------------------------------------------------------- + Delete on public.pt (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=0) + Output: ctid + Replaces: Scan on pt + One-Time Filter: false +(5 rows) + +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; + QUERY PLAN +------------------------------------------------------- + Update on public.pt (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=38) + 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 VERBOSE DELETE FROM pt WHERE a = 1; +ERROR: cannot delete from foreign table "p1" +-- UPDATE dummy-root again (with pruning on): still shows ctid. +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; + QUERY PLAN +------------------------------------------------------- + Update on public.pt (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=38) + Output: 'x'::text, ctid + Replaces: Scan on pt + One-Time Filter: false +(5 rows) + +-- Runtime pruning includes the foreign child in the plan; executor errors +-- since the foreign child does not support the command. +EXPLAIN VERBOSE DELETE FROM pt WHERE (SELECT false); +ERROR: cannot delete from foreign table "p1" +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE (SELECT false); +ERROR: cannot update 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..9b249c8b058 100644 --- a/contrib/file_fdw/sql/file_fdw.sql +++ b/contrib/file_fdw/sql/file_fdw.sql @@ -242,6 +242,44 @@ 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 VERBOSE DELETE FROM pt WHERE false; +-- also cover wholerow for UPDATE; expect NULL::oid and NULL::record +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; +-- MERGE behaves the same here; expect NULL::oid +EXPLAIN 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 VERBOSE DELETE FROM pt WHERE false; +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; + +-- Foreign child not pruned and it does not support DELETE: error. +EXPLAIN VERBOSE DELETE FROM pt WHERE a = 1; + +-- UPDATE dummy-root again (with pruning on): still shows ctid. +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE false; + +-- Runtime pruning includes the foreign child in the plan; executor errors +-- since the foreign child does not support the command. +EXPLAIN VERBOSE DELETE FROM pt WHERE (SELECT false); +EXPLAIN VERBOSE UPDATE pt SET b = 'x' WHERE (SELECT false); + DROP TABLE pt; -- generated column tests diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 4c5647ac38a..9f4dce8668f 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -4863,7 +4863,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) { resultRelInfo->ri_RowIdAttNo = ExecFindJunkAttributeInTlist(subplan->targetlist, "ctid"); - if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)) + + /* + * For heap relations, a ctid junk attribute must be present. + * For partitioned tables, require it only when at least one leaf + * result relation remains in the plan. If the plan has only the + * dummy root (no leaves), no rows can be produced and ctid is not + * needed. + */ + if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo) && + (relkind != RELKIND_PARTITIONED_TABLE || nrels > 1)) elog(ERROR, "could not find junk ctid column"); } else if (relkind == RELKIND_FOREIGN_TABLE) -- 2.47.3 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 06:03 Amit Langote <[email protected]> parent: Tender Wang <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2025-11-07 06:03 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] Hi, On Fri, Nov 7, 2025 at 10:01 AM Tender Wang <[email protected]> wrote: > Amit Langote <[email protected]> 于2025年11月6日周四 18:00写道： >> So for back branches, I’d propose sticking with the smaller >> executor-side fix and perhaps revisiting the planner behavior >> separately if we ever want to refine handling of pruned partitions or >> dummy roots. I understand, as was reported upthread, that the EXPLAIN >> VERBOSE output isn’t very consistent with that patch even though the >> internal error goes away. Making sense of the output differences >> requires knowing that the targetlist population behavior differs >> depending on whether enable_partition_pruning is on or off as I >> described above. > > The executor-side fix works for me Thanks for checking. > and the test case should be added to your patch. > Should we add some comments to explain the output difference in EXPLAIN VERBOSE > if enable_partition_pruning is set to a different value? I added some in the v2 patch I just posted. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 06:35 Tender Wang <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 0 replies; 22+ messages in thread From: Tender Wang @ 2025-11-07 06:35 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] Amit Langote <[email protected]> 于2025年11月7日周五 14:04写道： > Hi, > > On Fri, Nov 7, 2025 at 10:01 AM Tender Wang <[email protected]> wrote: > > Amit Langote <[email protected]> 于2025年11月6日周四 18:00写道： > >> So for back branches, I’d propose sticking with the smaller > >> executor-side fix and perhaps revisiting the planner behavior > >> separately if we ever want to refine handling of pruned partitions or > >> dummy roots. I understand, as was reported upthread, that the EXPLAIN > >> VERBOSE output isn’t very consistent with that patch even though the > >> internal error goes away. Making sense of the output differences > >> requires knowing that the targetlist population behavior differs > >> depending on whether enable_partition_pruning is on or off as I > >> described above. > > > > The executor-side fix works for me > > Thanks for checking. > > > and the test case should be added to your patch. > > Should we add some comments to explain the output difference in EXPLAIN > VERBOSE > > if enable_partition_pruning is set to a different value? > > I added some in the v2 patch I just posted. > I run tests in regress and file_fdw, no failed cases. No objections from me. -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 09:04 Kirill Reshke <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Kirill Reshke @ 2025-11-07 09:04 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Fri, 7 Nov 2025 at 11:02, Amit Langote <[email protected]> wrote: > > On Thu, Nov 6, 2025 at 7:00 PM Amit Langote <[email protected]> wrote: > > I looked at a few options, but none seem non-invasive enough for > > back-patching, apart from the first patch I posted. That one makes > > ExecInitModifyTable() not require a ctid to be present to set the root > > partitioned table’s ri_RowIdAttNo, except in the case of MERGE, which > > seems to need it. The corner case that triggers the internal error for > > UPDATE/DELETE doesn’t occur for MERGE now and likely won’t when > > foreign tables eventually gain MERGE support; don't mark my words > > though ;-). > > Well, OK, I just had not tried hard enough to see that the same error > happens for MERGE too. > > With my patch applied: > EXPLAIN VERBOSE MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, > b) ON false WHEN MATCHED THEN UPDATE SET b = s.b; > ERROR: could not find junk ctid column > > I have another idea: we can simply recognize the corner condition that > throws this error in ExecInitModifyTable() by checking if > ModifyTable.resultRelations contains only the root partitioned table. > That can only happen for UPDATE, DELETE, or MERGE when all child > relations were excluded. > > Patch doing that attached. Added test cases to file_fdw's suite. > > -- > Thanks, Amit Langote HI! I think this is an OK option for backpatching. After v2 applied, I found the behavior of DELETE and EXPLAIN DELETE consistent. The only remaining issue is VERBOSE output difference with or without enable_partition_pruning (which is v19+ issue to worry about), correct? Also, should we add COSTS OFF to EXPLAIN in the regression test? I understand that costs should be always zero, but COSTS OFF is almost everywhere is tests -- Best regards, Kirill Reshke ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-07 09:23 Amit Langote <[email protected]> parent: Kirill Reshke <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2025-11-07 09:23 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] Hi, On Fri, Nov 7, 2025 at 6:05 PM Kirill Reshke <[email protected]> wrote: > On Fri, 7 Nov 2025 at 11:02, Amit Langote <[email protected]> wrote: > > > > On Thu, Nov 6, 2025 at 7:00 PM Amit Langote <[email protected]> wrote: > > > I looked at a few options, but none seem non-invasive enough for > > > back-patching, apart from the first patch I posted. That one makes > > > ExecInitModifyTable() not require a ctid to be present to set the root > > > partitioned table’s ri_RowIdAttNo, except in the case of MERGE, which > > > seems to need it. The corner case that triggers the internal error for > > > UPDATE/DELETE doesn’t occur for MERGE now and likely won’t when > > > foreign tables eventually gain MERGE support; don't mark my words > > > though ;-). > > > > Well, OK, I just had not tried hard enough to see that the same error > > happens for MERGE too. > > > > With my patch applied: > > EXPLAIN VERBOSE MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, > > b) ON false WHEN MATCHED THEN UPDATE SET b = s.b; > > ERROR: could not find junk ctid column > > > > I have another idea: we can simply recognize the corner condition that > > throws this error in ExecInitModifyTable() by checking if > > ModifyTable.resultRelations contains only the root partitioned table. > > That can only happen for UPDATE, DELETE, or MERGE when all child > > relations were excluded. > > > > Patch doing that attached. Added test cases to file_fdw's suite. > > HI! > > I think this is an OK option for backpatching. After v2 applied, I > found the behavior of DELETE and EXPLAIN DELETE consistent. Thanks for the comment. > The only > remaining issue is VERBOSE output difference with or without > enable_partition_pruning (which is v19+ issue to worry about), > correct? Yes, iff we are to do anything at all about the difference. > Also, should we add COSTS OFF to EXPLAIN in the regression test? I > understand that costs should be always zero, but COSTS OFF is almost > everywhere is tests Yeah, a good call. v3 attached. -- Thanks, Amit Langote Attachments: [application/octet-stream] v3-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch (7.8K, 2-v3-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch) download | inline diff: From 42c14e3a448983cc3e52260fdfc48a428a71a67a Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Fri, 7 Nov 2025 18:12:46 +0900 Subject: [PATCH v3] Fix bogus ctid requirement for dummy-root partitioned targets ExecInitModifyTable() unconditionally required a ctid junk column even when the target was a partitioned table. This led to spurious "could not find junk ctid column" errors when all children were excluded and only the dummy root result relation remained. Require ctid for heap relations as before. For partitioned tables, require it only when at least one leaf result relation remains in the plan. If the plan has only the dummy root, no rows can be produced and ctid is thus not needed. 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 | 75 ++++++++++++++++++++++++++ contrib/file_fdw/sql/file_fdw.sql | 34 ++++++++++++ src/backend/executor/nodeModifyTable.c | 11 +++- 3 files changed, 119 insertions(+), 1 deletion(-) diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index 5121e27dce5..6f7b9175735 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: NULL::oid + 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, NULL::oid, NULL::record + 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: NULL::oid + 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/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 4c5647ac38a..9f4dce8668f 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -4863,7 +4863,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) { resultRelInfo->ri_RowIdAttNo = ExecFindJunkAttributeInTlist(subplan->targetlist, "ctid"); - if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)) + + /* + * For heap relations, a ctid junk attribute must be present. + * For partitioned tables, require it only when at least one leaf + * result relation remains in the plan. If the plan has only the + * dummy root (no leaves), no rows can be produced and ctid is not + * needed. + */ + if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo) && + (relkind != RELKIND_PARTITIONED_TABLE || nrels > 1)) elog(ERROR, "could not find junk ctid column"); } else if (relkind == RELKIND_FOREIGN_TABLE) -- 2.47.3 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-08 05:19 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 0 replies; 22+ messages in thread From: Amit Langote @ 2025-11-08 05:19 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Fri, Nov 7, 2025 at 6:23 PM Amit Langote <[email protected]> wrote: > On Fri, Nov 7, 2025 at 6:05 PM Kirill Reshke <[email protected]> wrote: > > On Fri, 7 Nov 2025 at 11:02, Amit Langote <[email protected]> wrote: > > > I have another idea: we can simply recognize the corner condition that > > > throws this error in ExecInitModifyTable() by checking if > > > ModifyTable.resultRelations contains only the root partitioned table. > > > That can only happen for UPDATE, DELETE, or MERGE when all child > > > relations were excluded. > > > > > > Patch doing that attached. Added test cases to file_fdw's suite. > > > > I think this is an OK option for backpatching. After v2 applied, I > > found the behavior of DELETE and EXPLAIN DELETE consistent. > > Thanks for the comment. > > > The only > > remaining issue is VERBOSE output difference with or without > > enable_partition_pruning (which is v19+ issue to worry about), > > correct? > > Yes, iff we are to do anything at all about the difference. > > > Also, should we add COSTS OFF to EXPLAIN in the regression test? I > > understand that costs should be always zero, but COSTS OFF is almost > > everywhere is tests > > Yeah, a good call. > > v3 attached. Attached v4 where I have updated the commit message to mention 86dc9005. The bug doesn’t seem critical enough to rush the fix, so I’ll hold off on committing it for next week’s release to leave room for further comments. -- Thanks, Amit Langote Attachments: [application/octet-stream] v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch (8.1K, 2-v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch) download | inline diff: From 45e05afcf053a8c39487cc13fcbc47bc7db46477 Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Sat, 8 Nov 2025 14:12:13 +0900 Subject: [PATCH v4] Fix bogus ctid requirement for dummy-root partitioned targets MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit ExecInitModifyTable() unconditionally required a ctid junk column even when the target was a partitioned table. This led to spurious "could not find junk ctid column" errors when all children were excluded and only the dummy root result relation remained. Require ctid for heap relations as before. For partitioned tables, require it only when at least one leaf result relation remains in the plan. If the plan has only the dummy root, no rows can be produced and ctid is thus not needed. Back-patch to v14, where commit 86dc9005 reworked UPDATE/DELETE planning to use a single subplan for all target relations. That change unified how the subplan’s targetlist is constructed and introduced this issue. 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 | 75 ++++++++++++++++++++++++++ contrib/file_fdw/sql/file_fdw.sql | 34 ++++++++++++ src/backend/executor/nodeModifyTable.c | 11 +++- 3 files changed, 119 insertions(+), 1 deletion(-) diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index 5121e27dce5..6f7b9175735 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: NULL::oid + 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, NULL::oid, NULL::record + 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: NULL::oid + 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/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 4c5647ac38a..b95f61f9311 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -4863,7 +4863,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) { resultRelInfo->ri_RowIdAttNo = ExecFindJunkAttributeInTlist(subplan->targetlist, "ctid"); - if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)) + + /* + * For heap relations, a ctid junk attribute must be present. + * For partitioned tables, require it only when at least one + * leaf result relation remains in the plan. If the plan has + * only the dummy root (no leaves), no rows can be produced + * and ctid is not needed. + */ + if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo) && + (relkind != RELKIND_PARTITIONED_TABLE || nrels > 1)) elog(ERROR, "could not find junk ctid column"); } else if (relkind == RELKIND_FOREIGN_TABLE) -- 2.47.3 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-26 11:27 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 2 siblings, 2 replies; 22+ messages in thread From: Amit Langote @ 2025-11-26 11:27 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] 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 Attachments: [application/octet-stream] 0001-Fix-row-identity-handling-for-dummy-partitioned-resu.patch (26.6K, 2-0001-Fix-row-identity-handling-for-dummy-partitioned-resu.patch) download | inline diff: From a1c2d5797313c2ef13a8c3b0ae2539577785eba1 Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Wed, 26 Nov 2025 20:19:21 +0900 Subject: [PATCH] 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 +++++++++ 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 ++-- 12 files changed, 171 insertions(+), 50 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/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.47.3 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-27 01:25 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 1 sibling, 0 replies; 22+ messages in thread From: Amit Langote @ 2025-11-27 01:25 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; Tender Wang <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] On Wed, Nov 26, 2025 at 8:27 PM Amit Langote <[email protected]> wrote: > 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. Forgot to mention that with this approach, unlike the other patch, the targetlists are identical whether or not enable_partition_pruning is on, since in both cases the if (root->row_identity_vars == NIL) block of distribute_row_identity_vars() executes: +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) pt.ctid in the off case vs only ctid in the on case has to do, I think, with there being more than one entry in the rtable in the pruning off case. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2025-11-30 05:59 Tender Wang <[email protected]> parent: Amit Langote <[email protected]> 1 sibling, 1 reply; 22+ messages in thread From: Tender Wang @ 2025-11-30 05:59 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; [email protected]; [email protected] 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 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-14 12:30 Amit Langote <[email protected]> parent: Tender Wang <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-14 12:30 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> 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 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-14 13:38 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-14 13:38 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Wed, Jan 14, 2026 at 9:30 PM Amit Langote <[email protected]> wrote: > 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 Hit send too soon re the 2nd bit I guess. Actually we can just return false when AddForeignUpdateTargets is missing, so the callback is still called when present. Updated patch attached. -- Thanks, Amit Langote Attachments: [application/octet-stream] v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch (40.6K, 2-v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch) download | inline diff: From a0bcb6be2380820108fbba739d0e13e02fb6a66a Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Wed, 14 Jan 2026 22:30:05 +0900 Subject: [PATCH v4] 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, returning false for foreign tables whose FDW lacks AddForeignUpdateTargets. 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. 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 | 23 ++++- 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, 150 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..e13841f34e4 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 lacks AddForeignUpdateTargets, 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,9 +991,17 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex, fdwroutine = GetFdwRoutineForRelation(target_relation, false); + /* + * If the FDW provides AddForeignUpdateTargets, let it add whatever + * row-identity columns it needs. Otherwise, this child cannot + * provide row identity, so return false to indicate no columns + * were added. + */ if (fdwroutine->AddForeignUpdateTargets != NULL) fdwroutine->AddForeignUpdateTargets(root, rtindex, target_rte, target_relation); + else + return false; /* * For UPDATE, we need to make the FDW fetch unchanged columns by @@ -1016,7 +1029,11 @@ add_row_identity_columns(PlannerInfo *root, Index rtindex, 0); add_row_identity_var(root, var, rtindex, "wholerow"); } + + return true; } + + return false; } /* @@ -1074,8 +1091,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 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-16 07:57 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-16 07:57 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Wed, Jan 14, 2026 at 10:38 PM Amit Langote <[email protected]> wrote: > On Wed, Jan 14, 2026 at 9:30 PM Amit Langote <[email protected]> wrote: > > 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 > > Hit send too soon re the 2nd bit I guess. Actually we can just return > false when AddForeignUpdateTargets is missing, so the callback is > still called when present. > > Updated patch attached. To summarize, the two approaches we've thought about: 1. Executor-side fix (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch posted with my Nov 8 email): Make ExecInitModifyTable() not require ctid when the only result relation is a dummy partitioned root. This is minimally invasive but leaves EXPLAIN VERBOSE output inconsistent depending on enable_partition_pruning -- with pruning off, you see tableoid but no ctid, while with pruning on, you see ctid. That's confusing for users as mentioned upthread. 2. Planner-side fix (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch posted with my last email): Don't add tableoid for child relations that don't contribute row-identity columns. This keeps root->row_identity_vars empty when there exists only one such child relation, so distribute_row_identity_vars() can add ctid for the dummy root. EXPLAIN output is consistent regardless of pruning setting. (Some may notice in the patch that there's still a minor change, but that's due to how explain.c decides whether to print the table name before the column name, which is unrelated to this.) I'm inclined to go with the second approach. The only back-patching concern is that EXPLAIN VERBOSE output order changes (ctid now appears before tableoid). This is cosmetic -- junk columns are looked up by name, not position -- but could affect tests or tools that parse EXPLAIN output by position. If there are no objections, I'll commit patch #2 next week. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-19 17:56 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-19 17:56 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: David Rowley <[email protected]>; Tom Lane <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Fri, Jan 16, 2026 at 4:57 PM Amit Langote <[email protected]> wrote: > On Wed, Jan 14, 2026 at 10:38 PM Amit Langote <[email protected]> wrote: > > On Wed, Jan 14, 2026 at 9:30 PM Amit Langote <[email protected]> wrote: > > > 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 > > > > Hit send too soon re the 2nd bit I guess. Actually we can just return > > false when AddForeignUpdateTargets is missing, so the callback is > > still called when present. > > > > Updated patch attached. > > To summarize, the two approaches we've thought about: > > 1. Executor-side fix > (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch > posted with my Nov 8 email): > > Make ExecInitModifyTable() not require ctid when the only result > relation is a dummy partitioned root. This is minimally invasive but > leaves EXPLAIN VERBOSE output inconsistent depending on > enable_partition_pruning -- with pruning off, you see tableoid but no > ctid, while with pruning on, you see ctid. That's confusing for users > as mentioned upthread. > > 2. Planner-side fix > (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch > posted with my last email): > > Don't add tableoid for child relations that don't contribute > row-identity columns. This keeps root->row_identity_vars empty when > there exists only one such child relation, so > distribute_row_identity_vars() can add ctid for the dummy root. > EXPLAIN output is consistent regardless of pruning setting. (Some may > notice in the patch that there's still a minor change, but that's due > to how explain.c decides whether to print the table name before the > column name, which is unrelated to this.) > > I'm inclined to go with the second approach. The only back-patching > concern is that EXPLAIN VERBOSE output order changes (ctid now appears > before tableoid). This is cosmetic -- junk columns are looked up by > name, not position -- but could affect tests or tools that parse > EXPLAIN output by position. > > If there are no objections, I'll commit patch #2 next week. Tom, do you have any thoughts on the above? -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-19 18:25 Tom Lane <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Tom Lane @ 2026-01-19 18:25 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Tender Wang <[email protected]>; David Rowley <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> Amit Langote <[email protected]> writes: >> To summarize, the two approaches we've thought about: >> >> 1. Executor-side fix >> (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch >> posted with my Nov 8 email): >> >> Make ExecInitModifyTable() not require ctid when the only result >> relation is a dummy partitioned root. This is minimally invasive but >> leaves EXPLAIN VERBOSE output inconsistent depending on >> enable_partition_pruning -- with pruning off, you see tableoid but no >> ctid, while with pruning on, you see ctid. That's confusing for users >> as mentioned upthread. >> >> 2. Planner-side fix >> (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch >> posted with my last email): >> >> Don't add tableoid for child relations that don't contribute >> row-identity columns. This keeps root->row_identity_vars empty when >> there exists only one such child relation, so >> distribute_row_identity_vars() can add ctid for the dummy root. >> EXPLAIN output is consistent regardless of pruning setting. (Some may >> notice in the patch that there's still a minor change, but that's due >> to how explain.c decides whether to print the table name before the >> column name, which is unrelated to this.) >> >> I'm inclined to go with the second approach. The only back-patching >> concern is that EXPLAIN VERBOSE output order changes (ctid now appears >> before tableoid). This is cosmetic -- junk columns are looked up by >> name, not position -- but could affect tests or tools that parse >> EXPLAIN output by position. >> >> If there are no objections, I'll commit patch #2 next week. > Tom, do you have any thoughts on the above? My apologies, I allowed this thread to fall off my radar. Of these two patches, I greatly prefer the executor-side fix. I think the planner-side fix is much too invasive to consider back-patching. Even if it doesn't bother any end users, it will surely break some extensions' regression tests, considering how many places it changes in our own tests. Also, I think the argument about preserving the same generated tlist is fairly misguided, for two reasons: 1. We've never expected that the set of row-identity columns would be independent of the set of child tables considered. For example, different FDWs might produce different sorts of row-ID Vars. 2. EXPLAIN's output for a partitioned query is usually different between pruning-on and pruning-off. Why's it important that this tlist detail not be different? So on the whole, I'd do #1 and call it good. I don't even see an argument for applying #2 in HEAD only. regards, tom lane ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-20 03:07 Amit Langote <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-20 03:07 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Tender Wang <[email protected]>; David Rowley <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Tue, Jan 20, 2026 at 3:25 AM Tom Lane <[email protected]> wrote: > Amit Langote <[email protected]> writes: > >> To summarize, the two approaches we've thought about: > >> > >> 1. Executor-side fix > >> (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch > >> posted with my Nov 8 email): > >> > >> Make ExecInitModifyTable() not require ctid when the only result > >> relation is a dummy partitioned root. This is minimally invasive but > >> leaves EXPLAIN VERBOSE output inconsistent depending on > >> enable_partition_pruning -- with pruning off, you see tableoid but no > >> ctid, while with pruning on, you see ctid. That's confusing for users > >> as mentioned upthread. > >> > >> 2. Planner-side fix > >> (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch > >> posted with my last email): > >> > >> Don't add tableoid for child relations that don't contribute > >> row-identity columns. This keeps root->row_identity_vars empty when > >> there exists only one such child relation, so > >> distribute_row_identity_vars() can add ctid for the dummy root. > >> EXPLAIN output is consistent regardless of pruning setting. (Some may > >> notice in the patch that there's still a minor change, but that's due > >> to how explain.c decides whether to print the table name before the > >> column name, which is unrelated to this.) > >> > >> I'm inclined to go with the second approach. The only back-patching > >> concern is that EXPLAIN VERBOSE output order changes (ctid now appears > >> before tableoid). This is cosmetic -- junk columns are looked up by > >> name, not position -- but could affect tests or tools that parse > >> EXPLAIN output by position. > >> > >> If there are no objections, I'll commit patch #2 next week. > > > Tom, do you have any thoughts on the above? > > My apologies, I allowed this thread to fall off my radar. > > Of these two patches, I greatly prefer the executor-side fix. > I think the planner-side fix is much too invasive to consider > back-patching. Even if it doesn't bother any end users, > it will surely break some extensions' regression tests, > considering how many places it changes in our own tests. Ok, a fair point. > Also, I think the argument about preserving the same generated > tlist is fairly misguided, for two reasons: > > 1. We've never expected that the set of row-identity columns would > be independent of the set of child tables considered. For example, > different FDWs might produce different sorts of row-ID Vars. > > 2. EXPLAIN's output for a partitioned query is usually different > between pruning-on and pruning-off. Why's it important that > this tlist detail not be different? Right, the targetlist will look different if a foreign child is pruned vs not anyway. I was maybe too focused on this degenerate case where all children are excluded -- with pruning off you get tableoid but no ctid (because the foreign child was processed before constraint exclusion, leaving root->row_identity_vars non-empty triggering the block in distribute_row_identity_vars() to add ctid), with pruning on you get ctid but no tableoid (because the child was never processed, leaving root->row_identity_vars empty). Because, the degenerate case is a no-op at runtime, maybe we're ok. > So on the whole, I'd do #1 and call it good. I don't even see an > argument for applying #2 in HEAD only. Ok, I'll post an updated patch for #1 shortly. Thanks a lot for chiming in. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-21 12:58 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 22+ messages in thread From: Amit Langote @ 2026-01-21 12:58 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Tender Wang <[email protected]>; David Rowley <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Tue, Jan 20, 2026 at 12:07 PM Amit Langote <[email protected]> wrote: > On Tue, Jan 20, 2026 at 3:25 AM Tom Lane <[email protected]> wrote: > > Amit Langote <[email protected]> writes: > > >> To summarize, the two approaches we've thought about: > > >> > > >> 1. Executor-side fix > > >> (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch > > >> posted with my Nov 8 email): > > >> > > >> Make ExecInitModifyTable() not require ctid when the only result > > >> relation is a dummy partitioned root. This is minimally invasive but > > >> leaves EXPLAIN VERBOSE output inconsistent depending on > > >> enable_partition_pruning -- with pruning off, you see tableoid but no > > >> ctid, while with pruning on, you see ctid. That's confusing for users > > >> as mentioned upthread. > > >> > > >> 2. Planner-side fix > > >> (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch > > >> posted with my last email): > > >> > > >> Don't add tableoid for child relations that don't contribute > > >> row-identity columns. This keeps root->row_identity_vars empty when > > >> there exists only one such child relation, so > > >> distribute_row_identity_vars() can add ctid for the dummy root. > > >> EXPLAIN output is consistent regardless of pruning setting. (Some may > > >> notice in the patch that there's still a minor change, but that's due > > >> to how explain.c decides whether to print the table name before the > > >> column name, which is unrelated to this.) > > >> > > >> I'm inclined to go with the second approach. The only back-patching > > >> concern is that EXPLAIN VERBOSE output order changes (ctid now appears > > >> before tableoid). This is cosmetic -- junk columns are looked up by > > >> name, not position -- but could affect tests or tools that parse > > >> EXPLAIN output by position. > > >> > > >> If there are no objections, I'll commit patch #2 next week. > > > > > Tom, do you have any thoughts on the above? > > > > My apologies, I allowed this thread to fall off my radar. > > > > Of these two patches, I greatly prefer the executor-side fix. > > I think the planner-side fix is much too invasive to consider > > back-patching. Even if it doesn't bother any end users, > > it will surely break some extensions' regression tests, > > considering how many places it changes in our own tests. > > Ok, a fair point. > > > Also, I think the argument about preserving the same generated > > tlist is fairly misguided, for two reasons: > > > > 1. We've never expected that the set of row-identity columns would > > be independent of the set of child tables considered. For example, > > different FDWs might produce different sorts of row-ID Vars. > > > > 2. EXPLAIN's output for a partitioned query is usually different > > between pruning-on and pruning-off. Why's it important that > > this tlist detail not be different? > > Right, the targetlist will look different if a foreign child is pruned > vs not anyway. I was maybe too focused on this degenerate case where > all children are excluded -- with pruning off you get tableoid but no > ctid (because the foreign child was processed before constraint > exclusion, leaving root->row_identity_vars non-empty triggering the > block in distribute_row_identity_vars() to add ctid), with pruning on > you get ctid but no tableoid (because the child was never processed, > leaving root->row_identity_vars empty). > > Because, the degenerate case is a no-op at runtime, maybe we're ok. > > > So on the whole, I'd do #1 and call it good. I don't even see an > > argument for applying #2 in HEAD only. > > Ok, I'll post an updated patch for #1 shortly. Updated patch attached. While reworking it, I realized that partitioned tables should only appear in the result relations list when all leaf partitions have been pruned. So instead of checking nrels > 1, I now Assert(nrels == 1) when we see a partitioned table and skip the ctid requirement. Also added a corresponding adjustment in ExecModifyTable() to allow invalid ri_RowIdAttNo for partitioned tables. -- Thanks, Amit Langote Attachments: [application/octet-stream] v5-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch (5.2K, 2-v5-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch) download | inline diff: From 0c3c3c48c5a815388c402ecbc2b83d0b6fa995ba Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Wed, 21 Jan 2026 21:47:06 +0900 Subject: [PATCH v5] Fix bogus ctid requirement for dummy-root partitioned targets ExecInitModifyTable() unconditionally required a ctid junk column even when the target was a partitioned table. This led to spurious "could not find junk ctid column" errors when all children were excluded and only the dummy root result relation remained. Partitioned tables should only appear in the result relations list when all leaf partitions have been pruned, leaving only the dummy root. In this case, no rows can be produced and ctid is not needed. Add an Assert to verify this invariant and skip the ctid requirement for partitioned tables. Also adjust ExecModifyTable() to allow invalid ri_RowIdAttNo for partitioned tables. 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 | 15 +++++++++++++++ contrib/file_fdw/sql/file_fdw.sql | 18 ++++++++++++++++++ src/backend/executor/nodeModifyTable.c | 19 ++++++++++++++++--- 3 files changed, 49 insertions(+), 3 deletions(-) diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out index 5121e27dce5..51a81ed59a8 100644 --- a/contrib/file_fdw/expected/file_fdw.out +++ b/contrib/file_fdw/expected/file_fdw.out @@ -457,6 +457,21 @@ SELECT tableoid::regclass, * FROM p2; p2 | 2 | xyzzy (3 rows) +-- Test DELETE/UPDATE/MERGE on a partitioned table when all partitions +-- are excluded and only the dummy root result relation remains. The +-- operation is a no-op but should not fail regardless of whether the +-- foreign child was processed (pruning off) or not (pruning on). +DROP TABLE p2; +SET enable_partition_pruning TO off; +DELETE FROM pt WHERE false; +UPDATE pt SET b = 'x' WHERE false; +MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b) + ON false WHEN MATCHED THEN UPDATE SET b = s.b; +SET enable_partition_pruning TO on; +DELETE FROM pt WHERE false; +UPDATE pt SET b = 'x' WHERE false; +MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b) + ON false WHEN MATCHED THEN UPDATE SET b = s.b; 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..4ccdcbb8d06 100644 --- a/contrib/file_fdw/sql/file_fdw.sql +++ b/contrib/file_fdw/sql/file_fdw.sql @@ -242,6 +242,24 @@ UPDATE pt set a = 1 where a = 2; -- ERROR SELECT tableoid::regclass, * FROM pt; SELECT tableoid::regclass, * FROM p1; SELECT tableoid::regclass, * FROM p2; + +-- Test DELETE/UPDATE/MERGE on a partitioned table when all partitions +-- are excluded and only the dummy root result relation remains. The +-- operation is a no-op but should not fail regardless of whether the +-- foreign child was processed (pruning off) or not (pruning on). +DROP TABLE p2; +SET enable_partition_pruning TO off; +DELETE FROM pt WHERE false; +UPDATE pt SET b = 'x' WHERE false; +MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b) + ON false WHEN MATCHED THEN UPDATE SET b = s.b; + +SET enable_partition_pruning TO on; +DELETE FROM pt WHERE false; +UPDATE pt SET b = 'x' WHERE false; +MERGE INTO pt t USING (VALUES (1, 'x'::text)) AS s(a, b) + ON false WHEN MATCHED THEN UPDATE SET b = s.b; + DROP TABLE pt; -- generated column tests diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 7d7411a7056..1126f7ed823 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -4369,8 +4369,12 @@ ExecModifyTable(PlanState *pstate) relkind == RELKIND_MATVIEW || relkind == RELKIND_PARTITIONED_TABLE) { - /* ri_RowIdAttNo refers to a ctid attribute */ - Assert(AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)); + /* + * ri_RowIdAttNo refers to a ctid attribute. See the comment + * in ExecInitModifyTable(). + */ + Assert(AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo) || + relkind == RELKIND_PARTITIONED_TABLE); datum = ExecGetJunkAttribute(slot, resultRelInfo->ri_RowIdAttNo, &isNull); @@ -4883,7 +4887,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) { resultRelInfo->ri_RowIdAttNo = ExecFindJunkAttributeInTlist(subplan->targetlist, "ctid"); - if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)) + + /* + * For heap relations, a ctid junk attribute must be present. + * Partitioned tables should only appear here when all leaf + * partitions were pruned, in which case no rows can be + * produced and ctid is not needed. + */ + if (relkind == RELKIND_PARTITIONED_TABLE) + Assert(nrels == 1); + else if (!AttributeNumberIsValid(resultRelInfo->ri_RowIdAttNo)) elog(ERROR, "could not find junk ctid column"); } else if (relkind == RELKIND_FOREIGN_TABLE) -- 2.47.3 ^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error @ 2026-01-23 01:40 Amit Langote <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 0 replies; 22+ messages in thread From: Amit Langote @ 2026-01-23 01:40 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Tender Wang <[email protected]>; David Rowley <[email protected]>; Kirill Reshke <[email protected]>; jian he <[email protected]>; Alexander Lakhin <[email protected]>; PostgreSQL mailing lists <[email protected]> On Wed, Jan 21, 2026 at 9:58 PM Amit Langote <[email protected]> wrote: > On Tue, Jan 20, 2026 at 12:07 PM Amit Langote <[email protected]> wrote: > > On Tue, Jan 20, 2026 at 3:25 AM Tom Lane <[email protected]> wrote: > > > Amit Langote <[email protected]> writes: > > > >> To summarize, the two approaches we've thought about: > > > >> > > > >> 1. Executor-side fix > > > >> (v4-0001-Fix-bogus-ctid-requirement-for-dummy-root-partiti.patch > > > >> posted with my Nov 8 email): > > > >> > > > >> Make ExecInitModifyTable() not require ctid when the only result > > > >> relation is a dummy partitioned root. This is minimally invasive but > > > >> leaves EXPLAIN VERBOSE output inconsistent depending on > > > >> enable_partition_pruning -- with pruning off, you see tableoid but no > > > >> ctid, while with pruning on, you see ctid. That's confusing for users > > > >> as mentioned upthread. > > > >> > > > >> 2. Planner-side fix > > > >> (v4-0001-Fix-row-identity-handling-for-dummy-partitioned-r.patch > > > >> posted with my last email): > > > >> > > > >> Don't add tableoid for child relations that don't contribute > > > >> row-identity columns. This keeps root->row_identity_vars empty when > > > >> there exists only one such child relation, so > > > >> distribute_row_identity_vars() can add ctid for the dummy root. > > > >> EXPLAIN output is consistent regardless of pruning setting. (Some may > > > >> notice in the patch that there's still a minor change, but that's due > > > >> to how explain.c decides whether to print the table name before the > > > >> column name, which is unrelated to this.) > > > >> > > > >> I'm inclined to go with the second approach. The only back-patching > > > >> concern is that EXPLAIN VERBOSE output order changes (ctid now appears > > > >> before tableoid). This is cosmetic -- junk columns are looked up by > > > >> name, not position -- but could affect tests or tools that parse > > > >> EXPLAIN output by position. > > > >> > > > >> If there are no objections, I'll commit patch #2 next week. > > > > > > > Tom, do you have any thoughts on the above? > > > > > > My apologies, I allowed this thread to fall off my radar. > > > > > > Of these two patches, I greatly prefer the executor-side fix. > > > I think the planner-side fix is much too invasive to consider > > > back-patching. Even if it doesn't bother any end users, > > > it will surely break some extensions' regression tests, > > > considering how many places it changes in our own tests. > > > > Ok, a fair point. > > > > > Also, I think the argument about preserving the same generated > > > tlist is fairly misguided, for two reasons: > > > > > > 1. We've never expected that the set of row-identity columns would > > > be independent of the set of child tables considered. For example, > > > different FDWs might produce different sorts of row-ID Vars. > > > > > > 2. EXPLAIN's output for a partitioned query is usually different > > > between pruning-on and pruning-off. Why's it important that > > > this tlist detail not be different? > > > > Right, the targetlist will look different if a foreign child is pruned > > vs not anyway. I was maybe too focused on this degenerate case where > > all children are excluded -- with pruning off you get tableoid but no > > ctid (because the foreign child was processed before constraint > > exclusion, leaving root->row_identity_vars non-empty triggering the > > block in distribute_row_identity_vars() to add ctid), with pruning on > > you get ctid but no tableoid (because the child was never processed, > > leaving root->row_identity_vars empty). > > > > Because, the degenerate case is a no-op at runtime, maybe we're ok. > > > > > So on the whole, I'd do #1 and call it good. I don't even see an > > > argument for applying #2 in HEAD only. > > > > Ok, I'll post an updated patch for #1 shortly. > > Updated patch attached. While reworking it, I realized that > partitioned tables should only appear in the result relations list > when all leaf partitions have been pruned. So instead of checking > nrels > 1, I now Assert(nrels == 1) when we see a partitioned table > and skip the ctid requirement. Also added a corresponding adjustment > in ExecModifyTable() to allow invalid ri_RowIdAttNo for partitioned > tables. Pushed this now. Thanks to all. -- Thanks, Amit Langote ^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2026-01-23 01:40 UTC | newest] Thread overview: 22+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-30 13:48 Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error Tom Lane <[email protected]> 2025-10-31 00:30 ` Amit Langote <[email protected]> 2025-10-31 01:50 ` David Rowley <[email protected]> 2025-11-06 10:00 ` Amit Langote <[email protected]> 2025-11-07 01:01 ` Tender Wang <[email protected]> 2025-11-07 06:03 ` Amit Langote <[email protected]> 2025-11-07 06:35 ` Tender Wang <[email protected]> 2025-11-07 06:02 ` Amit Langote <[email protected]> 2025-11-07 09:04 ` Kirill Reshke <[email protected]> 2025-11-07 09:23 ` Amit Langote <[email protected]> 2025-11-08 05:19 ` Amit Langote <[email protected]> 2025-11-26 11:27 ` Amit Langote <[email protected]> 2025-11-27 01:25 ` Amit Langote <[email protected]> 2025-11-30 05:59 ` Tender Wang <[email protected]> 2026-01-14 12:30 ` Amit Langote <[email protected]> 2026-01-14 13:38 ` Amit Langote <[email protected]> 2026-01-16 07:57 ` Amit Langote <[email protected]> 2026-01-19 17:56 ` Amit Langote <[email protected]> 2026-01-19 18:25 ` Tom Lane <[email protected]> 2026-01-20 03:07 ` Amit Langote <[email protected]> 2026-01-21 12:58 ` Amit Langote <[email protected]> 2026-01-23 01:40 ` Amit Langote <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox