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

Commit9361f6f

Browse files
committed
After a MINVALUE/MAXVALUE bound, allow only more of the same.
In the old syntax, which used UNBOUNDED, we had a similar restriction,but commitd363d42, which changed thesyntax, eliminated it. Put it back.Patch by me, reviewed by Dean Rasheed.Discussion:http://postgr.es/m/CA+Tgmobs+pLPC27tS3gOpEAxAffHrq5w509cvkwTf9pF6cWYbg@mail.gmail.com
1 parentc29145f commit9361f6f

File tree

8 files changed

+102
-37
lines changed

8 files changed

+102
-37
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -324,11 +324,10 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
324324
</para>
325325

326326
<para>
327-
Note that any values after <literal>MINVALUE</> or
328-
<literal>MAXVALUE</> in a partition bound are ignored; so the bound
329-
<literal>(10, MINVALUE, 0)</> is equivalent to
330-
<literal>(10, MINVALUE, 10)</> and <literal>(10, MINVALUE, MINVALUE)</>
331-
and <literal>(10, MINVALUE, MAXVALUE)</>.
327+
Note that if <literal>MINVALUE</> or <literal>MAXVALUE</> is used for
328+
one column of a partitioning bound, the same value must be used for all
329+
subsequent columns. For example, <literal>(10, MINVALUE, 0)</> is not
330+
a valid bound; you should write <literal>(10, MINVALUE, MINVALUE)</>.
332331
</para>
333332

334333
<para>
@@ -1665,7 +1664,7 @@ CREATE TABLE measurement_y2016m07
16651664
<programlisting>
16661665
CREATE TABLE measurement_ym_older
16671666
PARTITION OF measurement_year_month
1668-
FOR VALUES FROM (MINVALUE,0) TO (2016, 11);
1667+
FOR VALUES FROM (MINVALUE,MINVALUE) TO (2016, 11);
16691668

16701669
CREATE TABLE measurement_ym_y2016m11
16711670
PARTITION OF measurement_year_month

‎src/backend/parser/parse_utilcmd.c

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,7 @@ static void transformConstraintAttrs(CreateStmtContext *cxt,
135135
staticvoidtransformColumnType(CreateStmtContext*cxt,ColumnDef*column);
136136
staticvoidsetSchemaName(char*context_schema,char**stmt_schema_name);
137137
staticvoidtransformPartitionCmd(CreateStmtContext*cxt,PartitionCmd*cmd);
138+
staticvoidvalidateInfiniteBounds(ParseState*pstate,List*blist);
138139
staticConst*transformPartitionBoundValue(ParseState*pstate,A_Const*con,
139140
constchar*colName,OidcolType,int32colTypmod);
140141

@@ -3397,6 +3398,13 @@ transformPartitionBound(ParseState *pstate, Relation parent,
33973398
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
33983399
errmsg("TO must specify exactly one value per partitioning column")));
33993400

3401+
/*
3402+
* Once we see MINVALUE or MAXVALUE for one column, the remaining
3403+
* columns must be the same.
3404+
*/
3405+
validateInfiniteBounds(pstate,spec->lowerdatums);
3406+
validateInfiniteBounds(pstate,spec->upperdatums);
3407+
34003408
/* Transform all the constants */
34013409
i=j=0;
34023410
result_spec->lowerdatums=result_spec->upperdatums=NIL;
@@ -3468,6 +3476,46 @@ transformPartitionBound(ParseState *pstate, Relation parent,
34683476
returnresult_spec;
34693477
}
34703478

3479+
/*
3480+
* validateInfiniteBounds
3481+
*
3482+
* Check that a MAXVALUE or MINVALUE specification in a partition bound is
3483+
* followed only by more of the same.
3484+
*/
3485+
staticvoid
3486+
validateInfiniteBounds(ParseState*pstate,List*blist)
3487+
{
3488+
ListCell*lc;
3489+
PartitionRangeDatumKindkind=PARTITION_RANGE_DATUM_VALUE;
3490+
3491+
foreach(lc,blist)
3492+
{
3493+
PartitionRangeDatum*prd=castNode(PartitionRangeDatum,lfirst(lc));
3494+
3495+
if (kind==prd->kind)
3496+
continue;
3497+
3498+
switch (kind)
3499+
{
3500+
casePARTITION_RANGE_DATUM_VALUE:
3501+
kind=prd->kind;
3502+
break;
3503+
3504+
casePARTITION_RANGE_DATUM_MAXVALUE:
3505+
ereport(ERROR,
3506+
(errcode(ERRCODE_DATATYPE_MISMATCH),
3507+
errmsg("every bound following MAXVALUE must also be MAXVALUE"),
3508+
parser_errposition(pstate,exprLocation((Node*)prd))));
3509+
3510+
casePARTITION_RANGE_DATUM_MINVALUE:
3511+
ereport(ERROR,
3512+
(errcode(ERRCODE_DATATYPE_MISMATCH),
3513+
errmsg("every bound following MINVALUE must also be MINVALUE"),
3514+
parser_errposition(pstate,exprLocation((Node*)prd))));
3515+
}
3516+
}
3517+
}
3518+
34713519
/*
34723520
* Transform one constant in a partition bound spec
34733521
*/

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

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -723,27 +723,27 @@ Number of partitions: 3 (Use \d+ to list them.)
723723

724724
-- check that we get the expected partition constraints
725725
CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
726-
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (MAXVALUE,0, 0);
726+
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE);
727727
\d+ unbounded_range_part
728728
Table "public.unbounded_range_part"
729729
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
730730
--------+---------+-----------+----------+---------+---------+--------------+-------------
731731
a | integer | | | | plain | |
732732
b | integer | | | | plain | |
733733
c | integer | | | | plain | |
734-
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (MAXVALUE,0, 0)
734+
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE)
735735
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))
736736

737737
DROP TABLE unbounded_range_part;
738-
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (1, MAXVALUE,0);
738+
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE);
739739
\d+ range_parted4_1
740740
Table "public.range_parted4_1"
741741
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
742742
--------+---------+-----------+----------+---------+---------+--------------+-------------
743743
a | integer | | | | plain | |
744744
b | integer | | | | plain | |
745745
c | integer | | | | plain | |
746-
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (1, MAXVALUE,0)
746+
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE)
747747
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))
748748

749749
CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
@@ -757,15 +757,15 @@ CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5
757757
Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE)
758758
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))
759759

760-
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,0);
760+
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,MAXVALUE);
761761
\d+ range_parted4_3
762762
Table "public.range_parted4_3"
763763
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
764764
--------+---------+-----------+----------+---------+---------+--------------+-------------
765765
a | integer | | | | plain | |
766766
b | integer | | | | plain | |
767767
c | integer | | | | plain | |
768-
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,0)
768+
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,MAXVALUE)
769769
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))
770770

771771
DROP TABLE range_parted4;

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1853,12 +1853,12 @@ 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 mcrparted0 partition of mcrparted for values from (minvalue,0, 0) to (1, 1, 1);
1856+
create table mcrparted0 partition of mcrparted for values from (minvalue,minvalue, minvalue) to (1, 1, 1);
18571857
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
18581858
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
18591859
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
18601860
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
1861-
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue,0, 0);
1861+
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue,maxvalue, maxvalue);
18621862
explain (costs off) select * from mcrparted where a = 0;-- scans mcrparted0
18631863
QUERY PLAN
18641864
------------------------------

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

Lines changed: 24 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -574,15 +574,28 @@ revoke all on key_desc from someone_else;
574574
revoke all on key_desc_1 from someone_else;
575575
drop role someone_else;
576576
drop table key_desc, key_desc_1;
577+
-- test minvalue/maxvalue restrictions
578+
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
579+
create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue);
580+
ERROR: every bound following MINVALUE must also be MINVALUE
581+
LINE 1: ...partition of mcrparted for values from (minvalue, 0, 0) to (...
582+
^
583+
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue);
584+
ERROR: every bound following MAXVALUE must also be MAXVALUE
585+
LINE 1: ...r values from (10, 6, minvalue) to (10, maxvalue, minvalue);
586+
^
587+
create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue);
588+
ERROR: every bound following MINVALUE must also be MINVALUE
589+
LINE 1: ...ition of mcrparted for values from (21, minvalue, 0) to (30,...
590+
^
577591
-- check multi-column range partitioning expression enforces the same
578592
-- constraint as what tuple-routing would determine it to be
579-
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
580-
create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, 0);
593+
create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue);
581594
create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10);
582-
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue,0);
595+
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue,maxvalue);
583596
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
584-
create table mcrparted4 partition of mcrparted for values from (21, minvalue,0) to (30, 20, maxvalue);
585-
create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue,0, 0);
597+
create table mcrparted4 partition of mcrparted for values from (21, minvalue,minvalue) to (30, 20, maxvalue);
598+
create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue,maxvalue, maxvalue);
586599
-- routed to mcrparted0
587600
insert into mcrparted values (0, 1, 1);
588601
insert into mcrparted0 values (0, 1, 1);
@@ -666,37 +679,37 @@ drop table brtrigpartcon;
666679
drop function brtrigpartcon1trigf();
667680
-- check multi-column range partitioning with minvalue/maxvalue constraints
668681
create table mcrparted (a text, b int) partition by range(a, b);
669-
create table mcrparted1_lt_b partition of mcrparted for values from (minvalue,0) to ('b', minvalue);
682+
create table mcrparted1_lt_b partition of mcrparted for values from (minvalue,minvalue) to ('b', minvalue);
670683
create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue);
671684
create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue);
672685
create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0);
673686
create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10);
674687
create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue);
675688
create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue);
676-
create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue,0);
689+
create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue,maxvalue);
677690
\d+ mcrparted
678691
Table "public.mcrparted"
679692
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
680693
--------+---------+-----------+----------+---------+----------+--------------+-------------
681694
a | text | | | | extended | |
682695
b | integer | | | | plain | |
683696
Partition key: RANGE (a, b)
684-
Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE,0) TO ('b', MINVALUE),
697+
Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE,MINVALUE) TO ('b', MINVALUE),
685698
mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE),
686699
mcrparted3_c_to_common FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE),
687700
mcrparted4_common_lt_0 FOR VALUES FROM ('common', MINVALUE) TO ('common', 0),
688701
mcrparted5_common_0_to_10 FOR VALUES FROM ('common', 0) TO ('common', 10),
689702
mcrparted6_common_ge_10 FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE),
690703
mcrparted7_gt_common_lt_d FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE),
691-
mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,0)
704+
mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,MAXVALUE)
692705

