Horton

Thread

Commits

  1. Fix executor prune failure when plan already pruned

  1. [sqlsmith] ERROR: partition missing from subplans

    Andreas Seltenreich <seltenreich@gmx.de> — 2018-08-10T22:12:07Z

    Hi,

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);

                        QUERY PLAN
-----------------------------------------------------------
 Nested Loop
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_type_diff2_c3
   ->  Seq Scan on money_data sample_1
   ->  Append
         ->  Sample Scan on fk_partitioned_fk_1 sample_0
               Sampling: system ('9.4'::real)
               Filter: ((b IS NULL) AND ($0 <> a))
         ->  Sample Scan on fk_partitioned_fk_3 sample_0_1
               Sampling: system ('9.4'::real)
               Filter: ((b IS NULL) AND ($0 <> a))

regards,
Andreas

  2. Re: [sqlsmith] ERROR: partition missing from subplans

    David Rowley <david.rowley@2ndquadrant.com> — 2018-08-11T02:09:20Z

    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

  3. Re: [sqlsmith] ERROR: partition missing from subplans

    David Rowley <david.rowley@2ndquadrant.com> — 2018-08-11T03:32:58Z

    On 11 August 2018 at 14:09, David Rowley <david.rowley@2ndquadrant.com> wrote:
> So my best idea on a
> fix is simply to remove the code that raises the error.

Here's a patch to do that. I've also included a further simplified
test to ensure this case performs run-time pruning correctly.

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

  4. Re: [sqlsmith] ERROR: partition missing from subplans

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-08-16T16:01:30Z

    On 2018-Aug-11, David Rowley wrote:

> On 11 August 2018 at 14:09, David Rowley <david.rowley@2ndquadrant.com> wrote:
> > So my best idea on a
> > fix is simply to remove the code that raises the error.
> 
> Here's a patch to do that. I've also included a further simplified
> test to ensure this case performs run-time pruning correctly.

Looks good, pushed.  I edited the comment a little bit.

Thanks!

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  5. Re: [sqlsmith] ERROR: partition missing from subplans

    David Rowley <david.rowley@2ndquadrant.com> — 2018-08-19T22:47:17Z

    On 17 August 2018 at 04:01, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Looks good, pushed.  I edited the comment a little bit.

Thanks for pushing.


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