Обсуждение: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.

Hi,

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

Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.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

Zhihong Yu <zyu@yugabyte.com> writes:
> 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.

That doesn't seem particularly more convenient.  The point of the bool
array is to merge the results from examination of (possibly) many
AlternativeSubPlans.

Ranier Vilela <ranier.vf@gmail.com> writes:
> I would like to ask if this alternative fix (attached) would also solve the
> problem or not.

If I'm reading the patch correctly, that fixes it by failing to drop
unused subplans at all --- the second loop you have has no external
effect.

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.  The committed
patch seems small enough and cheap enough to be worthwhile.

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.

Ranier Vilela <ranier.vf@gmail.com> writes:
> Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@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.

It looks like what this fragment is doing is clobbering the List
substructure of the AlternativeSubPlan node itself.  That's not
going to make any difference, since the whole point of the exercise
is that the AlternativeSubPlan gets cut out of the finished tree.
But the list that we want to modify, in order to save the
executor time, is the root->glob->subplans list (which ends
up being PlannedStmt.subplans).  And that's global to the
query, so we can't fix it correctly on the basis of a single
AlternativeSubPlan.

Em qua., 15 de set. de 2021 às 15:35, Tom Lane <tgl@sss.pgh.pa.us> escreveu:

Ranier Vilela <ranier.vf@gmail.com> writes:
> Em qua., 15 de set. de 2021 às 12:00, Tom Lane <tgl@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.

It looks like what this fragment is doing is clobbering the List
substructure of the AlternativeSubPlan node itself.  That's not
going to make any difference, since the whole point of the exercise
is that the AlternativeSubPlan gets cut out of the finished tree.
But the list that we want to modify, in order to save the
executor time, is the root->glob->subplans list (which ends
up being PlannedStmt.subplans).  And that's global to the
query, so we can't fix it correctly on the basis of a single
AlternativeSubPlan.

Ok, I can see now.

But this leads me to the conclusion that AlternativeSubPlan *asplan

does not seem to me to be a good approach for this function, better to deal with it directly:
"root->glob->subplans" which, it seems, works too.