From 7b88d63a9122646ead60c1afffc248a31d4e457d Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 28 Nov 2017 10:51:01 -0500 Subject: [PATCH] Add null test to partition constraint for default range partitions. Non-default range partitions have a constraint which include null tests, and both default and non-default list partitions also have a constraint which includes null tests, but for some reason this was missed for default range partitions. This could cause the partition constraint to evaluate to false for rows that were (correctly) routed to that partition by insert tuple routing, which could in turn cause constraint exclusion to prune the default partition in cases where it should not. Amit Langote, reviewed by Kyotaro Horiguchi Discussion: http://postgr.es/m/ba7aaeb1-4399-220e-70b4-62eade1522d0@lab.ntt.co.jp --- src/backend/catalog/partition.c | 29 +++++++++++++++---- src/test/regress/expected/inherit.out | 41 ++++++++++++++++++--------- src/test/regress/expected/update.out | 2 +- src/test/regress/sql/inherit.sql | 9 +++--- 4 files changed, 56 insertions(+), 25 deletions(-) diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c index 9a44cceb22..e032c11ed4 100644 --- a/src/backend/catalog/partition.c +++ b/src/backend/catalog/partition.c @@ -2134,12 +2134,29 @@ get_qual_for_range(Relation parent, PartitionBoundSpec *spec, if (or_expr_args != NIL) { - /* OR all the non-default partition constraints; then negate it */ - result = lappend(result, - list_length(or_expr_args) > 1 - ? makeBoolExpr(OR_EXPR, or_expr_args, -1) - : linitial(or_expr_args)); - result = list_make1(makeBoolExpr(NOT_EXPR, result, -1)); + Expr *other_parts_constr; + + /* + * Combine the constraints obtained for non-default partitions + * using OR. As requested, each of the OR's args doesn't include + * the NOT NULL test for partition keys (which is to avoid its + * useless repetition). Add the same now. + */ + other_parts_constr = + makeBoolExpr(AND_EXPR, + lappend(get_range_nulltest(key), + list_length(or_expr_args) > 1 + ? makeBoolExpr(OR_EXPR, or_expr_args, + -1) + : linitial(or_expr_args)), + -1); + + /* + * Finally, the default partition contains everything *NOT* + * contained in the non-default partitions. + */ + result = list_make1(makeBoolExpr(NOT_EXPR, + list_make1(other_parts_constr), -1)); } return result; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index c698faff2f..fac7b62f9c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1853,29 +1853,34 @@ drop table range_list_parted; -- check that constraint exclusion is able to cope with the partition -- constraint emitted for multi-column range partitioned tables create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); +create table mcrparted_def partition of mcrparted default; create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); -explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0 - QUERY PLAN ------------------------------- +explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def + QUERY PLAN +--------------------------------- Append -> Seq Scan on mcrparted0 Filter: (a = 0) -(3 rows) + -> Seq Scan on mcrparted_def + Filter: (a = 0) +(5 rows) -explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1 +explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def QUERY PLAN --------------------------------------------- Append -> Seq Scan on mcrparted1 Filter: ((a = 10) AND (abs(b) < 5)) -(3 rows) + -> Seq Scan on mcrparted_def + Filter: ((a = 10) AND (abs(b) < 5)) +(5 rows) -explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2 +explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def QUERY PLAN --------------------------------------------- Append @@ -1883,11 +1888,13 @@ explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scan Filter: ((a = 10) AND (abs(b) = 5)) -> Seq Scan on mcrparted2 Filter: ((a = 10) AND (abs(b) = 5)) -(5 rows) + -> Seq Scan on mcrparted_def + Filter: ((a = 10) AND (abs(b) = 5)) +(7 rows) explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions - QUERY PLAN ------------------------------- + QUERY PLAN +--------------------------------- Append -> Seq Scan on mcrparted0 Filter: (abs(b) = 5) @@ -1899,7 +1906,9 @@ explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all parti Filter: (abs(b) = 5) -> Seq Scan on mcrparted5 Filter: (abs(b) = 5) -(11 rows) + -> Seq Scan on mcrparted_def + Filter: (abs(b) = 5) +(13 rows) explain (costs off) select * from mcrparted where a > -1; -- scans all partitions QUERY PLAN @@ -1917,7 +1926,9 @@ explain (costs off) select * from mcrparted where a > -1; -- scans all partition Filter: (a > '-1'::integer) -> Seq Scan on mcrparted5 Filter: (a > '-1'::integer) -(13 rows) + -> Seq Scan on mcrparted_def + Filter: (a > '-1'::integer) +(15 rows) explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 QUERY PLAN @@ -1927,7 +1938,7 @@ explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) (3 rows) -explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5 +explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def QUERY PLAN ----------------------------------------- Append @@ -1937,7 +1948,9 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc Filter: ((c > 20) AND (a = 20)) -> Seq Scan on mcrparted5 Filter: ((c > 20) AND (a = 20)) -(7 rows) + -> Seq Scan on mcrparted_def + Filter: ((c > 20) AND (a = 20)) +(9 rows) drop table mcrparted; -- check that partitioned table Appends cope with being referenced in diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index a4fe96112e..b69ceaa75e 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -227,7 +227,7 @@ create table part_def partition of range_parted default; a | text | | | | extended | | b | integer | | | | plain | | Partition of: range_parted DEFAULT -Partition constraint: (NOT (((a = 'a'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'a'::text) AND (b >= 10) AND (b < 20)) OR ((a = 'b'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'b'::text) AND (b >= 10) AND (b < 20)))) +Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a = 'a'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'a'::text) AND (b >= 10) AND (b < 20)) OR ((a = 'b'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'b'::text) AND (b >= 10) AND (b < 20))))) insert into range_parted values ('c', 9); -- ok diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 169d0dc0f5..c71febffc2 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -664,19 +664,20 @@ drop table range_list_parted; -- check that constraint exclusion is able to cope with the partition -- constraint emitted for multi-column range partitioned tables create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); +create table mcrparted_def partition of mcrparted default; create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); -explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0 -explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1 -explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2 +explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def +explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def +explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions explain (costs off) select * from mcrparted where a > -1; -- scans all partitions explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 -explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5 +explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def drop table mcrparted; -- check that partitioned table Appends cope with being referenced in -- 2.40.0