public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Tender Wang <tndrwang@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: ammmkilo@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
Date: Wed, 18 Mar 2026 09:12:57 +0800
Message-ID: <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com> (raw)
In-Reply-To: <CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
References: <19435-3cc1a87f291129f1@postgresql.org>
<CAHGQGwEEHFnH8DsZbwxdZeiHozm2LCRTLSgL8Qn=5MoN4450ZA@mail.gmail.com>
<CAPpHfdsDQhDqvqi+-UxvyYRP72ASkdWzCn43e2Hj6WsY15opvA@mail.gmail.com>
<CAHewXNmU3E0oXLgvoOtXw7kfTiAZsKu8La_hQ=pyScgxvP45iw@mail.gmail.com>
<CAHewXN=LjuWz3PcyhjdbJAyo+Zs9MisPDRYnSZBUy4PMeKi+zA@mail.gmail.com>
<CALdSSPj1kTTQvmV3H3HMf5P3um8ybxoH3DaTPm+XgdYAur1Q4A@mail.gmail.com>
<CAHewXNndByMu3S+_h4LLDkXA5qrO1s=s-CE8HqUtc9vTA9yrjg@mail.gmail.com>
<CAPpHfdv6gzSTXHJxYSgB8sULadXM4wvhgoQODaOxYCJfagKNPw@mail.gmail.com>
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.
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tndrwang@gmail.com, aekorotkov@gmail.com, reshkekirill@gmail.com, masao.fujii@gmail.com, ammmkilo@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19435: Error: "No relation entry for relid 2" Triggered by Complex Join with Self-Referencing Tables
In-Reply-To: <CAHewXN=7kDJjUcgEm+6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox