From d363d42bb9a4399a0207bd3b371c966e22e06bd3 Mon Sep 17 00:00:00 2001
From: Dean Rasheed
Date: Fri, 21 Jul 2017 09:20:47 +0100
Subject: Use MINVALUE/MAXVALUE instead of UNBOUNDED for range partition
bounds.
Previously, UNBOUNDED meant no lower bound when used in the FROM list,
and no upper bound when used in the TO list, which was OK for
single-column range partitioning, but problematic with multiple
columns. For example, an upper bound of (10.0, UNBOUNDED) would not be
collocated with a lower bound of (10.0, UNBOUNDED), thus making it
difficult or impossible to define contiguous multi-column range
partitions in some cases.
Fix this by using MINVALUE and MAXVALUE instead of UNBOUNDED to
represent a partition column that is unbounded below or above
respectively. This syntax removes any ambiguity, and ensures that if
one partition's lower bound equals another partition's upper bound,
then the partitions are contiguous.
Also drop the constraint prohibiting finite values after an unbounded
column, and just document the fact that any values after MINVALUE or
MAXVALUE are ignored. Previously it was necessary to repeat UNBOUNDED
multiple times, which was needlessly verbose.
Note: Forces a post-PG 10 beta2 initdb.
Report by Amul Sul, original patch by Amit Langote with some
additional hacking by me.
Discussion: https://postgr.es/m/CAAJ_b947mowpLdxL3jo3YLKngRjrq9+Ej4ymduQTfYR+8=YAYQ@mail.gmail.com
---
doc/src/sgml/ref/create_table.sgml | 61 +++++++++++++++++++++++++++++---------
1 file changed, 47 insertions(+), 14 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index b15c19d3d0..e9c2c49533 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -87,8 +87,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
and partition_bound_spec is:
IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
-FROM ( { numeric_literal | string_literal | UNBOUNDED } [, ...] )
- TO ( { numeric_literal | string_literal | UNBOUNDED } [, ...] )
+FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
+ TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
@@ -269,10 +269,10 @@ FROM ( { numeric_literal |
Each of the values specified in
the partition_bound_spec is
- a literal, NULL, or UNBOUNDED.
- Each literal value must be either a numeric constant that is coercible
- to the corresponding partition key column's type, or a string literal
- that is valid input for that type.
+ a literal, NULL, MINVALUE, or
+ MAXVALUE. Each literal value must be either a
+ numeric constant that is coercible to the corresponding partition key
+ column's type, or a string literal that is valid input for that type.
@@ -300,13 +300,46 @@ FROM ( { numeric_literal |
- Writing UNBOUNDED in FROM
- signifies -infinity as the lower bound of the
- corresponding column, whereas when written in TO,
- it signifies +infinity as the upper bound.
- All items following an UNBOUNDED item within
- a FROM or TO list must also
- be UNBOUNDED.
+ The special values MINVALUE and MAXVALUE
+ may be used when creating a range partition to indicate that there
+ is no lower or upper bound on the column's value. For example, a
+ partition defined using FROM (MINVALUE) TO (10) allows
+ any values less than 10, and a partition defined using
+ FROM (10) TO (MAXVALUE) allows any values greater than
+ or equal to 10.
+
+
+
+ When creating a range partition involving more than one column, it
+ can also make sense to use MAXVALUE as part of the lower
+ bound, and MINVALUE as part of the upper bound. For
+ example, a partition defined using
+ FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows
+ where the first partition key column is greater than 0 and less than
+ or equal to 10. Similarly, a partition defined using
+ FROM ('a', MINVALUE) TO ('b', MINVALUE) allows any rows
+ where the first partition key column starts with "a".
+
+
+
+ Note that any values after MINVALUE or
+ MAXVALUE in a partition bound are ignored; so the bound
+ (10, MINVALUE, 0) is equivalent to
+ (10, MINVALUE, 10) and (10, MINVALUE, MINVALUE)
+ and (10, MINVALUE, MAXVALUE).
+
+
+
+ Also note that some element types, such as timestamp,
+ have a notion of "infinity", which is just another value that can
+ be stored. This is different from MINVALUE and
+ MAXVALUE, which are not real values that can be stored,
+ but rather they are ways of saying that the value is unbounded.
+ MAXVALUE can be thought of as being greater than any
+ other value, including "infinity" and MINVALUE as being
+ less than any other value, including "minus infinity". Thus the range
+ FROM ('infinity') TO (MAXVALUE) is not an empty range; it
+ allows precisely one value to be stored — "infinity".
@@ -1610,7 +1643,7 @@ CREATE TABLE measurement_y2016m07
CREATE TABLE measurement_ym_older
PARTITION OF measurement_year_month
- FOR VALUES FROM (unbounded, unbounded) TO (2016, 11);
+ FOR VALUES FROM (MINVALUE, 0) TO (2016, 11);
CREATE TABLE measurement_ym_y2016m11
PARTITION OF measurement_year_month
--
cgit v1.2.3