MIME-Version: 1.0
References: <CA+HiwqFpZ80UJKr4tZus4Omgg7YESzFXKSwSHRW2Ap2=XSVyUA@mail.gmail.com>
 <54c35fb9-da3a-4754-ab8c-46ed0b612465@vondra.me> <CA+HiwqH8N-SxEB6SysEBsYNgV_KJs66k9Z2SNmqVzbBP-60yWg@mail.gmail.com>
 <684c70d7-180e-461d-9377-600c2db581ba@vondra.me> <CA+HiwqEmG9YCQvG6uux7sO=jKFSAW6hA4Ea-ymfD+JhJAe4PWQ@mail.gmail.com>
 <CA+HiwqE2FfJfH=siLiR3kJ13tmXZORAGTWsZc2r52o1_5BDv+g@mail.gmail.com>
 <bfcc1b78-aa39-4356-8f42-b8cfb9733d6e@vondra.me> <CA+HiwqFhkpXHAA=4NY5SqYXX08uq=nYtXcSByNZF=2MAy1UA7A@mail.gmail.com>
 <CA+HiwqHCcSoYfpMjFshaU1bj6NjreiDvMSDpVSeBmqk-kbWrPw@mail.gmail.com>
 <CA+HiwqHOejJk0_qMuM5g38h70hY_JvHMAKwnH3k=urfTXauPQA@mail.gmail.com>
 <CA+HiwqFsGKM82oaMby3VWYXf_XFpDAMeT+6SXgj-45HpTrS1dA@mail.gmail.com>
 <CA+HiwqFA5hUWYktt3VMh4zQOYMxqH-MpdX8eemfM+o-9dY-zbQ@mail.gmail.com>
 <CA+HiwqEn7bbUXaXO=SmUujBjJSHfS31cwQroHRBwT0sR=66bgg@mail.gmail.com>
 <CA+HiwqGGLDTd1ZTK1c0zv4La7XOVSVMqBuNtscJeh6FyUQvFvA@mail.gmail.com>
 <CA+HiwqE2JFiqqrXdyJVQWY-fMGwzDkLqjXQdUKbPaCpDpxd_2g@mail.gmail.com>
 <74839af6-aadc-4f60-ae77-ae65f94bf607@gmail.com> <CAHewXNnEwVQoXBiR+nC09R20Psr2xXjOuhatC-kHfr0-B2pcVw@mail.gmail.com>
In-Reply-To: <CAHewXNnEwVQoXBiR+nC09R20Psr2xXjOuhatC-kHfr0-B2pcVw@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Sun, 23 Feb 2025 17:35:49 +0900
Message-ID: <CA+HiwqFp3jZGSz==QjeuV62_62F6+V6b62=Uqvy99sW_gsgWBA@mail.gmail.com>
Subject: Re: generic plans and "initial" pruning
To: Tender Wang <tndrwang@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>, Tomas Vondra <tomas@vondra.me>, 
	Robert Haas <robertmhaas@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	Andres Freund <andres@anarazel.de>, Daniel Gustafsson <daniel@yesql.se>, 
	David Rowley <dgrowleyml@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 
	Thom Brown <thom@linux.com>, Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="0000000000005e8d4d062ecb1d86"
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqFp3jZGSz%3D%3DQjeuV62_62F6%2BV6b62%3DUqvy99sW_gsgWBA%40mail.gmail.com>
Precedence: bulk

--0000000000005e8d4d062ecb1d86
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Feb 23, 2025 at 2:03=E2=80=AFAM Tender Wang <tndrwang@gmail.com> wr=
ote:
> Alexander Lakhin <exclusion@gmail.com> =E4=BA=8E2025=E5=B9=B42=E6=9C=8822=
=E6=97=A5=E5=91=A8=E5=85=AD 23:00=E5=86=99=E9=81=93=EF=BC=9A
>> 21.02.2025 05:40, Amit Langote wrote:
>>
>> I pushed the final piece yesterday.
>>
>>
>> Please look at new error, produced by the following script,
>> starting from 525392d57:
>> CREATE TABLE t(id int) PARTITION BY RANGE (id);
>> CREATE INDEX idx on t(id);
>> CREATE TABLE tp_1 PARTITION OF t FOR VALUES FROM (10) TO (20);
>> CREATE TABLE tp_2 PARTITION OF t FOR VALUES FROM (20) TO (30) PARTITION =
BY RANGE(id);
>> CREATE TABLE tp_2_1 PARTITION OF tp_2 FOR VALUES FROM (21) to (22);
>> CREATE TABLE tp_2_2 PARTITION OF tp_2 FOR VALUES FROM (22) to (23);
>> CREATE FUNCTION stable_one() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ L=
ANGUAGE plpgsql STABLE;
>>
>> SELECT min(id) OVER (PARTITION BY id ORDER BY id) FROM t WHERE id >=3D s=
table_one();
>>
>> ERROR:  XX000: trying to open a pruned relation
>> LOCATION:  ExecGetRangeTableRelation, execUtils.c:830
>>
>> This issue was discovered with SQLsmith.

Thanks for the report.

> The error message was added in commit  525392d57. In this case, the estat=
e->es_unpruned_relids only includes 1, which is the offset of table t.
> In register_partpruneinfo(), we collect glob->prunableRelids; in this cas=
e, it contains 2,3,4,5. Then we will do:
> result->unprunableRelids =3D bms_difference(glob->allRelids,
>  glob->prunableRelids);
> so the result->unprunableRelids only contains 1.
>
> But tp_2 is also partition table, and its partpruneinfo created by create=
_append_plan() is put into the head of global list.
> So we first process it in ExecDoInitialPruning().  Then error reports bec=
ause we only contain 1 in estate->es_unpruned_relids.

Thanks for checking.

The RT index of tp_2 should appear in PlannedStmt.unprunableRelids,
because it needs to be opened in CreatePartitionPruneState() for
setting up its PartitionPruneInfo. We use ExecGetRangeTableRelation()
to open, which expects the relation to be locked, so the error.

To ensure tp_2 appears in PlannedStmt.unprunableRelids, we should
prevent make_partitionedrel_pruneinfo() from placing the RT index into
leafpart_rti_map[], as the current condition for inclusion doesn=E2=80=99t
account for whether the partition is itself partitioned.

I've come up with the attached.

--=20
Thanks, Amit Langote

--0000000000005e8d4d062ecb1d86
Content-Type: application/octet-stream; 
	name="v1-0001-Fix-bug-in-cbc127917-to-handle-nested-Append-corr.patch"
Content-Disposition: attachment; 
	filename="v1-0001-Fix-bug-in-cbc127917-to-handle-nested-Append-corr.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m7hdauir0>
X-Attachment-Id: f_m7hdauir0

RnJvbSBmODI5Y2Q5YTY3OTAwM2IxNDAzMDY3OWFkYmQwOGFhZTcwY2VmYzU1IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbWl0IExhbmdvdGUgPGFtaXRsYW5AcG9zdGdyZXNxbC5vcmc+
CkRhdGU6IFN1biwgMjMgRmViIDIwMjUgMTc6MTk6MjggKzA5MDAKU3ViamVjdDogW1BBVENIIHYx
XSBGaXggYnVnIGluIGNiYzEyNzkxNyB0byBoYW5kbGUgbmVzdGVkIEFwcGVuZCBjb3JyZWN0bHkK
CkEgc3ViLXBhcnRpdGlvbmVkIHBhcnRpdGlvbiB3aXRoIGEgc3VicGxhbiB0aGF0IGlzIGFuIEFw
cGVuZCBub2RlIHdhcwpub3QgY29ycmVjdGx5IHJlcG9ydGVkIGluIFBsYW5uZWRTdG10LnVucHJ1
bmFibGVSZWxpZHMuIFRoaXMgb21pc3Npb24KbGVkIHRvIEV4ZWNHZXRSYW5nZVRhYmxlUmVsYXRp
b24oKSByZXBvcnRpbmcgYW4gZXJyb3Igd2hlbiBjYWxsZWQgZnJvbQpDcmVhdGVQYXJ0aXRpb25Q
cnVuZVN0YXRlKCkgdG8gcHJvY2VzcyBpdHMgUGFydGl0aW9uUHJ1bmVJbmZvLgoKUmVwb3J0ZWQt
Ynk6IEFsZXhhbmRlciBMYWtoaW4gPGV4Y2x1c2lvbkBnbWFpbC5jb20+ICh2aWEgc3Fsc21pdGgp
CkRpYWdub3NlZC1ieTogVGVuZGVyIFdhbmcgPHRuZHJ3YW5nQGdtYWlsLmNvbT4KRGlzY3Vzc2lv
bjogaHR0cHM6Ly9wb3N0Z3IuZXMvbS83NDgzOWFmNi1hYWRjLTRmNjAtYWU3Ny1hZTY1Zjk0YmY2
MDdAZ21haWwuY29tCi0tLQogc3JjL2JhY2tlbmQvZXhlY3V0b3IvZXhlY1BhcnRpdGlvbi5jICAg
ICAgICAgIHwgMTQgKysrKysrKysrLS0tLQogc3JjL2JhY2tlbmQvcGFydGl0aW9uaW5nL3BhcnRw
cnVuZS5jICAgICAgICAgIHwgIDggKysrKysrKy0KIHNyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQv
cGFydGl0aW9uX3BydW5lLm91dCB8IDIwICsrKysrKysrKysrKysrKysrKysKIHNyYy90ZXN0L3Jl
Z3Jlc3Mvc3FsL3BhcnRpdGlvbl9wcnVuZS5zcWwgICAgICB8ICA3ICsrKysrKysKIDQgZmlsZXMg
Y2hhbmdlZCwgNDQgaW5zZXJ0aW9ucygrKSwgNSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9z
cmMvYmFja2VuZC9leGVjdXRvci9leGVjUGFydGl0aW9uLmMgYi9zcmMvYmFja2VuZC9leGVjdXRv
ci9leGVjUGFydGl0aW9uLmMKaW5kZXggNDMyZWVhZjkwMzQuLmI4NmZjNWVhMjk3IDEwMDY0NAot
LS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9leGVjUGFydGl0aW9uLmMKKysrIGIvc3JjL2JhY2tl
bmQvZXhlY3V0b3IvZXhlY1BhcnRpdGlvbi5jCkBAIC0yNTg5LDkgKzI1ODksOSBAQCBFeGVjRmlu
ZE1hdGNoaW5nU3ViUGxhbnMoUGFydGl0aW9uUHJ1bmVTdGF0ZSAqcHJ1bmVzdGF0ZSwKICAqIGZp
bmRfbWF0Y2hpbmdfc3VicGxhbnNfcmVjdXJzZQogICoJCVJlY3Vyc2l2ZSB3b3JrZXIgZnVuY3Rp
b24gZm9yIEV4ZWNGaW5kTWF0Y2hpbmdTdWJQbGFucwogICoKLSAqIEFkZHMgdmFsaWQgKG5vbi1w
cnVuYWJsZSkgc3VicGxhbiBJRHMgdG8gKnZhbGlkc3VicGxhbnMgYW5kIHRoZSBSVCBpbmRleGVz
Ci0gKiBvZiB0aGVpciBjb3JyZXNwb25kaW5nIGxlYWYgcGFydGl0aW9ucyB0byAqdmFsaWRzdWJw
bGFuX3J0aXMgaWYKLSAqIGl0J3Mgbm9uLU5VTEwuCisgKiBBZGRzIHZhbGlkIChub24tcHJ1bmFi
bGUpIHN1YnBsYW4gSURzIHRvICp2YWxpZHN1YnBsYW5zLiBJZgorICogKnZhbGlkc3VicGxhbl9y
dGlzIGlzIG5vbi1OVUxMLCBpdCBhbHNvIGFkZHMgdGhlIFJUIGluZGV4ZXMgb2YgdGhlaXIKKyAq
IGNvcnJlc3BvbmRpbmcgcGFydGl0aW9ucywgYnV0IG9ubHkgaWYgdGhleSBhcmUgbGVhZiBwYXJ0
aXRpb25zLgogICovCiBzdGF0aWMgdm9pZAogZmluZF9tYXRjaGluZ19zdWJwbGFuc19yZWN1cnNl
KFBhcnRpdGlvblBydW5pbmdEYXRhICpwcnVuZWRhdGEsCkBAIC0yNjI4LDcgKzI2MjgsMTMgQEAg
ZmluZF9tYXRjaGluZ19zdWJwbGFuc19yZWN1cnNlKFBhcnRpdGlvblBydW5pbmdEYXRhICpwcnVu
ZWRhdGEsCiAJCXsKIAkJCSp2YWxpZHN1YnBsYW5zID0gYm1zX2FkZF9tZW1iZXIoKnZhbGlkc3Vi
cGxhbnMsCiAJCQkJCQkJCQkJCXBwcnVuZS0+c3VicGxhbl9tYXBbaV0pOwotCQkJaWYgKHZhbGlk
c3VicGxhbl9ydGlzKQorCisJCQkvKgorCQkJICogU3VicGxhbiBtaWdodCBiZSBhIG5lc3RlZCBB
cHBlbmQgLyBNZXJnZUFwcGVuZCBmb3IgYQorCQkJICogc3ViLXBhcnRpdGlvbmVkIHBhcnRpdGlv
biB3aG9zZSBSVCBpbmRleCBuZWVkIG5vdCBiZSByZXBvcnRlZAorCQkJICogdG8gdGhlIGNhbGxl
ci4KKwkJCSAqLworCQkJaWYgKHZhbGlkc3VicGxhbl9ydGlzICYmIHBwcnVuZS0+bGVhZnBhcnRf
cnRpX21hcFtpXSkKIAkJCQkqdmFsaWRzdWJwbGFuX3J0aXMgPSBibXNfYWRkX21lbWJlcigqdmFs
aWRzdWJwbGFuX3J0aXMsCiAJCQkJCQkJCQkJCQkJcHBydW5lLT5sZWFmcGFydF9ydGlfbWFwW2ld
KTsKIAkJfQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcGFydGl0aW9uaW5nL3BhcnRwcnVuZS5j
IGIvc3JjL2JhY2tlbmQvcGFydGl0aW9uaW5nL3BhcnRwcnVuZS5jCmluZGV4IGZmOTI2NzMyZjM2
Li4zZmFmM2Y4NTU1YyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcGFydGl0aW9uaW5nL3BhcnRw
cnVuZS5jCisrKyBiL3NyYy9iYWNrZW5kL3BhcnRpdGlvbmluZy9wYXJ0cHJ1bmUuYwpAQCAtNjg3
LDcgKzY4NywxMyBAQCBtYWtlX3BhcnRpdGlvbmVkcmVsX3BydW5laW5mbyhQbGFubmVySW5mbyAq
cm9vdCwgUmVsT3B0SW5mbyAqcGFyZW50cmVsLAogCQkJaWYgKHN1YnBsYW5pZHggPj0gMCkKIAkJ
CXsKIAkJCQlwcmVzZW50X3BhcnRzID0gYm1zX2FkZF9tZW1iZXIocHJlc2VudF9wYXJ0cywgaSk7
Ci0JCQkJbGVhZnBhcnRfcnRpX21hcFtpXSA9IChpbnQpIHBhcnRyZWwtPnJlbGlkOworCisJCQkJ
LyoKKwkJCQkgKiBTdWJwbGFuIG1pZ2h0IGJlIGEgbmVzdGVkIEFwcGVuZC9NZXJnZUFwcGVuZCBm
b3IgYQorCQkJCSAqIHN1Yi1wYXJ0aXRpb25lZCBwYXJ0aXRpb24uCisJCQkJICovCisJCQkJaWYg
KHBhcnRyZWwtPm5wYXJ0cyA9PSAtMSkKKwkJCQkJbGVhZnBhcnRfcnRpX21hcFtpXSA9IChpbnQp
IHBhcnRyZWwtPnJlbGlkOwogCiAJCQkJLyogUmVjb3JkIGZpbmRpbmcgdGhpcyBzdWJwbGFuICAq
LwogCQkJCXN1YnBsYW5zZm91bmQgPSBibXNfYWRkX21lbWJlcihzdWJwbGFuc2ZvdW5kLCBzdWJw
bGFuaWR4KTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcGFydGl0aW9u
X3BydW5lLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcGFydGl0aW9uX3BydW5lLm91
dAppbmRleCA2ZjgwYjYyYTNiOC4uOTRhNDk5ZmM4ZTAgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3Jl
Z3Jlc3MvZXhwZWN0ZWQvcGFydGl0aW9uX3BydW5lLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNz
L2V4cGVjdGVkL3BhcnRpdGlvbl9wcnVuZS5vdXQKQEAgLTQ1OTAsNSArNDU5MCwyNSBAQCB0YWJs
ZSBwYXJ0X2FiY192aWV3OwogIDIgfCBjIHwgdAogKDEgcm93KQogCistLSBBIGNhc2Ugd2l0aCBu
ZXN0ZWQgQXBwZW5kIHdpdGggaXRzIG93biBQYXJ0aXRpb25QcnVuZUluZm8uCitjcmVhdGUgaW5k
ZXggb24gcGFydF9hYmMgKGEpOworY3JlYXRlIHRhYmxlIHBhcnRfYWJjXzMgcGFydGl0aW9uIG9m
IHBhcnRfYWJjIGZvciB2YWx1ZXMgaW4gKDMsIDQpIHBhcnRpdGlvbiBieSBsaXN0IChhKTsKK2Ny
ZWF0ZSB0YWJsZSBwYXJ0X2FiY18zXzMgcGFydGl0aW9uIG9mIHBhcnRfYWJjXzMgZm9yIHZhbHVl
cyBpbiAoMyk7CitjcmVhdGUgdGFibGUgcGFydF9hYmNfM180IHBhcnRpdGlvbiBvZiBwYXJ0X2Fi
Y18zIGZvciB2YWx1ZXMgaW4gKDQpOworZXhwbGFpbiAoY29zdHMgb2ZmKSBzZWxlY3QgbWluKGEp
IG92ZXIgKHBhcnRpdGlvbiBieSBhIG9yZGVyIGJ5IGEpIGZyb20gcGFydF9hYmMgd2hlcmUgYSA+
PSBzdGFibGVfb25lKCkgKyAxOworICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgCistLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyBXaW5kb3dBZ2cKKyAgIC0+ICBBcHBlbmQKKyAg
ICAgICAgIFN1YnBsYW5zIFJlbW92ZWQ6IDEKKyAgICAgICAgIC0+ICBJbmRleCBPbmx5IFNjYW4g
dXNpbmcgcGFydF9hYmNfMl9hX2lkeCBvbiBwYXJ0X2FiY18yIHBhcnRfYWJjXzEKKyAgICAgICAg
ICAgICAgIEluZGV4IENvbmQ6IChhID49IChzdGFibGVfb25lKCkgKyAxKSkKKyAgICAgICAgIC0+
ICBBcHBlbmQKKyAgICAgICAgICAgICAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgcGFydF9h
YmNfM18zX2FfaWR4IG9uIHBhcnRfYWJjXzNfMyBwYXJ0X2FiY18zCisgICAgICAgICAgICAgICAg
ICAgICBJbmRleCBDb25kOiAoYSA+PSAoc3RhYmxlX29uZSgpICsgMSkpCisgICAgICAgICAgICAg
ICAtPiAgSW5kZXggT25seSBTY2FuIHVzaW5nIHBhcnRfYWJjXzNfNF9hX2lkeCBvbiBwYXJ0X2Fi
Y18zXzQgcGFydF9hYmNfNAorICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKGEgPj0g
KHN0YWJsZV9vbmUoKSArIDEpKQorKDEwIHJvd3MpCisKIGRyb3AgdmlldyBwYXJ0X2FiY192aWV3
OwogZHJvcCB0YWJsZSBwYXJ0X2FiYzsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L3BhcnRpdGlvbl9wcnVuZS5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9wYXJ0aXRpb25fcHJ1
bmUuc3FsCmluZGV4IDg2NjIxZGNlYzBiLi40ODI3M2Y4ZDAyNyAxMDA2NDQKLS0tIGEvc3JjL3Rl
c3QvcmVncmVzcy9zcWwvcGFydGl0aW9uX3BydW5lLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNz
L3NxbC9wYXJ0aXRpb25fcHJ1bmUuc3FsCkBAIC0xMzk3LDUgKzEzOTcsMTIgQEAgdXNpbmcgKHNl
bGVjdCBzdGFibGVfb25lKCkgKyAyIGFzIHBpZCkgYXMgcSBqb2luIHBhcnRfYWJjXzEgcHQxIG9u
IChxLnBpZCA9IHB0MS4KIHdoZW4gbWF0Y2hlZCB0aGVuIGRlbGV0ZSByZXR1cm5pbmcgcHQuYTsK
IHRhYmxlIHBhcnRfYWJjX3ZpZXc7CiAKKy0tIEEgY2FzZSB3aXRoIG5lc3RlZCBBcHBlbmQgd2l0
aCBpdHMgb3duIFBhcnRpdGlvblBydW5lSW5mby4KK2NyZWF0ZSBpbmRleCBvbiBwYXJ0X2FiYyAo
YSk7CitjcmVhdGUgdGFibGUgcGFydF9hYmNfMyBwYXJ0aXRpb24gb2YgcGFydF9hYmMgZm9yIHZh
bHVlcyBpbiAoMywgNCkgcGFydGl0aW9uIGJ5IGxpc3QgKGEpOworY3JlYXRlIHRhYmxlIHBhcnRf
YWJjXzNfMyBwYXJ0aXRpb24gb2YgcGFydF9hYmNfMyBmb3IgdmFsdWVzIGluICgzKTsKK2NyZWF0
ZSB0YWJsZSBwYXJ0X2FiY18zXzQgcGFydGl0aW9uIG9mIHBhcnRfYWJjXzMgZm9yIHZhbHVlcyBp
biAoNCk7CitleHBsYWluIChjb3N0cyBvZmYpIHNlbGVjdCBtaW4oYSkgb3ZlciAocGFydGl0aW9u
IGJ5IGEgb3JkZXIgYnkgYSkgZnJvbSBwYXJ0X2FiYyB3aGVyZSBhID49IHN0YWJsZV9vbmUoKSAr
IDE7CisKIGRyb3AgdmlldyBwYXJ0X2FiY192aWV3OwogZHJvcCB0YWJsZSBwYXJ0X2FiYzsKLS0g
CjIuNDMuMAoK
--0000000000005e8d4d062ecb1d86--