Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.

On Tue, Sep 14, 2021 at 10:44 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> writes:
> > I am getting "ERROR: subplan "SubPlan 1" was not initialized" error with
> > below test case.
>
> > CREATE TABLE tbl ( c1 int, c2 int, c3 int ) PARTITION BY LIST (c1);
> > create table tbl_null PARTITION OF tbl FOR VALUES IN (null);
> > create table tbl_def PARTITION OF tbl DEFAULT;
> > insert into tbl values (8800,0,0);
> > insert into tbl values (1891,1,1);
> > insert into tbl values (3420,2,0);
> > insert into tbl values (9850,3,0);
> > insert into tbl values (7164,4,4);
> > analyze tbl;
> > explain (costs off) select count(*) from tbl t1 where (exists(select 1
> from
> > tbl t2 where t2.c1 = t1.c2) or c3 < 0);
>
> > postgres=# explain (costs off) select count(*) from tbl t1 where
> > (exists(select 1 from tbl t2 where t2.c1 = t1.c2) or c3 < 0);
> > ERROR: subplan "SubPlan 1" was not initialized
>
> Nice example. This is failing since 41efb8340. It happens because
> we copy the AlternativeSubPlan for the EXISTS into the scan clauses
> for each of t1's partitions. At setrefs.c time, when
> fix_alternative_subplan() looks at the first of these
> AlternativeSubPlans, it decides it likes the first subplan better,
> so it deletes SubPlan 2 from the root->glob->subplans list. But when
> it comes to the next copy (which is attached to a partition with a
> different number of rows), it likes the second subplan better, so it
> deletes SubPlan 1 from the root->glob->subplans list. Now we have
> SubPlan nodes in the tree with no referents in the global list of
> subplans, so kaboom.
>
> The easiest fix would just be to not try to delete unreferenced
> subplans. The error goes away if I remove the "lfirst(lc2) = NULL"
> statements from fix_alternative_subplan(). However, this is a bit
> annoying since then we will still pay the cost of initializing
> subplans that (in most cases) will never be used. I'm going to
> look into how painful it is to have setrefs.c remove unused subplans
> only at the end, after it's seen all the AlternativeSubPlans.
>
> regards, tom lane
>
>
> Hi,
In the fix, isUsedSubplan is used to tell whether any given subplan is used.
Since only one subplan is used, I wonder if the array can be replaced by
specifying the subplan is used.

Cheers