pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Richard Guo <guofenglinux@gmail.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>
Cc: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <akorotkov@postgresql.org>
Subject: Re: BUG #18170: Unexpected error: no relation entry for relid 3
Date: Tue, 31 Oct 2023 16:05:48 +0800
Message-ID: <CAMbWs4_P2yh_PSjJUfAVZ7gphUbBaKT9SAdOfTGXDsMWMV3v3A@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4_4Udor4YfMi6YiM+r1uj-UGbbf7S4yXrkXo4vXLiiYNw@mail.gmail.com>
References: <18170-f1d17bf9a0d58b24@postgresql.org>
	<4ecab120-6be3-4cf6-a2a0-249c73ae91d4@postgresfriends.org>
	<1189851.1698340331@sss.pgh.pa.us>
	<011bcae5-7840-42c7-a50c-b406189e2e78@postgrespro.ru>
	<CAPpHfdsiN0norSHfw3Ujemf=RuPzMxt5PRDiyv0vqUdwSBDtoA@mail.gmail.com>
	<721542ce-79df-469e-93e9-811fadb5bd0c@postgrespro.ru>
	<2eb87c2e-7e6f-4002-8df3-8fac3aa6a037@postgrespro.ru>
	<CAMbWs494m3AjhHry_HFCFi_SJ+6-PaeMRbx7jK39ZHmK0MVOUA@mail.gmail.com>
	<c0a5b39d-8a80-47fb-bd34-6d769f88df8f@postgrespro.ru>
	<CAPpHfduPMdLm42uwuXqM1weUKSYp=2Kf-_XU2TjZsizeZ3qoWQ@mail.gmail.com>
	<CAPpHfdv_6K_sv5sL_=9EiP7Lve81WLSdiLrc22afpmg_whyxtg@mail.gmail.com>
	<2141583.1698595016@sss.pgh.pa.us>
	<CAMbWs489RP3CFKwtWoG2m=qD5byfS_9_13yVU5wyknDTb3GToQ@mail.gmail.com>
	<fc183714-a61c-4de7-97e0-b1c376923aef@postgrespro.ru>
	<CAMbWs48FTQtO=ARgjbHqz5fYtOhVUAbdEC1PpnMsjsaRsOENeQ@mail.gmail.com>
	<7332bc61-a9fa-464b-b5a4-4e8e0f764a64@postgrespro.ru>
	<CAMbWs4_4Udor4YfMi6YiM+r1uj-UGbbf7S4yXrkXo4vXLiiYNw@mail.gmail.com>

On Tue, Oct 31, 2023 at 3:37 PM Richard Guo <guofenglinux@gmail.com> wrote:

> 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().
>

FWIW, here is a simplified query that can reproduce this error.

explain (costs off)
select ref_1.c17 from t2 ref_1 left join
        t2 ref_2 on ref_1.vkey = ref_2.vkey
where ref_2.vkey is not null
except all
select c17 from t2 ref_3;
ERROR:  no relation entry for relid 1

Thanks
Richard

view thread (34+ messages)  latest in thread

Message-ID: <CAMbWs4_P2yh_PSjJUfAVZ7gphUbBaKT9SAdOfTGXDsMWMV3v3A@mail.gmail.com>
Permalink:  ../CAMbWs4_P2yh_PSjJUfAVZ7gphUbBaKT9SAdOfTGXDsMWMV3v3A@mail.gmail.com/
Also on:    postgresql.org/message-id/CAMbWs4_P2yh_PSjJUfAVZ7gphUbBaKT9SAdOfTGXDsMWMV3v3A@mail.gmail.com

 · 

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: guofenglinux@gmail.com, a.lepikhov@postgrespro.ru, tgl@sss.pgh.pa.us, aekorotkov@gmail.com, vik@postgresfriends.org, akorotkov@postgresql.org
  Subject: Re: BUG #18170: Unexpected error: no relation entry for relid 3
  In-Reply-To: <CAMbWs4_P2yh_PSjJUfAVZ7gphUbBaKT9SAdOfTGXDsMWMV3v3A@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox