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>
 <CA+HiwqFp3jZGSz==QjeuV62_62F6+V6b62=Uqvy99sW_gsgWBA@mail.gmail.com> <CAHewXNkUz9XGG8nnoxZaw35e+5bQVVP=eeJE4cW4V2e+P9ndFw@mail.gmail.com>
In-Reply-To: <CAHewXNkUz9XGG8nnoxZaw35e+5bQVVP=eeJE4cW4V2e+P9ndFw@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Tue, 25 Feb 2025 11:51:31 +0900
Message-ID: <CA+HiwqFKSpfYruzcVz-5CcFxg7gMa+ycXjMa2aPz_J_P4LGXTg@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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqFKSpfYruzcVz-5CcFxg7gMa%2BycXjMa2aPz_J_P4LGXTg%40mail.gmail.com>
Precedence: bulk

On Sun, Feb 23, 2025 at 9:46=E2=80=AFPM Tender Wang <tndrwang@gmail.com> wr=
ote:
> Amit Langote <amitlangote09@gmail.com> =E4=BA=8E2025=E5=B9=B42=E6=9C=8823=
=E6=97=A5=E5=91=A8=E6=97=A5 16:36=E5=86=99=E9=81=93=EF=BC=9A
>> On Sun, Feb 23, 2025 at 2:03=E2=80=AFAM Tender Wang <tndrwang@gmail.com>=
 wrote:
>> > 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
>> >> 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) PARTITI=
ON 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; $=
$ LANGUAGE plpgsql STABLE;
>> >>
>> >> SELECT min(id) OVER (PARTITION BY id ORDER BY id) FROM t WHERE id >=
=3D stable_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 es=
tate->es_unpruned_relids only includes 1, which is the offset of table t.
>> > In register_partpruneinfo(), we collect glob->prunableRelids; in this =
case, 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 cre=
ate_append_plan() is put into the head of global list.
>> > So we first process it in ExecDoInitialPruning().  Then error reports =
because 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.
>
> LGTM.

Pushed after some tweaks to comments and the test case.

--=20
Thanks, Amit Langote