public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedRe: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
21+ messages / 5 participants
[nested] [flat]
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
@ 2026-03-18 08:40 Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 09:46 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-18 08:40 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Hi, Tender!
On Wed, Mar 18, 2026 at 7:44 AM Tender Wang <tndrwang@gmail.com> wrote:
>
> Tender Wang <tndrwang@gmail.com> 于2026年3月18日周三 09:12写道：
> >
> > Alexander Korotkov <aekorotkov@gmail.com> 于2026年3月18日周三 01:46写道：
> > >
> > > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <tndrwang@gmail.com> wrote:
> > > > Kirill Reshke <reshkekirill@gmail.com> 于2026年3月17日周二 21:24写道：
> > > > >
> > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <tndrwang@gmail.com> wrote:
> > > > > Yes, it looks like your analysis is valid. Will you share a patch for
> > > > > updating `clause` ?
> > > > >
> > > > Sorry, it's already the middle of the night here, so I'm afraid I
> > > > don't have much time to work on this right now.
> > >
> > > Thank you for your research. I've written a simple draft patch. It
> > > fixes the reported case, but I doubt it is correct in general. I'll
> > > continue the investigation.
> > I tried the above fix, no error again. But I got a plan like this:
> > QUERY PLAN
> > -------------------------------------------------------------------------------------------
> > Nested Loop Left Join (cost=0.00..115164616.71 rows=7458350250 width=4)
> > -> Seq Scan on pg_table_a tom3 (cost=0.00..34.70 rows=2470 width=0)
> > -> Materialize (cost=0.00..99509.82 rows=3019575 width=0)
> > -> Nested Loop Left Join (cost=0.00..75564.95 rows=3019575 width=0)
> > Join Filter: tom2.col_bool
> > -> Seq Scan on pg_table_a tom2 (cost=0.00..34.70
> > rows=2445 width=5)
> > Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))
> > -> Materialize (cost=0.00..47.05 rows=2470 width=0)
> > -> Seq Scan on pg_table_a tom0
> > (cost=0.00..34.70 rows=2470 width=0)
> > (9 rows)
> >
> > Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))
> > This makes me unhappy.
> > Your patch gets the same plan.
> >
> In replace_relid_callback(), we add NullTest to rinfo, but it is not a
> logical equal check by restrict_infos_logically_equal().
> I think for baserestrictinfo, we can just use rinfo->clause, no need
> to check the equality of RestrictInfo.
>
> I tried this way, the plan looks as follows:
> QUERY PLAN
> -------------------------------------------------------------------------------------------
> Nested Loop Left Join (cost=0.00..115776846.35 rows=7498006100 width=4)
> -> Seq Scan on pg_table_a tom3 (cost=0.00..34.70 rows=2470 width=0)
> -> Materialize (cost=0.00..100038.47 rows=3035630 width=0)
> -> Nested Loop Left Join (cost=0.00..75966.32 rows=3035630 width=0)
> Join Filter: tom2.col_bool
> -> Seq Scan on pg_table_a tom2 (cost=0.00..34.70
> rows=2458 width=5)
> Filter: (col_bool IS NOT NULL)
> -> Materialize (cost=0.00..47.05 rows=2470 width=0)
> -> Seq Scan on pg_table_a tom0
> (cost=0.00..34.70 rows=2470 width=0)
> (9 rows)
>
> No redundant filter anymore.
>
> Please see the attached patch.
What about being more generic and call ChangeVarNodes_walker() for the
node in ChangeVarNodesWalkExpression()? It also works with out case
and avoids code duplication.
Changes in restrict_infos_logically_equal() makes me a bit uneasy. I
see, restictinfo's are different by their outer_relids. Why
outer_relids doesn't matter when required_relids is singleton? More
general when do outer_relids matter for add_non_redundant_clauses() if
we're putting restictinfo's into a single list anyway?
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] 0001-Fix-sje-bug-v2.patch (1.3K, 2-0001-Fix-sje-bug-v2.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..4b9b5d563f6 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,14 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON)
+ result = equal(a->clause, b->clause);
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..2ad663c952a 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,6 +744,12 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
+ if (node == NULL)
+ return false;
+
+ if (ChangeVarNodes_walker(node, context))
+ return true;
+
return expression_tree_walker(node,
ChangeVarNodes_walker,
(void *) context);
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-18 09:46 ` Tender Wang <tndrwang@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Tender Wang @ 2026-03-18 09:46 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Alexander Korotkov <aekorotkov@gmail.com> 于2026年3月18日周三 16:40写道：
>
> What about being more generic and call ChangeVarNodes_walker() for the
> node in ChangeVarNodesWalkExpression()? It also works with out case
> and avoids code duplication.
Works for me.
>
> Changes in restrict_infos_logically_equal() makes me a bit uneasy. I
> see, restictinfo's are different by their outer_relids. Why
> outer_relids doesn't matter when required_relids is singleton? More
> general when do outer_relids matter for add_non_redundant_clauses() if
> we're putting restictinfo's into a single list anyway?
(gdb) call nodeToString(binfo_candidates )
{RESTRICTINFO :clause {NULLTEST :arg {VAR :varno 3 :varattno 2
:vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b)
:varlevelsup 0 :varreturningtype 0 :varnosyn 3 :varattnosyn 2
:location -1} :nulltesttype 1 :argisrow false :location -1}
:is_pushed_down true :can_join false :pseudoconstant false :has_clone
false :is_clone false :leakproof false :has_volatile 0 :security_level
0 :num_base_rels 1 :clause_relids (b 3) :required_relids (b 3)
:incompatible_relids (b) :outer_relids (b 5) :left_relids (b)
:right_relids (b) :orclause <> :rinfo_serial 3 :eval_cost.startup -1
:eval_cost.per_tuple 0 :norm_selec -1 :outer_selec -1 :mergeopfamilies
<> :left_em <> :right_em <> :outer_is_left false :hashjoinoperator 0
:left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1
:right_mcvfreq -1 :left_hasheqoperator 0 :right_hasheqoperator 0}
{RESTRICTINFO :clause {NULLTEST :arg {VAR :varno 3 :varattno 2
:vartype 16 :vartypmod -1 :varcollid 0 :varnullingrels (b)
:varlevelsup 0 :varreturningtype 0 :varnosyn 3 :varattnosyn 2
:location -1} :nulltesttype 1 :argisrow false :location -1}
:is_pushed_down true :can_join true :pseudoconstant false :has_clone
false :is_clone false :leakproof false :has_volatile 2 :security_level
0 :num_base_rels 1 :clause_relids (b 3) :required_relids (b 3)
:incompatible_relids (b) :outer_relids (b) :left_relids (b 3)
:right_relids (b 3) :orclause <> :rinfo_serial 6 :eval_cost.startup -1
:eval_cost.per_tuple 0 :norm_selec -1 :outer_selec -1 :mergeopfamilies
<> :left_em <> :right_em <> :outer_is_left true :hashjoinoperator 91
:left_bucketsize -1 :right_bucketsize -1 :left_mcvfreq -1
:right_mcvfreq -1 :left_hasheqoperator 91 :right_hasheqoperator 91})"
The first is tom1.col_bool IS NOT NULL, its outer_relids (b 5) is
not empty because it's in the nullable side.
The second is added in replace_relid_callback(). Some fields do not match.
In this case, tom1.col_bool IS NOT NULL becomes a filter clause, not a
join clause, and is safe to only check rinfo->clause.
I think it's better to add is_pushed_down == true check, for example:
...
- if (bms_membership(a->required_relids) == BMS_SINGLETON)
+ if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+ a->is_pushed_down &&
+ b->is_pushed_down)
...
Any thought?
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-18 12:18 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Andrei Lepikhov @ 2026-03-18 12:18 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On 18/3/26 09:40, Alexander Korotkov wrote:
> What about being more generic and call ChangeVarNodes_walker() for the
> node in ChangeVarNodesWalkExpression()? It also works with out case
> and avoids code duplication.
I’ve reached the same conclusion. We lost a possible case when the
RestrictInfo→clause contains a bare Var that isn’t pushed into either
the left or right subtree.
I think we can fix this by replacing the expression walker with
ChangeVarNodes_walker().
What is the reason for the second change? Tender, can you show us how to
reproduce the issue so we can support your update to
restrict_infos_logically_equal? If we include it, we should add a test.
--
regards, Andrei Lepikhov,
pgEdge
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
Attachments:
[text/plain] fix.diff (541B, 2-fix.diff)
download | inline diff:
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
@ 2026-03-18 12:21 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-18 12:21 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>
> On 18/3/26 09:40, Alexander Korotkov wrote:
> > What about being more generic and call ChangeVarNodes_walker() for the
> > node in ChangeVarNodesWalkExpression()? It also works with out case
> > and avoids code duplication.
> I’ve reached the same conclusion. We lost a possible case when the
> RestrictInfo→clause contains a bare Var that isn’t pushed into either
> the left or right subtree.
> I think we can fix this by replacing the expression walker with
> ChangeVarNodes_walker().
I see. This is even better.
> What is the reason for the second change? Tender, can you show us how to
> reproduce the issue so we can support your update to
> restrict_infos_logically_equal? If we include it, we should add a test.
I think Tender already shown this in [1]. The same qual is present
twice in the plan.
Links.
1. https://www.postgresql.org/message-id/CAHewXN%3D7kDJjUcgEm%2B6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg%40mail...
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-18 13:31 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 14:38 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Andrei Lepikhov @ 2026-03-18 13:31 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On 18/3/26 13:21, Alexander Korotkov wrote:
> On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> What is the reason for the second change? Tender, can you show us how to
>> reproduce the issue so we can support your update to
>> restrict_infos_logically_equal? If we include it, we should add a test.
>
> I think Tender already shown this in [1]. The same qual is present
> twice in the plan.
Got it. I suggest making this a separate commit to keep the history
clear. Let me share a draft with a test case for the bug fix first.
--
regards, Andrei Lepikhov,
pgEdge
From 86e65e16c76fe404cb5a16dd0d89a583da9b62e6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Wed, 18 Mar 2026 14:23:46 +0100
Subject: [PATCH] Fix self-join removal to update bare Var references in join
clauses
Self-join removal failed to update Var nodes when the join clause was a
bare Var (e.g., ON t1.bool_col) rather than an expression containing
Vars. ChangeVarNodesWalkExpression() used expression_tree_walker(),
which descends into child nodes but does not process the top-level node
itself. When a bare Var referencing the removed relation appeared as
the clause, its varno was left unchanged, leading to "no relation entry
for relid N" errors.
Fix by calling ChangeVarNodes_walker() directly instead of
expression_tree_walker(), so the top-level node is also processed.
Bug: #19435
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/rewrite/rewriteManip.c | 4 +---
src/test/regress/expected/join.out | 16 ++++++++++++++++
src/test/regress/sql/join.sql | 6 ++++++
3 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ea3dabff77c..bf758f881ac 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8091,6 +8091,22 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop Left Join
+ Join Filter: sl3.cool_bool
+ -> Seq Scan on sl sl3
+ Filter: (cool_bool AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (cool_bool IS NOT NULL))
+ -> Seq Scan on sl sl4
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ad90c326c00..faf07f7c4ab 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3156,6 +3156,12 @@ SELECT * FROM
(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
Attachments:
[text/plain] 0001-Fix-self-join-removal-to-update-bare-Var-references-.patch (3.9K, 2-0001-Fix-self-join-removal-to-update-bare-Var-references-.patch)
download | inline diff:
From 86e65e16c76fe404cb5a16dd0d89a583da9b62e6 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Wed, 18 Mar 2026 14:23:46 +0100
Subject: [PATCH] Fix self-join removal to update bare Var references in join
clauses
Self-join removal failed to update Var nodes when the join clause was a
bare Var (e.g., ON t1.bool_col) rather than an expression containing
Vars. ChangeVarNodesWalkExpression() used expression_tree_walker(),
which descends into child nodes but does not process the top-level node
itself. When a bare Var referencing the removed relation appeared as
the clause, its varno was left unchanged, leading to "no relation entry
for relid N" errors.
Fix by calling ChangeVarNodes_walker() directly instead of
expression_tree_walker(), so the top-level node is also processed.
Bug: #19435
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/rewrite/rewriteManip.c | 4 +---
src/test/regress/expected/join.out | 16 ++++++++++++++++
src/test/regress/sql/join.sql | 6 ++++++
3 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index fe89754a73c..610edf10284 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ea3dabff77c..bf758f881ac 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8091,6 +8091,22 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop Left Join
+ Join Filter: sl3.cool_bool
+ -> Seq Scan on sl sl3
+ Filter: (cool_bool AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (cool_bool IS NOT NULL))
+ -> Seq Scan on sl sl4
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ad90c326c00..faf07f7c4ab 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3156,6 +3156,12 @@ SELECT * FROM
(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
+-- BUG #19435 Do not forget to replace relid in bare Var join clause
+ALTER TABLE sl ADD COLUMN cool_bool boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.cool_bool LEFT JOIN sl AS sl4 ON sl2.cool_bool;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
@ 2026-03-18 14:38 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-19 01:11 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-18 14:38 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Wed, Mar 18, 2026 at 3:31 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> On 18/3/26 13:21, Alexander Korotkov wrote:
> > On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> >> What is the reason for the second change? Tender, can you show us how to
> >> reproduce the issue so we can support your update to
> >> restrict_infos_logically_equal? If we include it, we should add a test.
> >
> > I think Tender already shown this in [1]. The same qual is present
> > twice in the plan.
> Got it. I suggest making this a separate commit to keep the history
> clear. Let me share a draft with a test case for the bug fix first.
Yes, I was also thinking about splitting this into two distinct
commit. The patch you've attached looks good for me. I'm going to
push and backpatch it if no objections. And let's continue the
investigation on restrict_infos_logically_equal().
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 14:38 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-19 01:11 ` Tender Wang <tndrwang@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: Tender Wang @ 2026-03-19 01:11 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Alexander Korotkov <aekorotkov@gmail.com> 于2026年3月18日周三 22:38写道：
>
> On Wed, Mar 18, 2026 at 3:31 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> > On 18/3/26 13:21, Alexander Korotkov wrote:
> > > On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> > >> What is the reason for the second change? Tender, can you show us how to
> > >> reproduce the issue so we can support your update to
> > >> restrict_infos_logically_equal? If we include it, we should add a test.
> > >
> > > I think Tender already shown this in [1]. The same qual is present
> > > twice in the plan.
> > Got it. I suggest making this a separate commit to keep the history
> > clear. Let me share a draft with a test case for the bug fix first.
>
> Yes, I was also thinking about splitting this into two distinct
> commit. The patch you've attached looks good for me. I'm going to
> push and backpatch it if no objections. And let's continue the
> investigation on restrict_infos_logically_equal().
Agree.
And the patch seems to have forgotten to add "Reported by".
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
@ 2026-03-18 17:55 ` Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Kirill Reshke @ 2026-03-18 17:55 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Wed, 18 Mar 2026 at 18:31, Andrei Lepikhov <lepihov@gmail.com> wrote:
>
> On 18/3/26 13:21, Alexander Korotkov wrote:
> > On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> >> What is the reason for the second change? Tender, can you show us how to
> >> reproduce the issue so we can support your update to
> >> restrict_infos_logically_equal? If we include it, we should add a test.
> >
> > I think Tender already shown this in [1]. The same qual is present
> > twice in the plan.
> Got it. I suggest making this a separate commit to keep the history
> clear. Let me share a draft with a test case for the bug fix first.
>
> --
> regards, Andrei Lepikhov,
> pgEdge
Hi!
Is `cool_bool` a typo of `col_bool` in regression test ?
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
@ 2026-03-19 07:18 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Andrei Lepikhov @ 2026-03-19 07:18 UTC (permalink / raw)
To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On 18/3/26 18:55, Kirill Reshke wrote:
> On Wed, 18 Mar 2026 at 18:31, Andrei Lepikhov <lepihov@gmail.com> wrote:
>>
>> On 18/3/26 13:21, Alexander Korotkov wrote:
>>> On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
>>>> What is the reason for the second change? Tender, can you show us how to
>>>> reproduce the issue so we can support your update to
>>>> restrict_infos_logically_equal? If we include it, we should add a test.
>>>
>>> I think Tender already shown this in [1]. The same qual is present
>>> twice in the plan.
>> Got it. I suggest making this a separate commit to keep the history
>> clear. Let me share a draft with a test case for the bug fix first.
> Hi!
> Is `cool_bool` a typo of `col_bool` in regression test ?
This is actually a happy coincidence, not a typo. We made this mistake
during initial development because I didn't realise that a boolean
operation on a bool variable is never wrapped in an expression
structure. It's a 'cool' example that shows a rare edge case.
> And the patch seems to have forgotten to add "Reported by".
Yeah, let the committer manage the award part.
--
regards, Andrei Lepikhov,
pgEdge
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
@ 2026-03-19 13:03 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-19 13:03 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Mar 19, 2026 at 9:18 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
> On 18/3/26 18:55, Kirill Reshke wrote:
> > On Wed, 18 Mar 2026 at 18:31, Andrei Lepikhov <lepihov@gmail.com> wrote:
> >>
> >> On 18/3/26 13:21, Alexander Korotkov wrote:
> >>> On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> >>>> What is the reason for the second change? Tender, can you show us how to
> >>>> reproduce the issue so we can support your update to
> >>>> restrict_infos_logically_equal? If we include it, we should add a test.
> >>>
> >>> I think Tender already shown this in [1]. The same qual is present
> >>> twice in the plan.
> >> Got it. I suggest making this a separate commit to keep the history
> >> clear. Let me share a draft with a test case for the bug fix first.
> > Hi!
> > Is `cool_bool` a typo of `col_bool` in regression test ?
>
> This is actually a happy coincidence, not a typo. We made this mistake
> during initial development because I didn't realise that a boolean
> operation on a bool variable is never wrapped in an expression
> structure. It's a 'cool' example that shows a rare edge case.
>
> > And the patch seems to have forgotten to add "Reported by".
>
> Yeah, let the committer manage the award part.
I've revised the patch. Renamed cool_bool to cool_col, added
"Reported by", and revised authors list according to my opinion.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v2-0001-Fix-self-join-removal-to-update-bare-Var-referenc.patch (3.9K, 2-v2-0001-Fix-self-join-removal-to-update-bare-Var-referenc.patch)
download | inline diff:
From 482ecc12363230b96ccf4fa39fd2fcc2b77b486a Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Wed, 18 Mar 2026 14:23:46 +0100
Subject: [PATCH v2] Fix self-join removal to update bare Var references in
join clauses
Self-join removal failed to update Var nodes when the join clause was a
bare Var (e.g., ON t1.bool_col) rather than an expression containing
Vars. ChangeVarNodesWalkExpression() used expression_tree_walker(),
which descends into child nodes but does not process the top-level node
itself. When a bare Var referencing the removed relation appeared as
the clause, its varno was left unchanged, leading to "no relation entry
for relid N" errors.
Fix by calling ChangeVarNodes_walker() directly instead of
expression_tree_walker(), so the top-level node is also processed.
Bug: #19435
Reported-by: Hang Ammmkilo <ammmkilo@163.com>
Author: Andrei Lepikhov <lepihov@gmail.com>
Co-authored-by: Tender Wang <tndrwang@gmail.com>
Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/rewrite/rewriteManip.c | 4 +---
src/test/regress/expected/join.out | 16 ++++++++++++++++
src/test/regress/sql/join.sql | 6 ++++++
3 files changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index 5282f60e531..7249ffbfb36 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -744,9 +744,7 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
{
- return expression_tree_walker(node,
- ChangeVarNodes_walker,
- (void *) context);
+ return ChangeVarNodes_walker(node, context);
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index ea3dabff77c..dc5b798c336 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8091,6 +8091,22 @@ WHERE q0.a = 1;
-> Seq Scan on sj n1
(7 rows)
+-- Do not forget to replace relid in bare Var join clause (bug #19435)
+ALTER TABLE sl ADD COLUMN bool_col boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop Left Join
+ Join Filter: sl3.bool_col
+ -> Seq Scan on sl sl3
+ Filter: (bool_col AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+ -> Seq Scan on sl sl4
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ad90c326c00..f5631bf97ff 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3156,6 +3156,12 @@ SELECT * FROM
(SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a) q0, sl
WHERE q0.a = 1;
+-- Do not forget to replace relid in bare Var join clause (bug #19435)
+ALTER TABLE sl ADD COLUMN bool_col boolean;
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
+ ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-20 14:02 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
2026-03-26 19:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-20 14:02 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Mar 19, 2026 at 3:03 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Thu, Mar 19, 2026 at 9:18 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
> > On 18/3/26 18:55, Kirill Reshke wrote:
> > > On Wed, 18 Mar 2026 at 18:31, Andrei Lepikhov <lepihov@gmail.com> wrote:
> > >>
> > >> On 18/3/26 13:21, Alexander Korotkov wrote:
> > >>> On Wed, Mar 18, 2026 at 2:18 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> > >>>> What is the reason for the second change? Tender, can you show us how to
> > >>>> reproduce the issue so we can support your update to
> > >>>> restrict_infos_logically_equal? If we include it, we should add a test.
> > >>>
> > >>> I think Tender already shown this in [1]. The same qual is present
> > >>> twice in the plan.
> > >> Got it. I suggest making this a separate commit to keep the history
> > >> clear. Let me share a draft with a test case for the bug fix first.
> > > Hi!
> > > Is `cool_bool` a typo of `col_bool` in regression test ?
> >
> > This is actually a happy coincidence, not a typo. We made this mistake
> > during initial development because I didn't realise that a boolean
> > operation on a bool variable is never wrapped in an expression
> > structure. It's a 'cool' example that shows a rare edge case.
> >
> > > And the patch seems to have forgotten to add "Reported by".
> >
> > Yeah, let the committer manage the award part.
>
> I've revised the patch. Renamed cool_bool to cool_col, added
> "Reported by", and revised authors list according to my opinion.
OK. I've pushed this. Let's go back to
restrict_infos_logically_equal(). I'm still not convinced that we
need to check if required_relids is singleton. Why we can ignore
outer_relids for singleton, but can't do if, for instance, two
relations involved?
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-20 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-20 20:26 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2026-03-20 14:30 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Alexander Korotkov <aekorotkov@gmail.com> writes:
> OK. I've pushed this.
I don't love this patch. It fixes the functional problem, but it
does nothing to fix the underlying cause of that problem, namely
the abysmal under-documentation of the ChangeVarNodesXXX functions.
There are very specific assumptions about whether the recursion
is starting at a Query or not, and the code will do the wrong
thing if invoked at the wrong node level.
In particular, imagine that ChangeVarNodes_walker is invoked
directly on a Query node, something that ChangeVarNodesExtended
is careful not to do. It will increment sublevels_up immediately
and thus process the contents of the Query with sublevels_up==1,
meaning it will not recognize local Vars as needing adjustment.
At the very least we need to add comments, but I wonder if we
don't actually need an Assert that ChangeVarNodesWalkExpression
is not invoked directly on a Query. It would have done the
right thing before this patch, but now it won't. That's an
okay tradeoff for fixing the bare-Var case, but not documenting
what you did is not okay.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-20 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-22 05:15 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
2026-03-23 21:28 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Tom Lane @ 2026-03-20 20:26 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
I wrote:
> At the very least we need to add comments, but I wonder if we
> don't actually need an Assert that ChangeVarNodesWalkExpression
> is not invoked directly on a Query. It would have done the
> right thing before this patch, but now it won't. That's an
> okay tradeoff for fixing the bare-Var case, but not documenting
> what you did is not okay.
After further contemplation I've decided that an Assert would be
wrong, because it's not impossible that a callback would want
to invoke this on a sub-Query --- for instance, if it wanted to
short-circuit ChangeVarNodes's processing of a SubLink node,
it would need to do that. The key point is that if we do see a
Query node here, we will treat it as a sub-query not a top-level
query, which also justifies skipping the work that
ChangeVarNodesExtended does on a top-level Query. So we just
need a comment explaining that. I'm thinking about the attached.
(BTW, by this reasoning the previous implementation of
ChangeVarNodesWalkExpression was doubly wrong, since it would
have done the wrong thing at a Query node as well as a Var node.)
regards, tom lane
Attachments:
[text/x-diff] improve-ChangeVarNodesWalkExpression-comments.patch (1.6K, 2-improve-ChangeVarNodesWalkExpression-comments.patch)
download | inline diff:
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index 7249ffbfb36..dc803a17037 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -672,7 +672,7 @@ ChangeVarNodes_walker(Node *node, ChangeVarNodes_context *context)
* value indicating if the given node should be skipped from further processing
* by ChangeVarNodes_walker. The callback is called only for expressions and
* other children nodes of a Query processed by a walker. Initial processing
- * of the root Query doesn't involve the callback.
+ * of the root Query node doesn't invoke the callback.
*/
void
ChangeVarNodesExtended(Node *node, int rt_index, int new_index,
@@ -737,9 +737,16 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
}
/*
- * ChangeVarNodesWalkExpression - process expression within the custom
- * callback provided to the
- * ChangeVarNodesExtended.
+ * ChangeVarNodesWalkExpression - process subexpression within a callback
+ * function passed to ChangeVarNodesExtended.
+ *
+ * This is intended to be used by a callback that needs to recursively
+ * process subexpressions of some node being visited by an outer
+ * ChangeVarNodesExtended call (not letting ChangeVarNodes_walker do that).
+ * Hence, we invoke ChangeVarNodes_walker directly. This means that if
+ * the passed Node is a Query node, it will be treated as a sub-Query,
+ * so sublevels_up will be incremented immediately. Do not apply this
+ * to a top-level Query node, or you'll likely get wrong results.
*/
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
2026-03-20 20:26 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-22 05:15 ` Tender Wang <tndrwang@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Tender Wang @ 2026-03-22 05:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Hi Tom,
Tom Lane <tgl@sss.pgh.pa.us> 于2026年3月21日周六 04:26写道：
>
> I wrote:
> > At the very least we need to add comments, but I wonder if we
> > don't actually need an Assert that ChangeVarNodesWalkExpression
> > is not invoked directly on a Query. It would have done the
> > right thing before this patch, but now it won't. That's an
> > okay tradeoff for fixing the bare-Var case, but not documenting
> > what you did is not okay.
>
> After further contemplation I've decided that an Assert would be
> wrong, because it's not impossible that a callback would want
> to invoke this on a sub-Query --- for instance, if it wanted to
> short-circuit ChangeVarNodes's processing of a SubLink node,
> it would need to do that. The key point is that if we do see a
> Query node here, we will treat it as a sub-query not a top-level
> query, which also justifies skipping the work that
> ChangeVarNodesExtended does on a top-level Query. So we just
> need a comment explaining that. I'm thinking about the attached.
>
> (BTW, by this reasoning the previous implementation of
> ChangeVarNodesWalkExpression was doubly wrong, since it would
> have done the wrong thing at a Query node as well as a Var node.)
Thanks for pointing this out. The attached looks good to me.
Do you have some advice about that the same qual is present
twice in the plan, see [1].
Should we do something in restrict_infos_logically_equal().
Please take a look.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
2026-03-20 20:26 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-23 21:28 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-23 21:34 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2026-03-23 21:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Hi, Tom!
On Fri, Mar 20, 2026 at 10:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I wrote:
> > At the very least we need to add comments, but I wonder if we
> > don't actually need an Assert that ChangeVarNodesWalkExpression
> > is not invoked directly on a Query. It would have done the
> > right thing before this patch, but now it won't. That's an
> > okay tradeoff for fixing the bare-Var case, but not documenting
> > what you did is not okay.
>
> After further contemplation I've decided that an Assert would be
> wrong, because it's not impossible that a callback would want
> to invoke this on a sub-Query --- for instance, if it wanted to
> short-circuit ChangeVarNodes's processing of a SubLink node,
> it would need to do that. The key point is that if we do see a
> Query node here, we will treat it as a sub-query not a top-level
> query, which also justifies skipping the work that
> ChangeVarNodesExtended does on a top-level Query. So we just
> need a comment explaining that. I'm thinking about the attached.
>
> (BTW, by this reasoning the previous implementation of
> ChangeVarNodesWalkExpression was doubly wrong, since it would
> have done the wrong thing at a Query node as well as a Var node.)
Thank you so much for caring about this. I agree that this kind of
changes should go with proper comments.
Please, consider my additions to the comment. They explain why we use
ChangeVarNodes_walker() instead of expression_tree_walker(), and gives
a bit more details about difference in processing of top-level Query
and subquery.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] further-improve-ChangeVarNodesWalkExpression-comments.patch (1.6K, 2-further-improve-ChangeVarNodesWalkExpression-comments.patch)
download | inline diff:
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index dc803a17037..0c6c63aebe5 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -742,11 +742,17 @@ ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
*
* This is intended to be used by a callback that needs to recursively
* process subexpressions of some node being visited by an outer
- * ChangeVarNodesExtended call (not letting ChangeVarNodes_walker do that).
- * Hence, we invoke ChangeVarNodes_walker directly. This means that if
- * the passed Node is a Query node, it will be treated as a sub-Query,
- * so sublevels_up will be incremented immediately. Do not apply this
- * to a top-level Query node, or you'll likely get wrong results.
+ * ChangeVarNodesExtended call, instead of relying on ChangeVarNodes_walker's
+ * default recursion. We invoke ChangeVarNodes_walker directly rather than
+ * expression_tree_walker, because expression_tree_walker only visits child
+ * nodes and would fail to process the passed node itself --- for example,
+ * a bare Var node would not get its varno adjusted.
+ *
+ * Because this calls ChangeVarNodes_walker directly, if the passed node is
+ * a Query, it will be treated as a sub-Query: sublevels_up is incremented
+ * before recursing into it, and Query-level fields (resultRelation,
+ * mergeTargetRelation, rowMarks, etc.) will not be adjusted. Do not apply
+ * this to a top-level Query node; use ChangeVarNodesExtended for that.
*/
bool
ChangeVarNodesWalkExpression(Node *node, ChangeVarNodes_context *context)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
2026-03-20 20:26 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tom Lane <tgl@sss.pgh.pa.us>
2026-03-23 21:28 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-23 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2026-03-23 21:34 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Alexander Korotkov <aekorotkov@gmail.com> writes:
> Please, consider my additions to the comment. They explain why we use
> ChangeVarNodes_walker() instead of expression_tree_walker(), and gives
> a bit more details about difference in processing of top-level Query
> and subquery.
I'm fine with this, although in
+ * ... We invoke ChangeVarNodes_walker directly rather than
+ * expression_tree_walker
I would write "rather than via expression_tree_walker".
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-03-26 19:59 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-27 01:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Andrei Lepikhov @ 2026-03-26 19:59 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Tender Wang <tndrwang@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On 20/3/26 15:02, Alexander Korotkov wrote:
> OK. I've pushed this. Let's go back to
> restrict_infos_logically_equal(). I'm still not convinced that we
> need to check if required_relids is singleton. Why we can ignore
> outer_relids for singleton, but can't do if, for instance, two
> relations involved?
Let's continue. In the attachment, the Tender's proposal that I changed
a little and added some tests.
As you can see in the tests, the SINGLETON limitation keeps duplicates
of clauses like 'a.x + b.y = c'.
This example shows the main flaw of this approach. Introducing the
restrict_infos_logically_equal(), we do a little more job than just the
equal() routine could because of the context where we call this function
and on which clauses.
But skipping all other RestrictInfo fields except required_relids seems
excessive. - see the example with security_level difference - ignoring
its value, we potentially might remove the clause with enforced security
level in favour of an unsecured one.
That's more, further some new optimisations might introduce more fields
into RestrictInfo that should be checked to correctly decide on the
equality, and we may forget to arrange this specific place.
So, formally it works, and making the following replacement, we close
the singleton issue:
- if (bms_membership(a->required_relids) == BMS_SINGLETON &&
- a->security_level == b->security_level)
+ if (bms_equal(a->required_relids, b->required_relids) &&
+ a->security_level == b->security_level &&
+ a->is_pushed_down == b->is_pushed_down)
but I'm unsure, in general, that this approach is conservative enough.
Maybe we shouldn’t change this logic and invent one more optimisation
‘deduplication’ stage later, before the selectivity estimation stage.
--
regards, Andrei Lepikhov,
pgEdge
From 01b9dc8b728bef49104fe1652841770eddb0c45c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 26 Mar 2026 12:15:01 +0100
Subject: [PATCH] Improve RestrictInfo deduplication after self-join
elimination
After self-join elimination rewrites varnos, two RestrictInfos can
end up with identical clauses but different metadata (outer_relids,
rinfo_serial). The previous restrict_infos_logically_equal()
compared full RestrictInfo structs, missing these duplicates and
leaving redundant filter conditions in the plan.
For base restrictions (singleton required_relids), compare only the
clause expression, which is sufficient since these are pushed-down
filters that don't carry join-semantic meaning. Retain the full
struct comparison for join clauses (non-singleton required_relids)
to avoid incorrectly merging clauses at different join levels.
Also check security_level to prevent merging an RLS policy qual with a
user-written ON clause that happen to look identical after SJE.
Author: Tender Wang <tndrwang@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://www.postgresql.org/message-id/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/optimizer/plan/analyzejoins.c | 16 ++++-
src/test/regress/expected/join.out | 78 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 48 ++++++++++++++
3 files changed, 139 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..ed35c51a38f 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,19 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+ a->security_level == b->security_level)
+ {
+ Assert(a->is_pushed_down && b->is_pushed_down);
+
+ result = equal(a->clause, b->clause);
+ }
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 84872c6f04e..88766e5e626 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8108,6 +8108,84 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
-> Seq Scan on sl sl4
(7 rows)
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl5
+ -> Nested Loop Left Join
+ Join Filter: sl7.bool_col
+ -> Seq Scan on sl sl7
+ Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl8
+(7 rows)
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop
+ Join Filter: (((sl3.b + sl2.b) IS NOT NULL) AND ((sl3.b + sl2.b) IS NOT NULL))
+ -> Seq Scan on sl sl2
+ -> Seq Scan on sl sl3
+ Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+(7 rows)
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ Filter: (bool_col IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (sl3.bool_col IS NOT NULL)
+ -> Seq Scan on sl sl3
+ Filter: ((bool_col IS NOT NULL) AND (bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl4
+ Filter: (bool_col IS NOT NULL)
+(9 rows)
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 30b479dda7c..864d95694f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3162,6 +3162,54 @@ EXPLAIN (COSTS OFF)
SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
Attachments:
[text/plain] 0001-Improve-RestrictInfo-deduplication-after-self-join-e.patch (8.5K, 2-0001-Improve-RestrictInfo-deduplication-after-self-join-e.patch)
download | inline diff:
From 01b9dc8b728bef49104fe1652841770eddb0c45c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 26 Mar 2026 12:15:01 +0100
Subject: [PATCH] Improve RestrictInfo deduplication after self-join
elimination
After self-join elimination rewrites varnos, two RestrictInfos can
end up with identical clauses but different metadata (outer_relids,
rinfo_serial). The previous restrict_infos_logically_equal()
compared full RestrictInfo structs, missing these duplicates and
leaving redundant filter conditions in the plan.
For base restrictions (singleton required_relids), compare only the
clause expression, which is sufficient since these are pushed-down
filters that don't carry join-semantic meaning. Retain the full
struct comparison for join clauses (non-singleton required_relids)
to avoid incorrectly merging clauses at different join levels.
Also check security_level to prevent merging an RLS policy qual with a
user-written ON clause that happen to look identical after SJE.
Author: Tender Wang <tndrwang@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://www.postgresql.org/message-id/19435-3cc1a87f291129f1%40postgresql.org
---
src/backend/optimizer/plan/analyzejoins.c | 16 ++++-
src/test/regress/expected/join.out | 78 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 48 ++++++++++++++
3 files changed, 139 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..ed35c51a38f 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,19 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
int saved_rinfo_serial = a->rinfo_serial;
bool result;
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
+ if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+ a->security_level == b->security_level)
+ {
+ Assert(a->is_pushed_down && b->is_pushed_down);
+
+ result = equal(a->clause, b->clause);
+ }
+ else
+ {
+ a->rinfo_serial = b->rinfo_serial;
+ result = equal(a, b);
+ a->rinfo_serial = saved_rinfo_serial;
+ }
return result;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 84872c6f04e..88766e5e626 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8108,6 +8108,84 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
-> Seq Scan on sl sl4
(7 rows)
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl5
+ -> Nested Loop Left Join
+ Join Filter: sl7.bool_col
+ -> Seq Scan on sl sl7
+ Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl8
+(7 rows)
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ -> Nested Loop
+ Join Filter: (((sl3.b + sl2.b) IS NOT NULL) AND ((sl3.b + sl2.b) IS NOT NULL))
+ -> Seq Scan on sl sl2
+ -> Seq Scan on sl sl3
+ Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+(7 rows)
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl1
+ Filter: (bool_col IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (sl3.bool_col IS NOT NULL)
+ -> Seq Scan on sl sl3
+ Filter: ((bool_col IS NOT NULL) AND (bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl4
+ Filter: (bool_col IS NOT NULL)
+(9 rows)
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 30b479dda7c..864d95694f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3162,6 +3162,54 @@ EXPLAIN (COSTS OFF)
SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause. Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+ ON sl6.bool_col IS NOT NULL
+ LEFT JOIN sl AS sl8
+ ON sl6.bool_col;
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN sl sl2
+ LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+ ON (sl4.b + sl2.b) IS NOT NULL
+ ON (sl3.b + sl2.b) IS NOT NULL;
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+ sl sl1 LEFT JOIN (
+ (sl AS sl2 NATURAL JOIN sl AS sl3)
+ LEFT JOIN sl sl4
+ ON sl2.bool_col IS NOT NULL
+ ) ON sl2.bool_col IS NOT NULL;
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.51.0
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-26 19:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
@ 2026-03-27 01:19 ` Tender Wang <tndrwang@gmail.com>
2026-04-22 15:10 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tender Wang @ 2026-03-27 01:19 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Andrei Lepikhov <lepihov@gmail.com> 于2026年3月27日周五 03:59写道：
>
> On 20/3/26 15:02, Alexander Korotkov wrote:
> > OK. I've pushed this. Let's go back to
> > restrict_infos_logically_equal(). I'm still not convinced that we
> > need to check if required_relids is singleton. Why we can ignore
> > outer_relids for singleton, but can't do if, for instance, two
> > relations involved?
>
> Let's continue. In the attachment, the Tender's proposal that I changed
> a little and added some tests.
>
> As you can see in the tests, the SINGLETON limitation keeps duplicates
> of clauses like 'a.x + b.y = c'.
> This example shows the main flaw of this approach. Introducing the
> restrict_infos_logically_equal(), we do a little more job than just the
> equal() routine could because of the context where we call this function
> and on which clauses.
> But skipping all other RestrictInfo fields except required_relids seems
> excessive. - see the example with security_level difference - ignoring
> its value, we potentially might remove the clause with enforced security
> level in favour of an unsecured one.
Yes, it seems too strict to require all fields to be equal, but
skipping some fields is unsafe.
> That's more, further some new optimisations might introduce more fields
> into RestrictInfo that should be checked to correctly decide on the
> equality, and we may forget to arrange this specific place.
>
Agree.
> So, formally it works, and making the following replacement, we close
> the singleton issue:
>
> - if (bms_membership(a->required_relids) == BMS_SINGLETON &&
> - a->security_level == b->security_level)
> + if (bms_equal(a->required_relids, b->required_relids) &&
> + a->security_level == b->security_level &&
> + a->is_pushed_down == b->is_pushed_down)
>
The singleton issue does not seem to be the correct way; I don't dive
deeply to cover all cases.
> but I'm unsure, in general, that this approach is conservative enough.
> Maybe we shouldn’t change this logic and invent one more optimisation
> ‘deduplication’ stage later, before the selectivity estimation stage.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-26 19:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-27 01:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
@ 2026-04-22 15:10 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-04-23 08:26 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
2026-05-22 10:16 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Alexander Korotkov @ 2026-04-22 15:10 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On Fri, Mar 27, 2026 at 3:19 AM Tender Wang <tndrwang@gmail.com> wrote:
> Andrei Lepikhov <lepihov@gmail.com> 于2026年3月27日周五 03:59写道：
> >
> > On 20/3/26 15:02, Alexander Korotkov wrote:
> > > OK. I've pushed this. Let's go back to
> > > restrict_infos_logically_equal(). I'm still not convinced that we
> > > need to check if required_relids is singleton. Why we can ignore
> > > outer_relids for singleton, but can't do if, for instance, two
> > > relations involved?
> >
> > Let's continue. In the attachment, the Tender's proposal that I changed
> > a little and added some tests.
> >
> > As you can see in the tests, the SINGLETON limitation keeps duplicates
> > of clauses like 'a.x + b.y = c'.
> > This example shows the main flaw of this approach. Introducing the
> > restrict_infos_logically_equal(), we do a little more job than just the
> > equal() routine could because of the context where we call this function
> > and on which clauses.
> > But skipping all other RestrictInfo fields except required_relids seems
> > excessive. - see the example with security_level difference - ignoring
> > its value, we potentially might remove the clause with enforced security
> > level in favour of an unsecured one.
>
> Yes, it seems too strict to require all fields to be equal, but
> skipping some fields is unsafe.
>
>
> > That's more, further some new optimisations might introduce more fields
> > into RestrictInfo that should be checked to correctly decide on the
> > equality, and we may forget to arrange this specific place.
> >
>
> Agree.
>
> > So, formally it works, and making the following replacement, we close
> > the singleton issue:
> >
> > - if (bms_membership(a->required_relids) == BMS_SINGLETON &&
> > - a->security_level == b->security_level)
> > + if (bms_equal(a->required_relids, b->required_relids) &&
> > + a->security_level == b->security_level &&
> > + a->is_pushed_down == b->is_pushed_down)
> >
>
> The singleton issue does not seem to be the correct way; I don't dive
> deeply to cover all cases.
>
> > but I'm unsure, in general, that this approach is conservative enough.
> > Maybe we shouldn’t change this logic and invent one more optimisation
> > ‘deduplication’ stage later, before the selectivity estimation stage.
I have another approach about to deduplication of RestrictInfo's. The
field, which differs in this case, is outer_relids. AFAICS,
outer_relids and incompatible_relids serves as the restriction on what
we can do with RestrictInfo. So, what we can do is to ignore both
outer_relids and incompatible_relids during comparison, but compose a
union of their values for remaining RestrictInfo. That means that
remaining RestrictInfo will ancest all the restrictions, and that
should be safe.
What do you think?
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v1-0001-Deduplicate-RestrictInfos-differing-only-in-outer.patch (7.9K, 2-v1-0001-Deduplicate-RestrictInfos-differing-only-in-outer.patch)
download | inline diff:
From cad833802bc1a31c06fd80c3cded9fb6f5abbe19 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed, 22 Apr 2026 17:28:23 +0300
Subject: [PATCH v1] Deduplicate RestrictInfos differing only in outer_relids
in SJE
During self-join elimination, an EC-derived IS NOT NULL clause from an
inner join and an identical clause originating from an enclosing outer
join's ON clause may both be moved to the surviving relation. They
are logically equivalent but carry different outer_relids /
incompatible_relids (placement constraints, not semantic content), so
restrict_infos_logically_equal() used to keep both, producing a
duplicated filter in the plan.
Compare only the fields that describe the filter's semantics and
placement level (clause, clause_relids, required_relids,
security_level, is_pushed_down, has_clone, is_clone). When a
duplicate is found, merge outer_relids and incompatible_relids of the
two RestrictInfos by union into the surviving one, so the kept clause
carries the strictest placement allowed by either original.
Bug: #19435
---
src/backend/optimizer/plan/analyzejoins.c | 68 +++++++++++++++++------
src/test/regress/expected/join.out | 20 +++++++
src/test/regress/sql/join.sql | 10 ++++
3 files changed, 82 insertions(+), 16 deletions(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 03056bdf3e0..65e71519b30 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1651,25 +1651,44 @@ update_eclasses(EquivalenceClass *ec, int from, int to)
}
/*
- * "Logically" compares two RestrictInfo's ignoring the 'rinfo_serial' field,
- * which makes almost every RestrictInfo unique. This type of comparison is
- * useful when removing duplicates while moving RestrictInfo's from removed
- * relation to remaining relation during self-join elimination.
+ * Compare two RestrictInfos for logical equivalence during self-join
+ * elimination dedup. Two clauses are logically equivalent if they apply
+ * the same test at the same semantic placement level, i.e. their clause
+ * content and the fields that determine when/where the filter must run
+ * all match.
*
- * XXX: In the future, we might remove the 'rinfo_serial' field completely and
- * get rid of this function.
+ * outer_relids and incompatible_relids may legitimately differ between
+ * two such clauses: they are placement *constraints* rather than semantic
+ * content, and the same logical filter can arise from different levels of
+ * the join tree (e.g. an EC-derived IS NOT NULL from an inner join vs. an
+ * original ON-clause from an enclosing outer join) with different
+ * constraint sets. Callers must combine these constraints (by union)
+ * when treating one clause as redundant with another, so that the
+ * surviving clause carries the strictest placement allowed by either
+ * original. See add_non_redundant_clauses().
+ *
+ * rinfo_serial is skipped because it is only an identifier. Fields
+ * marked pg_node_attr(equal_ignore) in the struct definition (cached
+ * costs, selectivities, EC back-pointers, derived relid subsets) are
+ * likewise skipped since they are either caches or redundant with fields
+ * that are checked here.
*/
static bool
restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
{
- int saved_rinfo_serial = a->rinfo_serial;
- bool result;
-
- a->rinfo_serial = b->rinfo_serial;
- result = equal(a, b);
- a->rinfo_serial = saved_rinfo_serial;
-
- return result;
+ if (a->security_level != b->security_level)
+ return false;
+ if (a->is_pushed_down != b->is_pushed_down)
+ return false;
+ if (a->has_clone != b->has_clone)
+ return false;
+ if (a->is_clone != b->is_clone)
+ return false;
+ if (!bms_equal(a->clause_relids, b->clause_relids))
+ return false;
+ if (!bms_equal(a->required_relids, b->required_relids))
+ return false;
+ return equal(a->clause, b->clause);
}
/*
@@ -1682,6 +1701,13 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
* would have been better to avoid calling the equal() function here, but
* it's the only way to detect duplicated inequality expressions.
*
+ * When a candidate is found to be logically equivalent to an already-kept
+ * clause but carries different outer_relids / incompatible_relids, those
+ * placement-constraint sets are merged by union into the surviving
+ * clause. The union is the strictest common constraint: any placement
+ * allowed by both originals remains allowed, so keeping only the merged
+ * clause reproduces the same filtering without relaxing any prohibition.
+ *
* (*keep_rinfo_list) is given by pointer because it might be altered by
* distribute_restrictinfo_to_rels().
*/
@@ -1705,9 +1731,19 @@ add_non_redundant_clauses(PlannerInfo *root,
if (src == rinfo ||
(rinfo->parent_ec != NULL &&
- src->parent_ec == rinfo->parent_ec) ||
- restrict_infos_logically_equal(rinfo, src))
+ src->parent_ec == rinfo->parent_ec))
+ {
+ is_redundant = true;
+ break;
+ }
+
+ if (restrict_infos_logically_equal(rinfo, src))
{
+ src->outer_relids = bms_union(src->outer_relids,
+ rinfo->outer_relids);
+ src->incompatible_relids =
+ bms_union(src->incompatible_relids,
+ rinfo->incompatible_relids);
is_redundant = true;
break;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 78bf022f7b4..2635e26afd8 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8147,6 +8147,26 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
-> Seq Scan on sl sl4
(7 rows)
+-- An EC-derived IS NOT NULL (from the NATURAL JOIN's self-join removal) may
+-- collide with an identical IS NOT NULL originating from an enclosing outer
+-- join's ON clause. The two clauses differ only in outer_relids, so they
+-- must be merged (by union) rather than kept as duplicates. Bug #19435.
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM (sl AS sl0 RIGHT JOIN
+ ((sl AS sl1 NATURAL JOIN sl AS sl2)
+ RIGHT JOIN sl AS sl3 ON sl1.bool_col IS NOT NULL)
+ ON sl1.bool_col);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on sl sl3
+ -> Nested Loop Left Join
+ Join Filter: sl2.bool_col
+ -> Seq Scan on sl sl2
+ Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+ -> Seq Scan on sl sl0
+(7 rows)
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index fae19113cef..89c54a6e172 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3185,6 +3185,16 @@ EXPLAIN (COSTS OFF)
SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
+-- An EC-derived IS NOT NULL (from the NATURAL JOIN's self-join removal) may
+-- collide with an identical IS NOT NULL originating from an enclosing outer
+-- join's ON clause. The two clauses differ only in outer_relids, so they
+-- must be merged (by union) rather than kept as duplicates. Bug #19435.
+EXPLAIN (COSTS OFF)
+SELECT 1 AS c1 FROM (sl AS sl0 RIGHT JOIN
+ ((sl AS sl1 NATURAL JOIN sl AS sl2)
+ RIGHT JOIN sl AS sl3 ON sl1.bool_col IS NOT NULL)
+ ON sl1.bool_col);
+
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
-- stay in different special join infos.
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-26 19:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-27 01:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
2026-04-22 15:10 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-04-23 08:26 ` Tender Wang <tndrwang@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Tender Wang @ 2026-04-23 08:26 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrei Lepikhov <lepihov@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
Alexander Korotkov <aekorotkov@gmail.com> 于2026年4月22日周三 23:10写道：
>
> I have another approach about to deduplication of RestrictInfo's. The
> field, which differs in this case, is outer_relids. AFAICS,
> outer_relids and incompatible_relids serves as the restriction on what
> we can do with RestrictInfo. So, what we can do is to ignore both
> outer_relids and incompatible_relids during comparison, but compose a
> union of their values for remaining RestrictInfo. That means that
> remaining RestrictInfo will ancest all the restrictions, and that
> should be safe.
>
> What do you think?
It seems workable and safer than the previous solution.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 12:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 17:55 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-26 19:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Andrei Lepikhov <lepihov@gmail.com>
2026-03-27 01:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <tndrwang@gmail.com>
2026-04-22 15:10 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-05-22 10:16 ` Andrei Lepikhov <lepihov@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: Andrei Lepikhov @ 2026-05-22 10:16 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; ammmkilo@163.com; pgsql-bugs@lists.postgresql.org
On 22/04/2026 17:10, Alexander Korotkov wrote:
> On Fri, Mar 27, 2026 at 3:19 AM Tender Wang <tndrwang@gmail.com> wrote:
>>> but I'm unsure, in general, that this approach is conservative enough.
>>> Maybe we shouldn’t change this logic and invent one more optimisation
>>> ‘deduplication’ stage later, before the selectivity estimation stage.
>
> I have another approach about to deduplication of RestrictInfo's. The
> field, which differs in this case, is outer_relids. AFAICS,
> outer_relids and incompatible_relids serves as the restriction on what
> we can do with RestrictInfo. So, what we can do is to ignore both
> outer_relids and incompatible_relids during comparison, but compose a
> union of their values for remaining RestrictInfo. That means that
> remaining RestrictInfo will ancest all the restrictions, and that
> should be safe.
>
> What do you think?
Thank you for all the work you’ve put into de-duplicating clauses.
I agree that using the union of outer_relids and incompatible_relids is the
strictest common constraint. There shouldn’t be any issues, so this approach
should work.
However, the new function relies on a hand-picked list of "semantic" fields. If
someone adds another field to RestrictInfo, this function could break without
warning unless they remember to update it. We should add comment hooks that say,
"If you add a field here, update analyzejoins.c too."
Also, de-duplication happens in several places. If we change the logic in
add_non_redundant_clauses, maybe we should review the update_eclasses() code as
well.
--
regards, Andrei Lepikhov,
pgEdge
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2026-05-22 10:16 UTC | newest]
Thread overview: 21+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-18 08:40 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 09:46 ` Tender Wang <tndrwang@gmail.com>
2026-03-18 12:18 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 12:21 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-18 13:31 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-18 14:38 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-19 01:11 ` Tender Wang <tndrwang@gmail.com>
2026-03-18 17:55 ` Kirill Reshke <reshkekirill@gmail.com>
2026-03-19 07:18 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-19 13:03 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:02 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-20 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-20 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-22 05:15 ` Tender Wang <tndrwang@gmail.com>
2026-03-23 21:28 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-03-23 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-26 19:59 ` Andrei Lepikhov <lepihov@gmail.com>
2026-03-27 01:19 ` Tender Wang <tndrwang@gmail.com>
2026-04-22 15:10 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-04-23 08:26 ` Tender Wang <tndrwang@gmail.com>
2026-05-22 10:16 ` Andrei Lepikhov <lepihov@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox