Content-Type: multipart/mixed; boundary="------------lb2SrDQqhMhXsybRs6i3W0jM"
Message-ID: <2eb87c2e-7e6f-4002-8df3-8fac3aa6a037@postgrespro.ru>
Date: Fri, 27 Oct 2023 18:00:28 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18170: Unexpected error: no relation entry for relid 3
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Vik Fearing <vik@postgresfriends.org>,
 zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org,
 Alexander Korotkov <akorotkov@postgresql.org>
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>
Content-Language: en-US
Organization: Postgres Professional
In-Reply-To: <721542ce-79df-469e-93e9-811fadb5bd0c@postgrespro.ru>
Archived-At: 
 <https://www.postgresql.org/message-id/2eb87c2e-7e6f-4002-8df3-8fac3aa6a037%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------lb2SrDQqhMhXsybRs6i3W0jM
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 27/10/2023 16:07, Andrei Lepikhov wrote:
> On 27/10/2023 15:17, Alexander Korotkov wrote:
>> On Fri, Oct 27, 2023 at 9:31 AM Andrei Lepikhov
>> <a.lepikhov@postgrespro.ru> wrote:
>>> On 27/10/2023 00:12, Tom Lane wrote:
>>>> Vik Fearing <vik@postgresfriends.org> writes:
>>>>> On 10/26/23 16:01, PG Bug reporting form wrote:
>>>>>> My fuzzer finds a bug in Postgres, which triggers an unexpected 
>>>>>> error.
>>>>
>>>>> This bisects to d3d55ce571369dad6e1d582f1655e5a3fbd8594a, Remove 
>>>>> useless
>>>>> self-joins.
>>>>
>>>> I wonder if that new code thinks it can remove ref_2 from the query,
>>>> even though ref_2 is used in the targetlist.  I'm not seeing
>>>> control reach remove_leftjoinrel_from_query, though.
>>>
>>> As I see, this join can be removed: in the explain you can see that
>>> OUTER JOIN is replaced with the INNER JOIN(ref_2, ref_3) ON a key 
>>> column.
>>> In my opinion, the origin of the problem is that the parent_root
>>> contains a link to ref_2 in its
>>> simple_rte_array[]->subquery->targetList. I am still looking for a
>>> general solution right now, but it doesn't look too complicated at first
>>> sight.
>>
>> Yes, I came to the same conclusion.  We process root->parse.  But I
>> didn't get why parent_root->simple_rte_array[]->subquery is not the
>> same as root->parse.  They look the same, but they are distinct
>> copies.  If they were the same pointers, there would be no problem.
> 
> As I see, the copy of the parse tree is induced by the same feature as 
> usual in the last few months: 2489d76. It introduced 
> remove_nulling_relids, and it altered our parse tree. Right now, I don't 
> have an answer: it should be fixed in SJE, or this is a more general 
> issue just discovered by the SJE.

So, I can propose two options. First - don't clean only the current root 
structure, but also make cleanup of the parent. Although it looks safe, 
I am not happy with this approach - it seems too simple: we should have 
a genuine reason for such a cleaning because it potentially adds overhead.
The second option is to add a flag for not altering queries in 
remove_nulling_relids() - it looks like a mistake when we have two 
different query trees in the root and its parent. Also, it reduces 
memory usage a bit.
So, if my analysis is correct, it is better to use the second way (see 
attachment).

-- 
regards,
Andrei Lepikhov
Postgres Professional

--------------lb2SrDQqhMhXsybRs6i3W0jM
Content-Type: text/plain; charset=UTF-8;
 name="0001-Don-t-alter-parse-tree-during-the-procedure-of-outer.patch"
Content-Disposition: attachment;
 filename*0="0001-Don-t-alter-parse-tree-during-the-procedure-of-outer.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSBkMmZhYzhjZWQ0N2U4ODNjN2I4MDlhNGRjMzYxMmQzNTk4N2Y0NjBjIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmV5IFYuIExlcGlraG92IiA8YS5sZXBpa2hv
dkBwb3N0Z3Jlc3Byby5ydT4KRGF0ZTogRnJpLCAyNyBPY3QgMjAyMyAxNzo1NTowNCArMDcw
MApTdWJqZWN0OiBbUEFUQ0hdIERvbid0IGFsdGVyIHBhcnNlIHRyZWUgZHVyaW5nIHRoZSBw
cm9jZWR1cmUgb2Ygb3V0ZXIgam9pbgogcmVkdWN0aW9uLgoKLS0tCiBzcmMvYmFja2VuZC9v
cHRpbWl6ZXIvcHJlcC9wcmVwam9pbnRyZWUuYyB8IDE2ICsrKysrKysrLS0tLS0tLS0KIHNy
Yy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZU1hbmlwLmMgICAgICAgIHwgMTAgKysrKysrKysr
LQogc3JjL2luY2x1ZGUvcmV3cml0ZS9yZXdyaXRlTWFuaXAuaCAgICAgICAgfCAgNSArKysr
LQogMyBmaWxlcyBjaGFuZ2VkLCAyMSBpbnNlcnRpb25zKCspLCAxMCBkZWxldGlvbnMoLSkK
CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcHJlcC9wcmVwam9pbnRyZWUu
YyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wcmVwL3ByZXBqb2ludHJlZS5jCmluZGV4IDcz
ZmY0MDcyMWMuLjA2YTc2MzY0MDMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXpl
ci9wcmVwL3ByZXBqb2ludHJlZS5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wcmVw
L3ByZXBqb2ludHJlZS5jCkBAIC0yNjg3LDE0ICsyNjg3LDE0IEBAIHJlZHVjZV9vdXRlcl9q
b2lucyhQbGFubmVySW5mbyAqcm9vdCkKIAlpZiAoIWJtc19pc19lbXB0eShzdGF0ZTIuaW5u
ZXJfcmVkdWNlZCkpCiAJewogCQlyb290LT5wYXJzZSA9IChRdWVyeSAqKQotCQkJcmVtb3Zl
X251bGxpbmdfcmVsaWRzKChOb2RlICopIHJvb3QtPnBhcnNlLAorCQkJcmVtb3ZlX251bGxp
bmdfcmVsaWRzX2V4dCgoTm9kZSAqKSByb290LT5wYXJzZSwKIAkJCQkJCQkJICBzdGF0ZTIu
aW5uZXJfcmVkdWNlZCwKLQkJCQkJCQkJICBOVUxMKTsKKwkJCQkJCQkJICBOVUxMLCBRVFdf
RE9OVF9DT1BZX1FVRVJZKTsKIAkJLyogVGhlcmUgY291bGQgYmUgcmVmZXJlbmNlcyBpbiB0
aGUgYXBwZW5kX3JlbF9saXN0LCB0b28gKi8KIAkJcm9vdC0+YXBwZW5kX3JlbF9saXN0ID0g
KExpc3QgKikKLQkJCXJlbW92ZV9udWxsaW5nX3JlbGlkcygoTm9kZSAqKSByb290LT5hcHBl
bmRfcmVsX2xpc3QsCisJCQlyZW1vdmVfbnVsbGluZ19yZWxpZHNfZXh0KChOb2RlICopIHJv
b3QtPmFwcGVuZF9yZWxfbGlzdCwKIAkJCQkJCQkJICBzdGF0ZTIuaW5uZXJfcmVkdWNlZCwK
LQkJCQkJCQkJICBOVUxMKTsKKwkJCQkJCQkJICBOVUxMLCBRVFdfRE9OVF9DT1BZX1FVRVJZ
KTsKIAl9CiAKIAkvKgpAQCAtMjcwNywxMyArMjcwNywxMyBAQCByZWR1Y2Vfb3V0ZXJfam9p
bnMoUGxhbm5lckluZm8gKnJvb3QpCiAJCVJlbGlkcwkJZnVsbF9qb2luX3JlbGlkcyA9IGJt
c19tYWtlX3NpbmdsZXRvbihzdGF0ZXAtPmZ1bGxfam9pbl9ydGkpOwogCiAJCXJvb3QtPnBh
cnNlID0gKFF1ZXJ5ICopCi0JCQlyZW1vdmVfbnVsbGluZ19yZWxpZHMoKE5vZGUgKikgcm9v
dC0+cGFyc2UsCisJCQlyZW1vdmVfbnVsbGluZ19yZWxpZHNfZXh0KChOb2RlICopIHJvb3Qt
PnBhcnNlLAogCQkJCQkJCQkgIGZ1bGxfam9pbl9yZWxpZHMsCi0JCQkJCQkJCSAgc3RhdGVw
LT51bnJlZHVjZWRfc2lkZSk7CisJCQkJCQkJCSAgc3RhdGVwLT51bnJlZHVjZWRfc2lkZSwg
UVRXX0RPTlRfQ09QWV9RVUVSWSk7CiAJCXJvb3QtPmFwcGVuZF9yZWxfbGlzdCA9IChMaXN0
ICopCi0JCQlyZW1vdmVfbnVsbGluZ19yZWxpZHMoKE5vZGUgKikgcm9vdC0+YXBwZW5kX3Jl
bF9saXN0LAorCQkJcmVtb3ZlX251bGxpbmdfcmVsaWRzX2V4dCgoTm9kZSAqKSByb290LT5h
cHBlbmRfcmVsX2xpc3QsCiAJCQkJCQkJCSAgZnVsbF9qb2luX3JlbGlkcywKLQkJCQkJCQkJ
ICBzdGF0ZXAtPnVucmVkdWNlZF9zaWRlKTsKKwkJCQkJCQkJICBzdGF0ZXAtPnVucmVkdWNl
ZF9zaWRlLCBRVFdfRE9OVF9DT1BZX1FVRVJZKTsKIAl9CiB9CiAKZGlmZiAtLWdpdCBhL3Ny
Yy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZU1hbmlwLmMgYi9zcmMvYmFja2VuZC9yZXdyaXRl
L3Jld3JpdGVNYW5pcC5jCmluZGV4IDMyYmQyZjFkYzkuLjFmZGZiMDVjZmIgMTAwNjQ0Ci0t
LSBhL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZU1hbmlwLmMKKysrIGIvc3JjL2JhY2tl
bmQvcmV3cml0ZS9yZXdyaXRlTWFuaXAuYwpAQCAtMTIyNiw2ICsxMjI2LDE0IEBAIE5vZGUg
KgogcmVtb3ZlX251bGxpbmdfcmVsaWRzKE5vZGUgKm5vZGUsCiAJCQkJCSAgY29uc3QgQml0
bWFwc2V0ICpyZW1vdmFibGVfcmVsaWRzLAogCQkJCQkgIGNvbnN0IEJpdG1hcHNldCAqZXhj
ZXB0X3JlbGlkcykKK3sKKwlyZXR1cm4gcmVtb3ZlX251bGxpbmdfcmVsaWRzX2V4dChub2Rl
LCByZW1vdmFibGVfcmVsaWRzLCBleGNlcHRfcmVsaWRzLCAwKTsKKworfQorTm9kZSAqCity
ZW1vdmVfbnVsbGluZ19yZWxpZHNfZXh0KE5vZGUgKm5vZGUsCisJCQkJCQkgIGNvbnN0IEJp
dG1hcHNldCAqcmVtb3ZhYmxlX3JlbGlkcywKKwkJCQkJCSAgY29uc3QgQml0bWFwc2V0ICpl
eGNlcHRfcmVsaWRzLCBpbnQgZmxhZ3MpCiB7CiAJcmVtb3ZlX251bGxpbmdfcmVsaWRzX2Nv
bnRleHQgY29udGV4dDsKIApAQCAtMTIzNSw3ICsxMjQzLDcgQEAgcmVtb3ZlX251bGxpbmdf
cmVsaWRzKE5vZGUgKm5vZGUsCiAJcmV0dXJuIHF1ZXJ5X29yX2V4cHJlc3Npb25fdHJlZV9t
dXRhdG9yKG5vZGUsCiAJCQkJCQkJCQkJCXJlbW92ZV9udWxsaW5nX3JlbGlkc19tdXRhdG9y
LAogCQkJCQkJCQkJCQkmY29udGV4dCwKLQkJCQkJCQkJCQkJMCk7CisJCQkJCQkJCQkJCWZs
YWdzKTsKIH0KIAogc3RhdGljIE5vZGUgKgpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvcmV3
cml0ZS9yZXdyaXRlTWFuaXAuaCBiL3NyYy9pbmNsdWRlL3Jld3JpdGUvcmV3cml0ZU1hbmlw
LmgKaW5kZXggY2ExMjc4MGJjNy4uNmJjN2U2ZjM2ZSAxMDA2NDQKLS0tIGEvc3JjL2luY2x1
ZGUvcmV3cml0ZS9yZXdyaXRlTWFuaXAuaAorKysgYi9zcmMvaW5jbHVkZS9yZXdyaXRlL3Jl
d3JpdGVNYW5pcC5oCkBAIC03MSw3ICs3MSwxMCBAQCBleHRlcm4gTm9kZSAqYWRkX251bGxp
bmdfcmVsaWRzKE5vZGUgKm5vZGUsCiBleHRlcm4gTm9kZSAqcmVtb3ZlX251bGxpbmdfcmVs
aWRzKE5vZGUgKm5vZGUsCiAJCQkJCQkJCSAgIGNvbnN0IEJpdG1hcHNldCAqcmVtb3ZhYmxl
X3JlbGlkcywKIAkJCQkJCQkJICAgY29uc3QgQml0bWFwc2V0ICpleGNlcHRfcmVsaWRzKTsK
LQorZXh0ZXJuIE5vZGUgKnJlbW92ZV9udWxsaW5nX3JlbGlkc19leHQoTm9kZSAqbm9kZSwK
KwkJCQkJCQkJCSAgIGNvbnN0IEJpdG1hcHNldCAqcmVtb3ZhYmxlX3JlbGlkcywKKwkJCQkJ
CQkJCSAgIGNvbnN0IEJpdG1hcHNldCAqZXhjZXB0X3JlbGlkcywKKwkJCQkJCQkJCSAgIGlu
dCBmbGFncyk7CiBleHRlcm4gTm9kZSAqcmVwbGFjZV9ydGVfdmFyaWFibGVzKE5vZGUgKm5v
ZGUsCiAJCQkJCQkJCSAgIGludCB0YXJnZXRfdmFybm8sIGludCBzdWJsZXZlbHNfdXAsCiAJ
CQkJCQkJCSAgIHJlcGxhY2VfcnRlX3ZhcmlhYmxlc19jYWxsYmFjayBjYWxsYmFjaywKLS0g
CjIuNDIuMAoK

--------------lb2SrDQqhMhXsybRs6i3W0jM--