693706
\d+ mcrparted1_lt_b
694707
Table "public.mcrparted1_lt_b"
695708
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
696709
--------+---------+-----------+----------+---------+----------+--------------+-------------
697710
a | text | | | | extended | |
698711
b | integer | | | | plain | |
699-
Partition of: mcrparted FOR VALUES FROM (MINVALUE,0) TO ('b', MINVALUE)
712+
Partition of: mcrparted FOR VALUES FROM (MINVALUE,MINVALUE) TO ('b', MINVALUE)
700713
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text))
701714

702715
\d+ mcrparted2_b
@@ -759,7 +772,7 @@ Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::te
759772
--------+---------+-----------+----------+---------+----------+--------------+-------------
760773
a | text | | | | extended | |
761774
b | integer | | | | plain | |
762-
Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,0)
775+
Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,MAXVALUE)
763776
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text))
764777

765778
insert into mcrparted values ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10),

‎src/test/regress/sql/create_table.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -641,14 +641,14 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
641641

642642
-- check that we get the expected partition constraints
643643
CREATETABLErange_parted4 (aint, bint, cint) PARTITION BY RANGE (abs(a), abs(b), c);
644-
CREATETABLEunbounded_range_part PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,0,0) TO (MAXVALUE,0,0);
644+
CREATETABLEunbounded_range_part PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE);
645645
\d+ unbounded_range_part
646646
DROPTABLE unbounded_range_part;
647-
CREATETABLErange_parted4_1 PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,0,0) TO (1, MAXVALUE,0);
647+
CREATETABLErange_parted4_1 PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE);
648648
\d+ range_parted4_1
649649
CREATETABLErange_parted4_2 PARTITION OF range_parted4 FORVALUESFROM (3,4,5) TO (6,7, MAXVALUE);
650650
\d+ range_parted4_2
651-
CREATETABLErange_parted4_3 PARTITION OF range_parted4 FORVALUESFROM (6,8, MINVALUE) TO (9, MAXVALUE,0);
651+
CREATETABLErange_parted4_3 PARTITION OF range_parted4 FORVALUESFROM (6,8, MINVALUE) TO (9, MAXVALUE,MAXVALUE);
652652
\d+ range_parted4_3
653653
DROPTABLE range_parted4;
654654

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -664,12 +664,12 @@ 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-
createtablemcrparted0 partition of mcrparted forvaluesfrom (minvalue,0,0) to (1,1,1);
667+
createtablemcrparted0 partition of mcrparted forvaluesfrom (minvalue,minvalue, minvalue) to (1,1,1);
668668
createtablemcrparted1 partition of mcrparted forvaluesfrom (1,1,1) to (10,5,10);
669669
createtablemcrparted2 partition of mcrparted forvaluesfrom (10,5,10) to (10,10,10);
670670
createtablemcrparted3 partition of mcrparted forvaluesfrom (11,1,1) to (20,10,10);
671671
createtablemcrparted4 partition of mcrparted forvaluesfrom (20,10,10) to (20,20,20);
672-
createtablemcrparted5 partition of mcrparted forvaluesfrom (20,20,20) to (maxvalue,0,0);
672+
createtablemcrparted5 partition of mcrparted forvaluesfrom (20,20,20) to (maxvalue,maxvalue, maxvalue);
673673
explain (costs off)select*from mcrpartedwhere a=0;-- scans mcrparted0
674674
explain (costs off)select*from mcrpartedwhere a=10and abs(b)<5;-- scans mcrparted1
675675
explain (costs off)select*from mcrpartedwhere a=10and abs(b)=5;-- scans mcrparted1, mcrparted2

0 commit comments

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