Re: generic plans and "initial" pruning

On Sun, Feb 23, 2025 at 9:46 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> Amit Langote <amitlangote09(at)gmail(dot)com> 于2025年2月23日周日 16:36写道：
>> On Sun, Feb 23, 2025 at 2:03 AM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
>> > Alexander Lakhin <exclusion(at)gmail(dot)com> 于2025年2月22日周六 23:00写道：
>> >> 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; $$ LANGUAGE plpgsql STABLE;
>> >>
>> >> SELECT min(id) OVER (PARTITION BY id ORDER BY id) FROM t WHERE id >= 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 estate->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 = 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 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’t
>> 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.

--
Thanks, Amit Langote