Re: [sqlsmith] ERROR: partition missing from subplans

On 11 August 2018 at 10:12, Andreas Seltenreich <seltenreich@gmx.de> wrote:
> running sqlsmith on REL_11_STABLE at 1b9d1b08fe for a couple hours
> yielded the previously-unseen internal error "partition missing from
> subplans".  It is readily reproducible on the regression database with
> the following query:
>
> select * from public.fk_partitioned_fk as sample_0 tablesample system (9.4)
>    inner join public.money_data as sample_1
>       on ((select pg_catalog.min(int_two) from public.test_type_diff2_c3) <> sample_0.a)
>     where (sample_0.b is NULL);

Thanks for reporting this.

Here's a simplified self-contained test case:

drop table listp;
create table listp (a int, b int) partition by list (a);
create table listp1 partition of listp for values in(10);
create table listp2 partition of listp for values in(13) partition by range(b);

create table listp2_1 partition of listp2 for values from (0) to (1000);
create table listp2_2 partition of listp2 for values from (1000) to (2000);

explain analyze select sample_0.tableoid::regclass,* from public.listp
as sample_0
   inner join (select 0) a
      on (select 7) <> sample_0.a and b is null;

This seems to be caused by the fact that partition pruning that's done
on listp will match the listp2 relation, but when the pruning is done
on listp2 it matches no partitions. In set_append_rel_size() the
following code causes these partitions to be pruned:

/*
* Compute the child's size.
*/
set_rel_size(root, childrel, childRTindex, childRTE);

/*
* It is possible that constraint exclusion detected a contradiction
* within a child subquery, even though we didn't prove one above. If
* so, we can skip this child.
*/
if (IS_DUMMY_REL(childrel))
    continue;

This is because the recursive search is done first and it realises
that no sub-partitions match so there's no point in including that
partitioned table.  The same case in the executor complains that no
subplans were found for the partition that pruning says must match.

We could remove the error path and simply ignore these, but I put it
there because I thought it might actually capture some bugs, but given
this discovery I can't see a way to keep it since to verify that
listp2 is truly not required we'd need to perform pruning on it and
verify that no partitions match. That's not really possible since
we've not set up any pruning steps for listp2.  So my best idea on a
fix is simply to remove the code that raises the error.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services