Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables - Mailing list pgsql-bugs

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