[sqlsmith] ERROR: partition missing from subplans
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
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
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
Attachments:
fix_partition_missing_from_subplans_error.patchapplication/octet-stream; name=fix_partition_missing_from_subplans_error.patchDownload
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index d13be4145f..95814921fb 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -1886,8 +1886,14 @@ find_matching_subplans_recurse(PartitionPruningData *prunedata,
initial_prune, validsubplans);
else
{
- /* Shouldn't happen */
- elog(ERROR, "partition missing from subplans");
+ /*
+ * It's possible to get here for sub-partitioned tables when
+ * the planner found that none of the sub-partitions matched,
+ * therefore, pruned all its partitions. The same should
+ * happen here but we can't verify that as we have no pruning
+ * steps set up to call get_matching_partitions on the
+ * sub-partition, so let's just silently ignore it.
+ */
}
}
}
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 693c348185..24313e8c78 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3570,3 +3570,21 @@ execute q (1, 1);
reset plan_cache_mode;
drop table p, q;
+-- Ensure run-time pruning works correctly when we match a partitioned table
+-- on the first level but find no matching partitions on the second level.
+create table listp (a int, b int) partition by list (a);
+create table listp1 partition of listp for values in(1);
+create table listp2 partition of listp for values in(2) partition by list(b);
+create table listp2_10 partition of listp2 for values in (10);
+explain (analyze, costs off, summary off, timing off)
+select * from listp where a = (select 2) and b <> 10;
+ QUERY PLAN
+-------------------------------------------
+ Append (actual rows=0 loops=1)
+ InitPlan 1 (returns $0)
+ -> Result (actual rows=1 loops=1)
+ -> Seq Scan on listp1 (never executed)
+ Filter: ((b <> 10) AND (a = $0))
+(5 rows)
+
+drop table listp;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 935c509b29..eca1a7c5ac 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -946,3 +946,15 @@ execute q (1, 1);
reset plan_cache_mode;
drop table p, q;
+
+-- Ensure run-time pruning works correctly when we match a partitioned table
+-- on the first level but find no matching partitions on the second level.
+create table listp (a int, b int) partition by list (a);
+create table listp1 partition of listp for values in(1);
+create table listp2 partition of listp for values in(2) partition by list(b);
+create table listp2_10 partition of listp2 for values in (10);
+
+explain (analyze, costs off, summary off, timing off)
+select * from listp where a = (select 2) and b <> 10;
+
+drop table listp;
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
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