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

Commite8b6598

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 parentf830183 commite8b6598

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
@@ -322,11 +322,10 @@ FROM ( { <replaceable class="PARAMETER">numeric_literal</replaceable> | <replace
322322
</para>
323323

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

332331
<para>
@@ -1643,7 +1642,7 @@ CREATE TABLE measurement_y2016m07
16431642
<programlisting>
16441643
CREATE TABLE measurement_ym_older
16451644
PARTITION OF measurement_year_month
1646-
FOR VALUES FROM (MINVALUE,0) TO (2016, 11);
1645+
FOR VALUES FROM (MINVALUE,MINVALUE) TO (2016, 11);
16471646

16481647
CREATE TABLE measurement_ym_y2016m11
16491648
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

@@ -3382,6 +3383,13 @@ transformPartitionBound(ParseState *pstate, Relation parent,
33823383
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
33833384
errmsg("TO must specify exactly one value per partitioning column")));
33843385

3386+
/*
3387+
* Once we see MINVALUE or MAXVALUE for one column, the remaining
3388+
* columns must be the same.
3389+
*/
3390+
validateInfiniteBounds(pstate,spec->lowerdatums);
3391+
validateInfiniteBounds(pstate,spec->upperdatums);
3392+
33853393
/* Transform all the constants */
33863394
i=j=0;
33873395
result_spec->lowerdatums=result_spec->upperdatums=NIL;
@@ -3453,6 +3461,46 @@ transformPartitionBound(ParseState *pstate, Relation parent,
34533461
returnresult_spec;
34543462
}
34553463

3464+
/*
3465+
* validateInfiniteBounds
3466+
*
3467+
* Check that a MAXVALUE or MINVALUE specification in a partition bound is
3468+
* followed only by more of the same.
3469+
*/
3470+
staticvoid
3471+
validateInfiniteBounds(ParseState*pstate,List*blist)
3472+
{
3473+
ListCell*lc;
3474+
PartitionRangeDatumKindkind=PARTITION_RANGE_DATUM_VALUE;
3475+
3476+
foreach(lc,blist)
3477+
{
3478+
PartitionRangeDatum*prd=castNode(PartitionRangeDatum,lfirst(lc));
3479+
3480+
if (kind==prd->kind)
3481+
continue;
3482+
3483+
switch (kind)
3484+
{
3485+
casePARTITION_RANGE_DATUM_VALUE:
3486+
kind=prd->kind;
3487+
break;
3488+
3489+
casePARTITION_RANGE_DATUM_MAXVALUE:
3490+
ereport(ERROR,
3491+
(errcode(ERRCODE_DATATYPE_MISMATCH),
3492+
errmsg("every bound following MAXVALUE must also be MAXVALUE"),
3493+
parser_errposition(pstate,exprLocation((Node*)prd))));
3494+
3495+
casePARTITION_RANGE_DATUM_MINVALUE:
3496+
ereport(ERROR,
3497+
(errcode(ERRCODE_DATATYPE_MISMATCH),
3498+
errmsg("every bound following MINVALUE must also be MINVALUE"),
3499+
parser_errposition(pstate,exprLocation((Node*)prd))));
3500+
}
3501+
}
3502+
}
3503+
34563504
/*
34573505
* Transform one constant in a partition bound spec
34583506
*/

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

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

704704
-- check that we get the expected partition constraints
705705
CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
706-
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (MAXVALUE,0, 0);
706+
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE);
707707
\d+ unbounded_range_part
708708
Table "public.unbounded_range_part"
709709
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
710710
--------+---------+-----------+----------+---------+---------+--------------+-------------
711711
a | integer | | | | plain | |
712712
b | integer | | | | plain | |
713713
c | integer | | | | plain | |
714-
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (MAXVALUE,0, 0)
714+
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE)
715715
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))
716716

717717
DROP TABLE unbounded_range_part;
718-
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (1, MAXVALUE,0);
718+
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE);
719719
\d+ range_parted4_1
720720
Table "public.range_parted4_1"
721721
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
722722
--------+---------+-----------+----------+---------+---------+--------------+-------------
723723
a | integer | | | | plain | |
724724
b | integer | | | | plain | |
725725
c | integer | | | | plain | |
726-
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,0, 0) TO (1, MAXVALUE,0)
726+
Partition of: range_parted4 FOR VALUES FROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE)
727727
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))
728728

729729
CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
@@ -737,15 +737,15 @@ CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5
737737
Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE)
738738
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))))
739739

740-
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,0);
740+
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,MAXVALUE);
741741
\d+ range_parted4_3
742742
Table "public.range_parted4_3"
743743
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
744744
--------+---------+-----------+----------+---------+---------+--------------+-------------
745745
a | integer | | | | plain | |
746746
b | integer | | | | plain | |
747747
c | integer | | | | plain | |
748-
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,0)
748+
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE,MAXVALUE)
749749
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))
750750

751751
DROP TABLE range_parted4;

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1831,12 +1831,12 @@ drop table range_list_parted;
18311831
-- check that constraint exclusion is able to cope with the partition
18321832
-- constraint emitted for multi-column range partitioned tables
18331833
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
1834-
create table mcrparted0 partition of mcrparted for values from (minvalue,0, 0) to (1, 1, 1);
1834+
create table mcrparted0 partition of mcrparted for values from (minvalue,minvalue, minvalue) to (1, 1, 1);
18351835
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
18361836
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
18371837
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
18381838
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
1839-
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue,0, 0);
1839+
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue,maxvalue, maxvalue);
18401840
explain (costs off) select * from mcrparted where a = 0;-- scans mcrparted0
18411841
QUERY PLAN
18421842
------------------------------

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

Lines changed: 24 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -451,15 +451,28 @@ revoke all on key_desc from someone_else;
451451
revoke all on key_desc_1 from someone_else;
452452
drop role someone_else;
453453
drop table key_desc, key_desc_1;
454+
-- test minvalue/maxvalue restrictions
455+
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
456+
create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, maxvalue);
457+
ERROR: every bound following MINVALUE must also be MINVALUE
458+
LINE 1: ...partition of mcrparted for values from (minvalue, 0, 0) to (...
459+
^
460+
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue, minvalue);
461+
ERROR: every bound following MAXVALUE must also be MAXVALUE
462+
LINE 1: ...r values from (10, 6, minvalue) to (10, maxvalue, minvalue);
463+
^
464+
create table mcrparted4 partition of mcrparted for values from (21, minvalue, 0) to (30, 20, minvalue);
465+
ERROR: every bound following MINVALUE must also be MINVALUE
466+
LINE 1: ...ition of mcrparted for values from (21, minvalue, 0) to (30,...
467+
^
454468
-- check multi-column range partitioning expression enforces the same
455469
-- constraint as what tuple-routing would determine it to be
456-
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
457-
create table mcrparted0 partition of mcrparted for values from (minvalue, 0, 0) to (1, maxvalue, 0);
470+
create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, maxvalue, maxvalue);
458471
create table mcrparted1 partition of mcrparted for values from (2, 1, minvalue) to (10, 5, 10);
459-
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue,0);
472+
create table mcrparted2 partition of mcrparted for values from (10, 6, minvalue) to (10, maxvalue,maxvalue);
460473
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
461-
create table mcrparted4 partition of mcrparted for values from (21, minvalue,0) to (30, 20, maxvalue);
462-
create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue,0, 0);
474+
create table mcrparted4 partition of mcrparted for values from (21, minvalue,minvalue) to (30, 20, maxvalue);
475+
create table mcrparted5 partition of mcrparted for values from (30, 21, 20) to (maxvalue,maxvalue, maxvalue);
463476
-- routed to mcrparted0
464477
insert into mcrparted values (0, 1, 1);
465478
insert into mcrparted0 values (0, 1, 1);
@@ -543,37 +556,37 @@ drop table brtrigpartcon;
543556
drop function brtrigpartcon1trigf();
544557
-- check multi-column range partitioning with minvalue/maxvalue constraints
545558
create table mcrparted (a text, b int) partition by range(a, b);
546-
create table mcrparted1_lt_b partition of mcrparted for values from (minvalue,0) to ('b', minvalue);
559+
create table mcrparted1_lt_b partition of mcrparted for values from (minvalue,minvalue) to ('b', minvalue);
547560
create table mcrparted2_b partition of mcrparted for values from ('b', minvalue) to ('c', minvalue);
548561
create table mcrparted3_c_to_common partition of mcrparted for values from ('c', minvalue) to ('common', minvalue);
549562
create table mcrparted4_common_lt_0 partition of mcrparted for values from ('common', minvalue) to ('common', 0);
550563
create table mcrparted5_common_0_to_10 partition of mcrparted for values from ('common', 0) to ('common', 10);
551564
create table mcrparted6_common_ge_10 partition of mcrparted for values from ('common', 10) to ('common', maxvalue);
552565
create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue);
553-
create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue,0);
566+
create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue,maxvalue);
554567
\d+ mcrparted
555568
Table "public.mcrparted"
556569
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
557570
--------+---------+-----------+----------+---------+----------+--------------+-------------
558571
a | text | | | | extended | |
559572
b | integer | | | | plain | |
560573
Partition key: RANGE (a, b)
561-
Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE,0) TO ('b', MINVALUE),
574+
Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE,MINVALUE) TO ('b', MINVALUE),
562575
mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE),
563576
mcrparted3_c_to_common FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE),
564577
mcrparted4_common_lt_0 FOR VALUES FROM ('common', MINVALUE) TO ('common', 0),
565578
mcrparted5_common_0_to_10 FOR VALUES FROM ('common', 0) TO ('common', 10),
566579
mcrparted6_common_ge_10 FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE),
567580
mcrparted7_gt_common_lt_d FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE),
568-
mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,0)
581+
mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,MAXVALUE)
569582

570583
\d+ mcrparted1_lt_b
571584
Table "public.mcrparted1_lt_b"
572585
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
573586
--------+---------+-----------+----------+---------+----------+--------------+-------------
574587
a | text | | | | extended | |
575588
b | integer | | | | plain | |
576-
Partition of: mcrparted FOR VALUES FROM (MINVALUE,0) TO ('b', MINVALUE)
589+
Partition of: mcrparted FOR VALUES FROM (MINVALUE,MINVALUE) TO ('b', MINVALUE)
577590
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text))
578591

579592
\d+ mcrparted2_b
@@ -636,7 +649,7 @@ Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::te
636649
--------+---------+-----------+----------+---------+----------+--------------+-------------
637650
a | text | | | | extended | |
638651
b | integer | | | | plain | |
639-
Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,0)
652+
Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE,MAXVALUE)
640653
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text))
641654

642655
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
@@ -621,14 +621,14 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
621621

622622
-- check that we get the expected partition constraints
623623
CREATETABLErange_parted4 (aint, bint, cint) PARTITION BY RANGE (abs(a), abs(b), c);
624-
CREATETABLEunbounded_range_part PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,0,0) TO (MAXVALUE,0,0);
624+
CREATETABLEunbounded_range_part PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,MINVALUE, MINVALUE) TO (MAXVALUE,MAXVALUE, MAXVALUE);
625625
\d+ unbounded_range_part
626626
DROPTABLE unbounded_range_part;
627-
CREATETABLErange_parted4_1 PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,0,0) TO (1, MAXVALUE,0);
627+
CREATETABLErange_parted4_1 PARTITION OF range_parted4 FORVALUESFROM (MINVALUE,MINVALUE, MINVALUE) TO (1, MAXVALUE,MAXVALUE);
628628
\d+ range_parted4_1
629629
CREATETABLErange_parted4_2 PARTITION OF range_parted4 FORVALUESFROM (3,4,5) TO (6,7, MAXVALUE);
630630
\d+ range_parted4_2
631-
CREATETABLErange_parted4_3 PARTITION OF range_parted4 FORVALUESFROM (6,8, MINVALUE) TO (9, MAXVALUE,0);
631+
CREATETABLErange_parted4_3 PARTITION OF range_parted4 FORVALUESFROM (6,8, MINVALUE) TO (9, MAXVALUE,MAXVALUE);
632632
\d+ range_parted4_3
633633
DROPTABLE range_parted4;
634634

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -647,12 +647,12 @@ drop table range_list_parted;
647647
-- check that constraint exclusion is able to cope with the partition
648648
-- constraint emitted for multi-column range partitioned tables
649649
createtablemcrparted (aint, bint, cint) partition by range (a, abs(b), c);
650-
createtablemcrparted0 partition of mcrparted forvaluesfrom (minvalue,0,0) to (1,1,1);
650+
createtablemcrparted0 partition of mcrparted forvaluesfrom (minvalue,minvalue, minvalue) to (1,1,1);
651651
createtablemcrparted1 partition of mcrparted forvaluesfrom (1,1,1) to (10,5,10);
652652
createtablemcrparted2 partition of mcrparted forvaluesfrom (10,5,10) to (10,10,10);
653653
createtablemcrparted3 partition of mcrparted forvaluesfrom (11,1,1) to (20,10,10);
654654
createtablemcrparted4 partition of mcrparted forvaluesfrom (20,10,10) to (20,20,20);
655-
createtablemcrparted5 partition of mcrparted forvaluesfrom (20,20,20) to (maxvalue,0,0);
655+
createtablemcrparted5 partition of mcrparted forvaluesfrom (20,20,20) to (maxvalue,maxvalue, maxvalue);
656656
explain (costs off)select*from mcrpartedwhere a=0;-- scans mcrparted0
657657
explain (costs off)select*from mcrpartedwhere a=10and abs(b)<5;-- scans mcrparted1
658658
explain (costs off)select*from mcrpartedwhere a=10and abs(b)=5;-- scans mcrparted1, mcrparted2

0 commit comments

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