Re: BUG #18170: Unexpected error: no relation entry for relid 3

On Tue, Oct 31, 2023 at 1:05 PM Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
wrote:

> On 30/10/2023 13:24, Richard Guo wrote:
> > Yeah, that's what I meant. We need to ensure that root->parse
> > references the same Query structure during the whole subquery_planner()
> > for this patch to work, which seems hacky, and error-prone for future
> > development. If we really want to do so, at least we need to emphasize
> > this point in the comment of subquery_planner().
>
> Okay, let's go another way and try to imagine what additional
> opportunities it will give us in the future? I mean, save unchanged a
> rte->subquery tree and, simultaneously, have some transformed version in
> the subroot->parse field.
> I can imagine kind of a subquery replanning procedure in the case we
> realized during higher-level query planning that the underlying subquery
> is not optimal.
> Such a picture makes sense, and I can agree with your words. From this
> point of view, all links to the subquery must reference the subroot
> structures, and the patch you have proposed is the best solution. And
> the SJE feature works correctly.
> If we don't imagine the picture I have drawn above, it is reasonable to
> save memory and not alter the parse tree while removing unneeded joins.
> I think, in that case, we can use the walker procedure instead of a
> mutator.

Well, I think what happens here is that the new SJE code alters the
subquery (by removing ref_2) and that change is not propagated into the
parent level. So in the parent level, when we look at the subquery's
original targetlist (which remains unchanged) against 'subroot' (which
has been changed accordingly), we'd have problem.

AFAICS there are two solutions being discussed:

1) We propagate the change to the subquery into the parent level by
ensuring that root->parse references the same Query structure during the
whole subquery_planner().

2) In the parent level, we look at the changed subquery instead of the
original rte->subquery. IOW, we look at subroot->parse->targetList
instead of subquery->targetList.

Personally I prefer the second solution because it seems more natural to
look at 'subroot->parse' together with 'subroot' in estimate_num_groups
and it does not introduce new constraint to subquery_planner().

Thanks
Richard