public inbox for [email protected]help / color / mirror / Atom feed
pg17: XX000: no relation entry for relid 0 6+ messages / 4 participants [nested] [flat]
* pg17: XX000: no relation entry for relid 0 @ 2026-04-10 10:54 Justin Pryzby <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Justin Pryzby @ 2026-04-10 10:54 UTC (permalink / raw) To: [email protected]; +Cc: Paul Jungwirth <[email protected]> CREATE VIEW x AS SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT ARRAY[NULL::bigint]; SELECT FROM x; ERROR: XX000: no relation entry for relid 0 Since 9391f7152. -- Justin ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg17: XX000: no relation entry for relid 0 @ 2026-04-10 12:49 Tender Wang <[email protected]> parent: Justin Pryzby <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tender Wang @ 2026-04-10 12:49 UTC (permalink / raw) To: Justin Pryzby <[email protected]>; +Cc: [email protected]; Paul Jungwirth <[email protected]>; Tom Lane <[email protected]> Justin Pryzby <[email protected]> 于2026年4月10日周五 18:54写道： > > CREATE VIEW x AS SELECT NULL::int[] > UNION ALL SELECT NULL::int[] > UNION ALL SELECT ARRAY[NULL::bigint]; > > SELECT FROM x; > ERROR: XX000: no relation entry for relid 0 > > Since 9391f7152. The varno = 0 is from below code: *pTargetList = generate_setop_tlist(colTypes, colCollations, 0, false, *pTargetList, refnames_tlist, &trivial_tlist); *istrivial_tlist = trivial_tlist; target = create_pathtarget(root, *pTargetList); in recurse_set_operations(). When calling set_pathtarget_cost_width(), we estimate the length of the array that would enter estimate_array_length(). Before 9391f7152, we returned 10 directly. But now, we see if we can find any statistics about it. In examine_variable(), try to find base_rel in find_base_rel(), error reported. Because the varno of the Var is 0. I didn't think too much at now, a quick fix as below: diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 4160d2d6e24..ff93fc3ac8a 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -2265,6 +2265,9 @@ estimate_array_length(PlannerInfo *root, Node *arrayexpr) AttStatsSlot sslot; double nelem = 0; + if (IsA(arrayexpr, Var) && ((Var *) arrayexpr)->varno == 0) + return 10; + examine_variable(root, arrayexpr, 0, &vardata); if (HeapTupleIsValid(vardata.statsTuple)) { Any thoughts? -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg17: XX000: no relation entry for relid 0 @ 2026-04-10 14:38 Richard Guo <[email protected]> parent: Tender Wang <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Richard Guo @ 2026-04-10 14:38 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: Justin Pryzby <[email protected]>; [email protected]; Paul Jungwirth <[email protected]>; Tom Lane <[email protected]> On Fri, Apr 10, 2026 at 9:49 PM Tender Wang <[email protected]> wrote: > Justin Pryzby <[email protected]> 于2026年4月10日周五 18:54写道： > > CREATE VIEW x AS SELECT NULL::int[] > > UNION ALL SELECT NULL::int[] > > UNION ALL SELECT ARRAY[NULL::bigint]; > > > > SELECT FROM x; > > ERROR: XX000: no relation entry for relid 0 Nice catch. It seems that we need at least three branches to reproduce this, so that there's a nested UNION ALL whose output type doesn't match the parent's expected type. At the outer branch maybe we can use NULL::bigint[] instead to be a little simpler. SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT NULL::bigint[]; ERROR: no relation entry for relid 0 > I didn't think too much at now, a quick fix as below: > diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c > index 4160d2d6e24..ff93fc3ac8a 100644 > --- a/src/backend/utils/adt/selfuncs.c > +++ b/src/backend/utils/adt/selfuncs.c > @@ -2265,6 +2265,9 @@ estimate_array_length(PlannerInfo *root, Node *arrayexpr) > AttStatsSlot sslot; > double nelem = 0; > > + if (IsA(arrayexpr, Var) && ((Var *) arrayexpr)->varno == 0) > + return 10; > + > examine_variable(root, arrayexpr, 0, &vardata); > if (HeapTupleIsValid(vardata.statsTuple)) > { > > Any thoughts? This looks like the right fix to me. We can use some comment here. - Richard ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg17: XX000: no relation entry for relid 0 @ 2026-04-10 20:34 Michael Paquier <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Michael Paquier @ 2026-04-10 20:34 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Tender Wang <[email protected]>; Justin Pryzby <[email protected]>; [email protected]; Paul Jungwirth <[email protected]>; Tom Lane <[email protected]> On Fri, Apr 10, 2026 at 11:38:50PM +0900, Richard Guo wrote: > Nice catch. It seems that we need at least three branches to > reproduce this, so that there's a nested UNION ALL whose output type > doesn't match the parent's expected type. At the outer branch maybe > we can use NULL::bigint[] instead to be a little simpler. Indeed, three branches: $ git branch --contains 9391f7152 + REL_17_STABLE + REL_18_STABLE * master -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg17: XX000: no relation entry for relid 0 @ 2026-04-11 01:31 Tender Wang <[email protected]> parent: Richard Guo <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Tender Wang @ 2026-04-11 01:31 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Justin Pryzby <[email protected]>; [email protected]; Paul Jungwirth <[email protected]>; Tom Lane <[email protected]>; Michael Paquier <[email protected]> Richard Guo <[email protected]> 于2026年4月10日周五 22:39写道： > > On Fri, Apr 10, 2026 at 9:49 PM Tender Wang <[email protected]> wrote: > > Justin Pryzby <[email protected]> 于2026年4月10日周五 18:54写道： > > > CREATE VIEW x AS SELECT NULL::int[] > > > UNION ALL SELECT NULL::int[] > > > UNION ALL SELECT ARRAY[NULL::bigint]; > > > > > > SELECT FROM x; > > > ERROR: XX000: no relation entry for relid 0 > > Nice catch. It seems that we need at least three branches to > reproduce this, so that there's a nested UNION ALL whose output type > doesn't match the parent's expected type. At the outer branch maybe > we can use NULL::bigint[] instead to be a little simpler. > > SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT NULL::bigint[]; > ERROR: no relation entry for relid 0 Yes, add it to the test case. > > > I didn't think too much at now, a quick fix as below: > > diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c > > index 4160d2d6e24..ff93fc3ac8a 100644 > > --- a/src/backend/utils/adt/selfuncs.c > > +++ b/src/backend/utils/adt/selfuncs.c > > @@ -2265,6 +2265,9 @@ estimate_array_length(PlannerInfo *root, Node *arrayexpr) > > AttStatsSlot sslot; > > double nelem = 0; > > > > + if (IsA(arrayexpr, Var) && ((Var *) arrayexpr)->varno == 0) > > + return 10; > > + > > examine_variable(root, arrayexpr, 0, &vardata); > > if (HeapTupleIsValid(vardata.statsTuple)) > > { > > > > Any thoughts? > > This looks like the right fix to me. We can use some comment here. comments added. Please see the attached patch. -- Thanks, Tender Wang Attachments: [application/octet-stream] 0001-Fix-no-relation-entry-for-relid-0-in-nested-UNION-AL.patch (2.8K, 2-0001-Fix-no-relation-entry-for-relid-0-in-nested-UNION-AL.patch) download | inline diff: From 09cae95e24996e34ca12e38133150e71e351edcc Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Sat, 11 Apr 2026 09:25:50 +0800 Subject: [PATCH] Fix "no relation entry for relid 0" in nested UNION ALL with array coercion. This patch addresses an issue where a three-branch (or more) UNION ALL fails during the planning/costing stage if it involves array type promotion. The error "no relation entry for relid 0" occurs because when UNION ALL branches are nested, the planner may generate a Var node with varno 0 to represent a coerced sub-tree (e.g., promoting int[] to bigint[]). Previously, the costing logic for array expressions did not account for these Vars with varno = 0, leading to a lookup failure. Example of failing query: SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT NULL::bigint[]; The fix ensures that such Var nodes are recognized and assigned a default cost, allowing the planner to proceed correctly. --- src/backend/utils/adt/selfuncs.c | 9 +++++++++ src/test/regress/expected/union.out | 11 +++++++++++ src/test/regress/sql/union.sql | 5 +++++ 3 files changed, 25 insertions(+) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 4160d2d6e24..adccb13dd0d 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -2265,6 +2265,15 @@ estimate_array_length(PlannerInfo *root, Node *arrayexpr) AttStatsSlot sslot; double nelem = 0; + /* + * Handle Var nodes with varno 0: + * in cases like (int[] UNION ALL int[]) UNION ALL bigint[]. + * The planner may generate a Var node with varno 0 when coercing + * the result of nested set operations. See recurse_set_operations(). + */ + if (IsA(arrayexpr, Var) && ((Var *) arrayexpr)->varno == 0 ) + return 10; + examine_variable(root, arrayexpr, 0, &vardata); if (HeapTupleIsValid(vardata.statsTuple)) { diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 709c85f2294..2c7643b5289 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1661,3 +1661,14 @@ on true limit 1; -> Result (6 rows) +-- +-- Test nested UNION ALL with type coercion +-- +SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT NULL::bigint[]; + int4 +------ + + + +(3 rows) + diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index d0c70fafbea..cc5fd029fa3 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -664,3 +664,8 @@ select * from tenk1 t1 left join lateral (select t1.tenthous from tenk2 t2 union all (values(1))) on true limit 1; + +-- +-- Test nested UNION ALL with type coercion +-- +SELECT NULL::int[] UNION ALL SELECT NULL::int[] UNION ALL SELECT NULL::bigint[]; \ No newline at end of file -- 2.34.1 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg17: XX000: no relation entry for relid 0 @ 2026-04-11 07:57 Richard Guo <[email protected]> parent: Tender Wang <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-04-11 07:57 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: Justin Pryzby <[email protected]>; [email protected]; Paul Jungwirth <[email protected]>; Tom Lane <[email protected]>; Michael Paquier <[email protected]> On Sat, Apr 11, 2026 at 10:32 AM Tender Wang <[email protected]> wrote: > Richard Guo <[email protected]> 于2026年4月10日周五 22:39写道： > > This looks like the right fix to me. We can use some comment here. > comments added. > Please see the attached patch. I've pushed and back-patched the fix after some tweaks to the comment and commit message. I also used explain (verbose, costs off) in the test case so that we can observe that the inner UNION ALL's output type doesn't match the outer's. Thanks for the report and the patch. - Richard ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-11 07:57 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-04-10 10:54 pg17: XX000: no relation entry for relid 0 Justin Pryzby <[email protected]> 2026-04-10 12:49 ` Tender Wang <[email protected]> 2026-04-10 14:38 ` Richard Guo <[email protected]> 2026-04-10 20:34 ` Michael Paquier <[email protected]> 2026-04-11 01:31 ` Tender Wang <[email protected]> 2026-04-11 07:57 ` Richard Guo <[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