public inbox for [email protected]help / color / mirror / Atom feed
Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 9+ messages / 3 participants [nested] [flat]
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables @ 2026-03-17 12:26 Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Alexander Korotkov @ 2026-03-17 12:26 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: [email protected]; [email protected] On Tue, Mar 17, 2026 at 2:14 PM Fujii Masao <[email protected]> wrote: > > On Tue, Mar 17, 2026 at 7:34 PM PG Bug reporting form > <[email protected]> wrote: > > > > The following bug has been logged on the website: > > > > Bug reference: 19435 > > Logged by: Hang ammmkilo > > Email address: [email protected] > > PostgreSQL version: 18.3 > > Operating system: Ubuntu 22.04 > > Description: > > > > A user encountered an error when attempting to execute a query involving > > multiple RIGHT JOIN operations and a NATURAL JOIN on the same table > > (pg_table_a). The error message returned was: > > [XX000]ERROR: no relation entry for relid 2 > > This error seems to be an internal one and should not be triggered by users. > > It might be a bug. > > ```sql > > DROP TABLE IF EXISTS pg_table_a; > > > > CREATE TABLE pg_table_a ( > > id INTEGER PRIMARY KEY, > > col_bool BOOLEAN > > ); > > > > INSERT INTO pg_table_a (id, col_bool) > > VALUES (5, TRUE); > > > > SELECT 1 AS c1 > > FROM ( > > pg_table_a AS tom0 > > RIGHT JOIN ( > > (pg_table_a AS tom1 NATURAL JOIN pg_table_a AS tom2) > > RIGHT JOIN pg_table_a AS tom3 > > ON tom1.col_bool IS NOT NULL > > ) > > ON tom1.col_bool > > ); > > ``` > > Thanks for the report! > > I was able to reproduce this issue on the master. git bisect that I ran pointed > to commit fc069a3a631 as the likely cause. So I've CC'd its committer, > Alexander, on this thread. Thank you for adding me to the thread. I'm lookin at this. ------ Regards, Alexander Korotkov Supabase ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> @ 2026-03-17 12:59 ` Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tender Wang @ 2026-03-17 12:59 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Fujii Masao <[email protected]>; [email protected]; [email protected] Alexander Korotkov <[email protected]> 于2026年3月17日周二 20:26写道： > > On Tue, Mar 17, 2026 at 2:14 PM Fujii Masao <[email protected]> wrote: > > > > On Tue, Mar 17, 2026 at 7:34 PM PG Bug reporting form > > <[email protected]> wrote: > > > > > > The following bug has been logged on the website: > > > > > > Bug reference: 19435 > > > Logged by: Hang ammmkilo > > > Email address: [email protected] > > > PostgreSQL version: 18.3 > > > Operating system: Ubuntu 22.04 > > > Description: > > > > > > A user encountered an error when attempting to execute a query involving > > > multiple RIGHT JOIN operations and a NATURAL JOIN on the same table > > > (pg_table_a). The error message returned was: > > > [XX000]ERROR: no relation entry for relid 2 > > > This error seems to be an internal one and should not be triggered by users. > > > It might be a bug. > > > ```sql > > > DROP TABLE IF EXISTS pg_table_a; > > > > > > CREATE TABLE pg_table_a ( > > > id INTEGER PRIMARY KEY, > > > col_bool BOOLEAN > > > ); > > > > > > INSERT INTO pg_table_a (id, col_bool) > > > VALUES (5, TRUE); > > > > > > SELECT 1 AS c1 > > > FROM ( > > > pg_table_a AS tom0 > > > RIGHT JOIN ( > > > (pg_table_a AS tom1 NATURAL JOIN pg_table_a AS tom2) > > > RIGHT JOIN pg_table_a AS tom3 > > > ON tom1.col_bool IS NOT NULL > > > ) > > > ON tom1.col_bool > > > ); > > > ``` > > > > Thanks for the report! > > > > I was able to reproduce this issue on the master. git bisect that I ran pointed > > to commit fc069a3a631 as the likely cause. So I've CC'd its committer, > > Alexander, on this thread. > > Thank you for adding me to the thread. I'm lookin at this. The error was reported in rebuild_joinclause_attr_needed() when processing Relid = 1(rtindex =1), When processing its joininfo" ON tom1.col_bool IS NOT NULL", (gdb) pgprint rinfo->clause Var [varno=2 varattno=2 vartype=16 varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=2] The varno=2, rtindex=2(tom1) has been removed. In add_vars_to_attr_needed(), to find the base_rel, but the root->simple_rel_array[2] is NULL. So the error is reporting. It seems the joininfo should be replaced by rtindex = 3, because the rtindex=2 would be removed. -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> @ 2026-03-17 13:19 ` Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tender Wang @ 2026-03-17 13:19 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Fujii Masao <[email protected]>; [email protected]; [email protected] Tender Wang <[email protected]> 于2026年3月17日周二 20:59写道： > > The error was reported in rebuild_joinclause_attr_needed() when > processing Relid = 1(rtindex =1), > When processing its joininfo" ON tom1.col_bool IS NOT NULL", > (gdb) pgprint rinfo->clause > Var [varno=2 varattno=2 vartype=16 > varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=2] > > The varno=2, rtindex=2(tom1) has been removed. In > add_vars_to_attr_needed(), to find the base_rel, but the > root->simple_rel_array[2] is NULL. > So the error is reporting. > It seems the joininfo should be replaced by rtindex = 3, because the > rtindex=2 would be removed. > -- (gdb) pgprint rinfo RestrictInfo [is_pushed_down=false can_join=false pseudoconstant=false has_clone=true is_clone=false leakproof=false has_volatile=VOLATILITY_UNKNOWN security_level=0 num_base_rels=1 rinfo_serial=4 eval_cost={startup = -1, per_tuple = 0} norm_selec=-1 outer_selec=-1 outer_is_left=false hashjoinoperator=0 left_bucketsize=-1 right_bucketsize=-1 left_mcvfreq=-1 right_mcvfreq=-1 left_hasheqoperator=0 right_hasheqoperator=0] [clause] Var [varno=2 varattno=2 vartype=16 varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=2] [clause_relids] Bitmapset [3] [required_relids] Bitmapset [3 1] [incompatible_relids] Bitmapset [7 6] [outer_relids] Bitmapset [6 5 3] The above is the joininfo of the rtindex=1(tom0), we can see that the required_relids is changed to [3 1], but the clause is still rtindex=2(varno=2). I guess the current logic in remove_self_join_rel() may forget to process the rinfo->clause. -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> @ 2026-03-17 13:24 ` Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Kirill Reshke @ 2026-03-17 13:24 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> wrote: > > Tender Wang <[email protected]> 于2026年3月17日周二 20:59写道： > > > > The error was reported in rebuild_joinclause_attr_needed() when > > processing Relid = 1(rtindex =1), > > When processing its joininfo" ON tom1.col_bool IS NOT NULL", > > (gdb) pgprint rinfo->clause > > Var [varno=2 varattno=2 vartype=16 > > varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=2] > > > > The varno=2, rtindex=2(tom1) has been removed. In > > add_vars_to_attr_needed(), to find the base_rel, but the > > root->simple_rel_array[2] is NULL. > > So the error is reporting. > > It seems the joininfo should be replaced by rtindex = 3, because the > > rtindex=2 would be removed. > > -- > (gdb) pgprint rinfo > RestrictInfo [is_pushed_down=false can_join=false pseudoconstant=false > has_clone=true is_clone=false leakproof=false > has_volatile=VOLATILITY_UNKNOWN security_level=0 > num_base_rels=1 rinfo_serial=4 eval_cost={startup = -1, > per_tuple = 0} norm_selec=-1 outer_selec=-1 outer_is_left=false > hashjoinoperator=0 left_bucketsize=-1 > right_bucketsize=-1 left_mcvfreq=-1 right_mcvfreq=-1 > left_hasheqoperator=0 right_hasheqoperator=0] > [clause] Var [varno=2 varattno=2 vartype=16 > varreturningtype=VAR_RETURNING_DEFAULT varnosyn=2 varattnosyn=2] > [clause_relids] Bitmapset [3] > [required_relids] Bitmapset [3 1] > [incompatible_relids] Bitmapset [7 6] > [outer_relids] Bitmapset [6 5 3] > > The above is the joininfo of the rtindex=1(tom0), we can see that the > required_relids is changed to [3 1], but the clause is still > rtindex=2(varno=2). > I guess the current logic in remove_self_join_rel() may forget to > process the rinfo->clause. Yes, it looks like your analysis is valid. Will you share a patch for updating `clause` ? -- Best regards, Kirill Reshke ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> @ 2026-03-17 13:30 ` Tender Wang <[email protected]> 2026-03-17 17:46 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tender Wang @ 2026-03-17 13:30 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道： > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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. -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> @ 2026-03-17 17:46 ` Alexander Korotkov <[email protected]> 2026-03-18 01:12 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Alexander Korotkov @ 2026-03-17 17:46 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <[email protected]> wrote: > Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道： > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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. ------ Regards, Alexander Korotkov Supabase Attachments: [application/octet-stream] sje_joininfo_fix.patch (752B, 2-sje_joininfo_fix.patch) download | inline diff: diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 12e9ed0d0c7..68bfb76a5b0 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1957,6 +1957,12 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark, ChangeVarNodesExtended((Node *) root->parse, toRemove->relid, toKeep->relid, 0, replace_relid_callback); + foreach_node(RestrictInfo, rinfo, toKeep->joininfo) + { + ChangeVarNodesExtended((Node *) rinfo->clause, toRemove->relid, + toKeep->relid, 0, replace_relid_callback); + } + /* Replace links in the planner info */ remove_rel_from_query(root, toRemove, toKeep->relid, NULL, NULL); ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 17:46 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> @ 2026-03-18 01:12 ` Tender Wang <[email protected]> 2026-03-18 05:44 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tender Wang @ 2026-03-18 01:12 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] Alexander Korotkov <[email protected]> 于2026年3月18日周三 01:46写道： > > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <[email protected]> wrote: > > Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道： > > > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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. The cause of this bug is doing the following statement: ... ChangeVarNodesWalkExpression((Node *) rinfo->clause, context); ... in replace_relid_callback(). The rinfo->clause is only Var(tom1.col_bool), sorry I typoed it to " ON tom1.col_bool IS NOT NULL", in my first reply email. See expression_tree_walker_impl(), it does nothing if the node is just a Var node. So I think whether we can add logic in ChangeVarNodesWalkExpression() as below: ... if (node && IsA(node, Var)) { Var *var = (Var *) node; if (var->varlevelsup == context->sublevels_up) { if (var->varno == context->rt_index) var->varno = context->new_index; var->varnullingrels = adjust_relid_set(var->varnullingrels, context->rt_index, context->new_index); if (var->varnosyn == context->rt_index) var->varnosyn = context->new_index; } return false; } else return expression_tree_walker(node, ChangeVarNodes_walker, (void *) context); ... 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. -- Thanks, Tender Wang ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 17:46 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-18 01:12 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> @ 2026-03-18 05:44 ` Tender Wang <[email protected]> 2026-03-18 05:56 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tender Wang @ 2026-03-18 05:44 UTC (permalink / raw) To: Alexander Korotkov <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] Tender Wang <[email protected]> 于2026年3月18日周三 09:12写道： > > Alexander Korotkov <[email protected]> 于2026年3月18日周三 01:46写道： > > > > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <[email protected]> wrote: > > > Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道： > > > > > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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. -- Thanks, Tender Wang Attachments: [application/octet-stream] 0001-Fix-sje-bug.patch (2.1K, 2-0001-Fix-sje-bug.patch) download | inline diff: From 13b720474fb04e9027abb72ae63ec651d6ed80c8 Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Wed, 18 Mar 2026 13:29:33 +0800 Subject: [PATCH] Fix sje bug --- src/backend/optimizer/plan/analyzejoins.c | 11 +++++++--- src/backend/rewrite/rewriteManip.c | 26 ++++++++++++++++++++--- 2 files changed, 31 insertions(+), 6 deletions(-) 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..fff566eeb3a 100644 --- a/src/backend/rewrite/rewriteManip.c +++ b/src/backend/rewrite/rewriteManip.c @@ -744,9 +744,29 @@ 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); + if (node == NULL) + return false; + + if (IsA(node, Var)) + { + Var *var = (Var *) node; + + if (var->varlevelsup == context->sublevels_up) + { + if (var->varno == context->rt_index) + var->varno = context->new_index; + var->varnullingrels = adjust_relid_set(var->varnullingrels, + context->rt_index, + context->new_index); + if (var->varnosyn == context->rt_index) + var->varnosyn = context->new_index; + } + return false; + } + else + return expression_tree_walker(node, + ChangeVarNodes_walker, + (void *) context); } /* -- 2.34.1 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:19 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 13:24 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-17 17:46 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-18 01:12 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> 2026-03-18 05:44 ` Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Tender Wang <[email protected]> @ 2026-03-18 05:56 ` Kirill Reshke <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Kirill Reshke @ 2026-03-18 05:56 UTC (permalink / raw) To: Tender Wang <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; Fujii Masao <[email protected]>; [email protected]; [email protected] On Wed, 18 Mar 2026 at 10:44, Tender Wang <[email protected]> wrote: > > Tender Wang <[email protected]> 于2026年3月18日周三 09:12写道： > > > > Alexander Korotkov <[email protected]> 于2026年3月18日周三 01:46写道： > > > > > > On Tue, Mar 17, 2026 at 3:30 PM Tender Wang <[email protected]> wrote: > > > > Kirill Reshke <[email protected]> 于2026年3月17日周二 21:24写道： > > > > > > > > > > On Tue, 17 Mar 2026 at 18:20, Tender Wang <[email protected]> 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. > > -- > Thanks, > Tender Wang Hi! Your patch looks solid. ChangeVarNodesWalkExpression looks like a generic rewriter utility function, so I was wondering why we never got complaints about bugs related it. But this functions is reachable only when SJE optimisation is allowed, so looks like this explains the issue. -- Best regards, Kirill Reshke ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-03-18 05:56 UTC | newest] Thread overview: 9+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-17 12:26 Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables Alexander Korotkov <[email protected]> 2026-03-17 12:59 ` Tender Wang <[email protected]> 2026-03-17 13:19 ` Tender Wang <[email protected]> 2026-03-17 13:24 ` Kirill Reshke <[email protected]> 2026-03-17 13:30 ` Tender Wang <[email protected]> 2026-03-17 17:46 ` Alexander Korotkov <[email protected]> 2026-03-18 01:12 ` Tender Wang <[email protected]> 2026-03-18 05:44 ` Tender Wang <[email protected]> 2026-03-18 05:56 ` Kirill Reshke <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox