Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7b88d63

Browse files
committed
Add null test to partition constraint for default range partitions.
Non-default range partitions have a constraint which include nulltests, and both default and non-default list partitions also have aconstraint which includes null tests, but for some reason this wasmissed for default range partitions. This could cause the partitionconstraint to evaluate to false for rows that were (correctly) routedto that partition by insert tuple routing, which could in turncause constraint exclusion to prune the default partition in caseswhere it should not.Amit Langote, reviewed by Kyotaro HoriguchiDiscussion:http://postgr.es/m/ba7aaeb1-4399-220e-70b4-62eade1522d0@lab.ntt.co.jp
1 parent487a0c1 commit7b88d63

File tree

4 files changed

+56
-25
lines changed

4 files changed

+56
-25
lines changed

‎src/backend/catalog/partition.c

Lines changed: 23 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2134,12 +2134,29 @@ get_qual_for_range(Relation parent, PartitionBoundSpec *spec,
21342134

21352135
if (or_expr_args!=NIL)
21362136
{
2137-
/* OR all the non-default partition constraints; then negate it */
2138-
result=lappend(result,
2139-
list_length(or_expr_args)>1
2140-
?makeBoolExpr(OR_EXPR,or_expr_args,-1)
2141-
:linitial(or_expr_args));
2142-
result=list_make1(makeBoolExpr(NOT_EXPR,result,-1));
2137+
Expr*other_parts_constr;
2138+
2139+
/*
2140+
* Combine the constraints obtained for non-default partitions
2141+
* using OR. As requested, each of the OR's args doesn't include
2142+
* the NOT NULL test for partition keys (which is to avoid its
2143+
* useless repetition). Add the same now.
2144+
*/
2145+
other_parts_constr=
2146+
makeBoolExpr(AND_EXPR,
2147+
lappend(get_range_nulltest(key),
2148+
list_length(or_expr_args)>1
2149+
?makeBoolExpr(OR_EXPR,or_expr_args,
2150+
-1)
2151+
:linitial(or_expr_args)),
2152+
-1);
2153+
2154+
/*
2155+
* Finally, the default partition contains everything *NOT*
2156+
* contained in the non-default partitions.
2157+
*/
2158+
result=list_make1(makeBoolExpr(NOT_EXPR,
2159+
list_make1(other_parts_constr),-1));
21432160
}
21442161

21452162
returnresult;

‎src/test/regress/expected/inherit.out

Lines changed: 27 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1853,41 +1853,48 @@ drop table range_list_parted;
18531853
-- check that constraint exclusion is able to cope with the partition
18541854
-- constraint emitted for multi-column range partitioned tables
18551855
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
1856+
create table mcrparted_def partition of mcrparted default;
18561857
create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
18571858
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
18581859
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
18591860
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
18601861
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
18611862
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
1862-
explain (costs off) select * from mcrparted where a = 0;-- scans mcrparted0
1863-
QUERY PLAN
1864-
------------------------------
1863+
explain (costs off) select * from mcrparted where a = 0;-- scans mcrparted0, mcrparted_def
1864+
QUERY PLAN
1865+
---------------------------------
18651866
Append
18661867
-> Seq Scan on mcrparted0
18671868
Filter: (a = 0)
1868-
(3 rows)
1869+
-> Seq Scan on mcrparted_def
1870+
Filter: (a = 0)
1871+
(5 rows)
18691872

1870-
explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;-- scans mcrparted1
1873+
explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;-- scans mcrparted1, mcrparted_def
18711874
QUERY PLAN
18721875
---------------------------------------------
18731876
Append
18741877
-> Seq Scan on mcrparted1
18751878
Filter: ((a = 10) AND (abs(b) < 5))
1876-
(3 rows)
1879+
-> Seq Scan on mcrparted_def
1880+
Filter: ((a = 10) AND (abs(b) < 5))
1881+
(5 rows)
18771882

1878-
explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;-- scans mcrparted1, mcrparted2
1883+
explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;-- scans mcrparted1, mcrparted2, mcrparted_def
18791884
QUERY PLAN
18801885
---------------------------------------------
18811886
Append
18821887
-> Seq Scan on mcrparted1
18831888
Filter: ((a = 10) AND (abs(b) = 5))
18841889
-> Seq Scan on mcrparted2
18851890
Filter: ((a = 10) AND (abs(b) = 5))
1886-
(5 rows)
1891+
-> Seq Scan on mcrparted_def
1892+
Filter: ((a = 10) AND (abs(b) = 5))
1893+
(7 rows)
18871894

18881895
explain (costs off) select * from mcrparted where abs(b) = 5;-- scans all partitions
1889-
QUERY PLAN
1890-
------------------------------
1896+
QUERY PLAN
1897+
---------------------------------
18911898
Append
18921899
-> Seq Scan on mcrparted0
18931900
Filter: (abs(b) = 5)
@@ -1899,7 +1906,9 @@ explain (costs off) select * from mcrparted where abs(b) = 5;-- scans all parti
18991906
Filter: (abs(b) = 5)
19001907
-> Seq Scan on mcrparted5
19011908
Filter: (abs(b) = 5)
1902-
(11 rows)
1909+
-> Seq Scan on mcrparted_def
1910+
Filter: (abs(b) = 5)
1911+
(13 rows)
19031912

19041913
explain (costs off) select * from mcrparted where a > -1;-- scans all partitions
19051914
QUERY PLAN
@@ -1917,7 +1926,9 @@ explain (costs off) select * from mcrparted where a > -1;-- scans all partition
19171926
Filter: (a > '-1'::integer)
19181927
-> Seq Scan on mcrparted5
19191928
Filter: (a > '-1'::integer)
1920-
(13 rows)
1929+
-> Seq Scan on mcrparted_def
1930+
Filter: (a > '-1'::integer)
1931+
(15 rows)
19211932

19221933
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;-- scans mcrparted4
19231934
QUERY PLAN
@@ -1927,7 +1938,7 @@ explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c >
19271938
Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
19281939
(3 rows)
19291940

1930-
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
1941+
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
19311942
QUERY PLAN
19321943
-----------------------------------------
19331944
Append
@@ -1937,7 +1948,9 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc
19371948
Filter: ((c > 20) AND (a = 20))
19381949
-> Seq Scan on mcrparted5
19391950
Filter: ((c > 20) AND (a = 20))
1940-
(7 rows)
1951+
-> Seq Scan on mcrparted_def
1952+
Filter: ((c > 20) AND (a = 20))
1953+
(9 rows)
19411954

19421955
drop table mcrparted;
19431956
-- check that partitioned table Appends cope with being referenced in

‎src/test/regress/expected/update.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -227,7 +227,7 @@ create table part_def partition of range_parted default;
227227
a | text | | | | extended | |
228228
b | integer | | | | plain | |
229229
Partition of: range_parted DEFAULT
230-
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))))
230+
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)))))
231231

232232
insert into range_parted values ('c', 9);
233233
-- ok

‎src/test/regress/sql/inherit.sql

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -664,19 +664,20 @@ drop table range_list_parted;
664664
-- check that constraint exclusion is able to cope with the partition
665665
-- constraint emitted for multi-column range partitioned tables
666666
createtablemcrparted (aint, bint, cint) partition by range (a, abs(b), c);
667+
createtablemcrparted_def partition of mcrparted default;
667668
createtablemcrparted0 partition of mcrparted forvaluesfrom (minvalue, minvalue, minvalue) to (1,1,1);
668669
createtablemcrparted1 partition of mcrparted forvaluesfrom (1,1,1) to (10,5,10);
669670
createtablemcrparted2 partition of mcrparted forvaluesfrom (10,5,10) to (10,10,10);
670671
createtablemcrparted3 partition of mcrparted forvaluesfrom (11,1,1) to (20,10,10);
671672
createtablemcrparted4 partition of mcrparted forvaluesfrom (20,10,10) to (20,20,20);
672673
createtablemcrparted5 partition of mcrparted forvaluesfrom (20,20,20) to (maxvalue, maxvalue, maxvalue);
673-
explain (costs off)select*from mcrpartedwhere a=0;-- scans mcrparted0
674-
explain (costs off)select*from mcrpartedwhere a=10and abs(b)<5;-- scans mcrparted1
675-
explain (costs off)select*from mcrpartedwhere a=10and abs(b)=5;-- scans mcrparted1, mcrparted2
674+
explain (costs off)select*from mcrpartedwhere a=0;-- scans mcrparted0, mcrparted_def
675+
explain (costs off)select*from mcrpartedwhere a=10and abs(b)<5;-- scans mcrparted1, mcrparted_def
676+
explain (costs off)select*from mcrpartedwhere a=10and abs(b)=5;-- scans mcrparted1, mcrparted2, mcrparted_def
676677
explain (costs off)select*from mcrpartedwhere abs(b)=5;-- scans all partitions
677678
explain (costs off)select*from mcrpartedwhere a>-1;-- scans all partitions
678679
explain (costs off)select*from mcrpartedwhere a=20and abs(b)=10and c>10;-- scans mcrparted4
679-
explain (costs off)select*from mcrpartedwhere a=20and c>20;-- scans mcrparted3, mcrparte4, mcrparte5
680+
explain (costs off)select*from mcrpartedwhere a=20and c>20;-- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
680681
droptable mcrparted;
681682

682683
-- check that partitioned table Appends cope with being referenced in

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp