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

[Ranier Vilela]

Em qua., 15 de set. de 2021 às 12:00, Tom Lane <t...@sss.pgh.pa.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