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

Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:

> We could, in fact, not bother with removing the no-longer-referenced
> subplans, and it probably wouldn't be all that awful. But the intent
> of the original patch was to save the executor startup time for such
> subplans, so I wanted to preserve that goal if I could.
>

I'm sorry if I'm being persistent with this issue, but I'd like to give it
one last try before I let it go
I modified the way the subplane deletion is done and it seems to me that
this really happens.

I ran a quick dirty test to count the remaining subplanes.

i = 0;
foreach(lc, asplan->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
Cost curcost;

curcost = curplan->startup_cost + num_exec * curplan->per_call_cost;
if (bestplan == NULL || curcost <= bestcost)
{
bestplan = curplan;
bestcost = curcost;
}
i++;
}
if (bestplan != NULL)
{
foreach(lc, asplan->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
if (curplan != bestplan)
lfirst(lc) = NULL;
}
j = 0;
foreach(lc, asplan->subplans)
{
SubPlan *curplan = (SubPlan *) lfirst(lc);
if (curplan != NULL)
j++;
}
if (j != i)
{
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("too many subplans: total_plans=%d, remain_plans=%d",
i, j)));
}
}

explain (costs off)
postgres-# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
ERROR: too many subplans: total_plans=2, remain_plans=1
postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
ERROR: too many subplans: total_plans=2, remain_plans=1

I think that works:
lfirst(lc) = NULL;

regards,
Ranier Vilela