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

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