Here are some patches to improve the documentation about partitioned tables:
0001: Adds some details about partition_bound_spec to the CREATE TABLE
page, especially:
- a note about inclusivity of range partition bounds,
- a note about the UNBOUNDED literal in case of range partitioning,
- a note about the NULL literal in case of list partitioning,
I wonder if the above "note" should be added under the Notes section or
are they fine to be added as part of the description of PARTITION OF
clause. Also:
- in syntax synopsis, it appears now that any expression is OK to be used
for individual bound datum, but it's not true. Only literals are
allowed. So fixed that.
- added an example showing how to create partitions of a range
partitioned table with multiple columns in the partition key
- added PARTITION BY and PARTITION OF (FOR VALUES) as PostgreSQL
extensions in the compatibility section
0002: Adds details about partitioned tables to the DDL chapter (ddl.sgml)
- a new section named "Partitioned Tables" right next to the
Inheritance and Partitioning sections is created.
- examples are added to the existing Partitioning section using the new
partitioned tables. Old text about implementing table partitioning
using inheritance is kept, sort of as a still supported older
alternative.
0003: Add partitioning keywords to keywords.sgml
This is all I have for now. Any feedback is greatly appreciated. Adding
this to the next CF.
Thanks,
Amit
>From 47c9edf3e46080d4bfb7d2a2908016c9039ee20f Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 26 Jan 2017 18:57:55 +0900
Subject: [PATCH 1/3] Improve CREATE TABLE documentation of partitioning
---
doc/src/sgml/ref/create_table.sgml | 103 ++++++++++++++++++++++++++++++++++---
1 file changed, 96 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 58f8bf6d6a..5596250aef 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -85,8 +85,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
-{ IN ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) |
- FROM ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">expression</replaceable> | UNBOUNDED } [, ...] ) }
+{ IN ( { <replaceable class="PARAMETER">bound_literal</replaceable> | NULL } [, ...] ) |
+ FROM ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) }
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
@@ -261,6 +261,44 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
any existing partition of that parent.
</para>
+ <note>
+ <para>
+ Each of the values specified in the partition bound specification is
+ a literal, <literal>NULL</literal>, or <literal>UNBOUNDED</literal>.
+ A literal is either a numeric constant or a string constant that can be
+ automatically coerced to the corresponding partition key column's type.
+ </para>
+
+ <para>
+ When creating a range partition, the lower bound specified with
+ <literal>FROM</literal> is an inclusive bound, whereas the upper bound
+ specified with <literal>TO</literal> is an exclusive bound. That is,
+ the values specified in the <literal>FROM</literal> list are accepted
+ values of the corresponding partition key columns in a given partition,
+ whereas those in the <literal>TO</literal> list are not. To be precise,
+ this applies only to the first of the partition key columns for which
+ the corresponding values in the <literal>FROM</literal> and
+ <literal>TO</literal> lists are not equal. All rows in a given
+ partition contain the same values for all preceding columns, equal to
+ those specified in <literal>FROM</literal> and <literal>TO</literal>
+ lists. On the other hand, any subsequent columns are insignificant
+ as far as implicit partition constraint is concerned.
+
+ Specifying <literal>UNBOUNDED</literal> in <literal>FROM</literal>
+ signifies <literal>-infinity</literal> as the lower bound of the
+ corresponding column, whereas it signifies <literal>+infinity</literal>
+ as the upper bound when specified in <literal>TO</literal>.
+ </para>
+
+ <para>
+ When creating a list partition, <literal>NULL</literal> can be specified
+ to signify that the partition allows the partition key column to be null.
+ However, there cannot be more than one such list partitions for a given
+ parent table. <literal>NULL</literal> cannot specified for range
+ partitions.
+ </para>
+ </note>
+
<para>
A partition cannot have columns other than those inherited from the
parent. That includes the <structfield>oid</> column, which can be
@@ -386,11 +424,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<firstterm>partitioned</firstterm> table. The parenthesized list of
columns or expressions forms the <firstterm>partition key</firstterm>
for the table. When using range partitioning, the partition key can
- include multiple columns or expressions, but for list partitioning, the
- partition key must consist of a single column or expression. If no
- btree operator class is specified when creating a partitioned table,
- the default btree operator class for the datatype will be used. If
- there is none, an error will be reported.
+ include multiple columns or expressions (up to 32, but this limit can
+ altered when building <productname>PostgreSQL</productname>.), but for
+ list partitioning, the partition key must consist of a single column or
+ expression. If no btree operator class is specified when creating a
+ partitioned table, the default btree operator class for the datatype will
+ be used. If there is none, an error will be reported.
</para>
<para>
@@ -1485,6 +1524,16 @@ CREATE TABLE measurement (
</programlisting></para>
<para>
+ Create a range partitioned table with multiple columns in the partition key:
+<programlisting>
+CREATE TABLE measurement_year_month (
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (EXTRACT(YEAR FROM DATE logdate), EXTRACT(MONTH FROM DATE logdate));
+</programlisting></para>
+
+ <para>
Create a list partitioned table:
<programlisting>
CREATE TABLE cities (
@@ -1504,6 +1553,27 @@ CREATE TABLE measurement_y2016m07
</programlisting></para>
<para>
+ Create a few partitions of a range partitioned table with multiple
+ columns in the partition key:
+<programlisting>
+CREATE TABLE measurement_ym_older
+ PARTITION OF measurement_year_month
+ FOR VALUES FROM (unbounded, unbounded) TO (2016, 11);
+
+CREATE TABLE measurement_ym_y2016m11
+ PARTITION OF measurement_year_month
+ FOR VALUES FROM (2016, 11) TO (2016, 12);
+
+CREATE TABLE measurement_ym_y2016m12
+ PARTITION OF measurement_year_month
+ FOR VALUES FROM (2016, 12) TO (2017, 01);
+
+CREATE TABLE measurement_ym_y2017m01
+ PARTITION OF measurement_year_month
+ FOR VALUES FROM (2017, 01) TO (2017, 02);
+</programlisting></para>
+
+ <para>
Create partition of a list partitioned table:
<programlisting>
CREATE TABLE cities_ab
@@ -1705,6 +1775,25 @@ CREATE TABLE cities_ab_10000_to_100000
effect can be had using the OID feature.
</para>
</refsect2>
+
+ <refsect2>
+ <title><literal>PARTITION BY</> Clause</title>
+
+ <para>
+ The <literal>PARTITION BY</> clause is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect2>
+
+ <refsect2>
+ <title><literal>PARTITION OF</> Clause</title>
+
+ <para>
+ The <literal>PARTITION OF</> clause is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect2>
+
</refsect1>
--
2.11.0
>From 06841c7c2bbce4e923d770ff8bb16a3bdc899a09 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 31 Jan 2017 10:57:41 +0900
Subject: [PATCH 2/3] Update ddl.sgml for declarative partitioning
Add a section titled "Partitioned Tables" to describe what are
partitioned tables, partition, their relation with inheritance,
differences, limitations, etc.
Then add examples to the partitioning chapter that show how to use
partitioned tables. In fact they implement the same partitioning
scheme that is currently shown using inheritance and highlights
some differences between the two methods.
---
doc/src/sgml/ddl.sgml | 431 +++++++++++++++++++++++++++++++++++++++++++++++---
1 file changed, 412 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index aebe898466..491ce9b32c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -12,7 +12,8 @@
Subsequently, we discuss how tables can be organized into
schemas, and how privileges can be assigned to tables. Finally,
we will briefly look at other features that affect the data storage,
- such as inheritance, views, functions, and triggers.
+ such as inheritance, table partitioning, views, functions, and
+ triggers.
</para>
<sect1 id="ddl-basics">
@@ -2771,6 +2772,132 @@ VALUES ('Albany', NULL, NULL, 'NY');
</sect2>
</sect1>
+ <sect1 id="ddl-partitioned-tables">
+ <title>Partitioned Tables</title>
+
+ <indexterm>
+ <primary>partitioned table</primary>
+ </indexterm>
+
+ <para>
+ PostgreSQL offers a way to specify how to divide a table into pieces
+ called partitions. The table that is divided is called
+ <firstterm>partitioned table</firstterm>. The specification consists
+ of the <firstterm>partitioning method</firstterm> and a list of columns
+ or expressions to be used as the <firstterm>partition key</firstterm>.
+ Any data inserted into a partitioned table must live in one of its
+ <firstterm>partitions</firstterm> based on the value of the partition
+ key. Each partition is assigned a subset of the data that is inserted
+ into the partitioned table, which is defined by its <firstterm>partition
+ bounds</firstterm>. Currently supported methods of partitioning include
+ range and list, wherein each partition is assigned a range of keys or
+ a list of keys, respectively. It is possible to implement
+ <firstterm>sub-partitioning</firstterm> by defining individual partitions
+ themselves to be partitioned table. See <xref linkend="sql-createtable">
+ for more details creating partitioned tables and partitions. It is not
+ currently possible to make a regular table into a partitioned table or
+ vice versa. However, it is possible to make a regular table containing
+ data a partition of a partitioned table and vice versa; see
+ <xref linkend="sql-altertable"> to learn more about the
+ <command>ATTACH PARTITION</> and <command>DETACH PARTITION</> commands.
+ </para>
+
+ <para>
+ Individual partitions are linked to the partitioned table with inheritance
+ behind-the-scenes, however it is not possible to use various inheritance
+ features discussed in the previous section with partitioned tables and
+ partitions. For example, partitions cannot have any other parents than
+ the partitioned table it is a partition of, nor can a regular table inherit
+ from a partitioned table making the latter its parent. That means
+ partitioned table and partitions do not participate in inheritance with
+ regular tables. Since a partition hierarchy consisting of the
+ partitioned table and its partitions is still an inheritance hierarchy,
+ all the normal rules of inheritance apply as described in the previous
+ section (<xref linkend="ddl-inherit">) with some exceptions, most notably:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Both <literal>CHECK</literal> and <literal>NOT NULL</literal>
+ constraints of a partitioned table are always inherited by all its
+ partitions. There cannot be any <literal>CHECK</literal> constraints
+ that are marked <literal>NO INHERIT</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>ONLY</literal> notation used to exclude child tables
+ would either cause error or will be ignored in some cases for
+ partitioned tables. For example, specifying <literal>ONLY</literal>
+ when querying data from a partitioned table does not make much sense,
+ because all the data is contained in partitions, so it will be
+ ignored. Specifying <literal>ONLY</literal> when modifying schema is
+ not desirable in certain cases with partitioned tables where it may be
+ fine for regular inheritance parents (for example, dropping a column
+ from only the parent); an error will be thrown in that case.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Partitions cannot have columns that are not present in the parent.
+ It is neither possible to specify own columns when creating partitions
+ with <command>CREATE TABLE</> nor is it possible to add own columns
+ using <command>ALTER TABLE</>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ One cannot drop a <literal>NOT NULL</literal> constraint on a
+ partition's column, if the constraint is present in the parent table.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Partitions can also be foreign tables (see <xref linkend="ddl-foreign-data">),
+ although certain limitations exist currently in their usage. For example,
+ data inserted into the partitioned table cannot be routed to foreign table
+ partitions.
+ </para>
+
+ <para>
+ There are currently following limitations of using partitioned tables:
+ <itemizedlist>
+ <listitem>
+ <para>
+ It is currently not possible to define indexes on partitioned tables.
+ Consequently, it is not possible to create constraints that are realized
+ using an index such as <literal>UNIQUE</>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Foreign keys referencing partitioned tables are not supported, nor
+ are foreign key references from a partitioned table to some other table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Row triggers, if necessary, must be defined on individual partitions, not
+ the partitioned table as it is currently not supported.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ A detailed example that shows how to use partitioned tables is discussed in
+ the next chapter.
+ </para>
+
+ </sect1>
+
<sect1 id="ddl-partitioning">
<title>Partitioning</title>
@@ -2821,8 +2948,8 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning design.
- <command>ALTER TABLE NO INHERIT</> and <command>DROP TABLE</> are
- both far faster than a bulk operation.
+ <command>ALTER TABLE NO INHERIT</> or <command>ALTER TABLE DETACH PARTITION</>
+ and <command>DROP TABLE</> are both far faster than a bulk operation.
These commands also entirely avoid the <command>VACUUM</command>
overhead caused by a bulk <command>DELETE</>.
</para>
@@ -2844,16 +2971,41 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
Currently, <productname>PostgreSQL</productname> supports partitioning
- via table inheritance. Each partition must be created as a child
- table of a single parent table. The parent table itself is normally
- empty; it exists just to represent the entire data set. You should be
- familiar with inheritance (see <xref linkend="ddl-inherit">) before
- attempting to set up partitioning.
+ using two methods:
+
+ <variablelist>
+ <varlistentry>
+ <term>Using Table Inheritance</term>
+
+ <listitem>
+ <para>
+ Each partition must be created as a child table of a single parent
+ table. The parent table itself is normally empty; it exists just to
+ represent the entire data set. You should be familiar with
+ inheritance (see <xref linkend="ddl-inherit">) before attempting to
+ set up partitioning with it. This was the only method to implement
+ partitioning in older versions.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>Using Partitioned Tables</term>
+
+ <listitem>
+ <para>
+ See last section for some general information:
+ <xref linkend="ddl-partitioned-tables">
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</para>
<para>
The following forms of partitioning can be implemented in
- <productname>PostgreSQL</productname>:
+ <productname>PostgreSQL</productname> using either of the above mentioned
+ methods, although the latter provides dedicated syntax for each:
<variablelist>
<varlistentry>
@@ -2888,7 +3040,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
<title>Implementing Partitioning</title>
<para>
- To set up a partitioned table, do the following:
+ To set up a partitioned table using inheritance, do the following:
<orderedlist spacing="compact">
<listitem>
<para>
@@ -2979,6 +3131,88 @@ CHECK ( outletID BETWEEN 200 AND 300 )
</para>
<para>
+ To use partitioned tables, do the following:
+ <orderedlist spacing="compact">
+ <listitem>
+ <para>
+ Create <quote>master</quote> table as a partitioned table by
+ specifying the <literal>PARTITION BY</literal> clause, which includes
+ the partitioning method (<literal>RANGE</literal> or
+ <literal>LIST</literal>) and the list of column(s) to use as the
+ partition key. To be able to insert data into the table, one must
+ create partitions, as described below.
+ </para>
+
+ <note>
+ <para>
+ To decide when to use multiple columns in the partition key for range
+ partitioning, consider whether queries accessing the partitioned
+ in question will include conditions that involve multiple columns,
+ especially the columns being considered to be the partition key.
+ If so, the optimizer can create a plan that will scan fewer partitions
+ if a query's conditions are such that there is equality constraint on
+ leading partition key columns, because they limit the number of
+ partitions of interest. The first partition key column with
+ inequality constraint also further eliminates some partitions of
+ those chosen by equality constraints on earlier columns.
+ </para>
+ </note>
+ </listitem>
+
+ <listitem>
+ <para>
+ Create partitions of the master partitioned table, with the partition
+ bounds specified for each partition matching the partitioning method
+ and partition key of the master table. Note that specifying partition
+ bounds such that the new partition's values will overlap with one or
+ more existing partitions will cause an error. It is only after
+ creating partitions that one is able to insert data into the master
+ partitioned table, provided it maps to one of the existing partitions.
+ If a data row does not map to any of the existing partitions, it will
+ cause an error.
+ </para>
+
+ <para>
+ Partitions thus created are also in every way normal
+ <productname>PostgreSQL</> tables (or, possibly, foreign tables),
+ whereas partitioned tables differ in a number of ways.
+ </para>
+
+ <para>
+ It is not necessary to create table constraints for partitions.
+ Instead, partition constraints are generated implicitly whenever
+ there is a need to refer to them. Also, since any data inserted into
+ the master partitioned table is automatically inserted into the
+ appropriate partition, it is not necessary to create triggers for the
+ same.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Just like with inheritance, create an index on the key column(s),
+ as well as any other indexes you might want for every partition.
+ Note that it is currently not supported to propagate index definition
+ from the master partitioned table to its partitions; in fact, it is
+ not possible to define indexes on partitioned tables in the first
+ place. This might change in future releases.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Currently, partitioned tables also depend on constraint exclusion
+ for query optimization, so ensure that the
+ <xref linkend="guc-constraint-exclusion"> configuration parameter is
+ not disabled in <filename>postgresql.conf</>. This might change in
+ future releases.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ <para>
For example, suppose we are constructing a database for a large
ice cream company. The company measures peak temperatures every
day as well as ice cream sales in each region. Conceptually,
@@ -3004,7 +3238,8 @@ CREATE TABLE measurement (
<para>
In this situation we can use partitioning to help us meet all of our
different requirements for the measurements table. Following the
- steps outlined above, partitioning can be set up as follows:
+ steps outlined above for both methods, partitioning can be set up as
+ follows:
</para>
<para>
@@ -3172,10 +3407,85 @@ LANGUAGE plpgsql;
</para>
<para>
+ Steps when using a partitioned table are as follows:
+ </para>
+
+ <para>
+ <orderedlist spacing="compact">
+ <listitem>
+ <para>
+ Create the <structname>measurement</> table as a partitioned table:
+
+<programlisting>
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+</programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Then create partitions as follows:
+
+<programlisting>
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
+...
+CREATE TABLE measurement_y2007m11 PARTITION OF measurement
+ FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
+CREATE TABLE measurement_y2007m12 PARTITION OF measurement
+ FOR VALUES FROM ('2007-12-01') TO ('2008-01-01');
+CREATE TABLE measurement_y2008m01 PARTITION OF measurement
+ FOR VALUES FROM ('2008-01-01') TO ('2008-02-01');
+</programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Create indexes on the key columns just like in case of inheritance
+ partitions.
+ </para>
+ </listitem>
+ </orderedlist>
+
+ <note>
+ <para>
+ To implement sub-partitioning, specify the
+ <literal>PARTITION BY</literal> clause in the commands used to create
+ individual partitions, for example:
+
+<programlisting>
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
+ PARTITION BY RANGE (peaktemp);
+</programlisting>
+
+ After creating partitions of <structname>measurement_y2006m02</>, any
+ data inserted into <structname>measurement</> that is mapped to
+ <structname>measurement_y2006m02</> will be further redirected to one
+ of its partitions based on the <structfield>peaktemp</> column.
+ Partition key specified may overlap with the parent's partition key,
+ although care must be taken when specifying the bounds of sub-partitions
+ such that the accepted set of data constitutes a subset of what a
+ partition's own bounds allows; the system does not try to check if
+ that's really the case.
+ </para>
+ </note>
+ </para>
+
+ <para>
As we can see, a complex partitioning scheme could require a
- substantial amount of DDL. In the above example we would be
- creating a new partition each month, so it might be wise to write a
- script that generates the required DDL automatically.
+ substantial amount of DDL, although significantly less when using
+ partitioned tables. In the above example we would be creating a new
+ partition each month, so it might be wise to write a script that
+ generates the required DDL automatically.
</para>
</sect2>
@@ -3195,8 +3505,15 @@ LANGUAGE plpgsql;
</para>
<para>
+ Both the inheritance-based and partitioned table methods allow this to
+ be done, although the latter requires taking an <literal>ACCESS EXCLUSIVE</literal>
+ lock on the master table for various commands mentioned below.
+ </para>
+
+ <para>
The simplest option for removing old data is simply to drop the partition
- that is no longer necessary:
+ that is no longer necessary, which works using both methods of
+ partitioning:
<programlisting>
DROP TABLE measurement_y2006m02;
</programlisting>
@@ -3211,6 +3528,13 @@ DROP TABLE measurement_y2006m02;
<programlisting>
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
</programlisting>
+
+ When using a partitioned table:
+
+<programlisting>
+ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
+</programlisting>
+
This allows further operations to be performed on the data before
it is dropped. For example, this is often a useful time to back up
the data using <command>COPY</>, <application>pg_dump</>, or
@@ -3230,6 +3554,13 @@ CREATE TABLE measurement_y2008m02 (
) INHERITS (measurement);
</programlisting>
+ When using a partitioned table:
+
+<programlisting>
+CREATE TABLE measurement_y2008m02 PARTITION OF measurement
+ FOR VALUES FROM ('2008-02-01') TO ('2008-03-01');
+</programlisting>
+
As an alternative, it is sometimes more convenient to create the
new table outside the partition structure, and make it a proper
partition later. This allows the data to be loaded, checked, and
@@ -3244,7 +3575,28 @@ ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;
</programlisting>
+
+ The last of the above commands when using a partitioned table would be:
+
+<programlisting>
+ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
+ FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
+</programlisting>
</para>
+
+ <tip>
+ <para>
+ Before running the <command>ATTACH PARTITION</> command, it is
+ recommended to create a <literal>CHECK</> constraint on the table to
+ be attached describing the desired partition constraint. Using the
+ same, system is able to skip the scan to validate the implicit
+ partition constraint. Without such a constraint, the table will be
+ scanned to validate the partition constraint, while holding an
+ <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
+ One may want to drop the constraint after <command>ATTACH PARTITION</>
+ is finished, because it is no longer necessary.
+ </para>
+ </tip>
</sect2>
<sect2 id="ddl-partitioning-constraint-exclusion">
@@ -3340,6 +3692,15 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
are unlikely to benefit.
</para>
+ <note>
+ <para>
+ Currently, constraint exclusion is also used for partitioned tables.
+ However, we did not create any <literal>CHECK</literal> constraints
+ for individual partitions as seen above. In this case, the optimizer
+ uses internally generated constraint for every partition.
+ </para>
+ </note>
+
</sect2>
<sect2 id="ddl-partitioning-alternatives">
@@ -3348,7 +3709,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<para>
A different approach to redirecting inserts into the appropriate
partition table is to set up rules, instead of a trigger, on the
- master table. For example:
+ master table (unless it is a partitioned table). For example:
<programlisting>
CREATE RULE measurement_insert_y2006m02 AS
@@ -3408,7 +3769,8 @@ UNION ALL SELECT * FROM measurement_y2008m01;
<title>Caveats</title>
<para>
- The following caveats apply to partitioned tables:
+ The following caveats apply to partitioned tables implemented using either
+ method (unless noted otherwise):
<itemizedlist>
<listitem>
<para>
@@ -3418,6 +3780,13 @@ UNION ALL SELECT * FROM measurement_y2008m01;
partitions and creates and/or modifies associated objects than
to write each by hand.
</para>
+
+ <para>
+ This is not a problem with partitioned tables though, as trying to
+ create a partition that overlaps with one of the existing partitions
+ results in an error, so it is impossible to end up with partitions
+ that overlap one another.
+ </para>
</listitem>
<listitem>
@@ -3430,6 +3799,14 @@ UNION ALL SELECT * FROM measurement_y2008m01;
on the partition tables, but it makes management of the structure
much more complicated.
</para>
+
+ <para>
+ This problem exists even for partitioned tables. An <command>UPDATE</>
+ that causes a row to move from one partition to another fails, because
+ the new value of the row fails to satisfy the implicit partition
+ constraint of the original partition. This might change in future
+ releases.
+ </para>
</listitem>
<listitem>
@@ -3440,7 +3817,8 @@ UNION ALL SELECT * FROM measurement_y2008m01;
<programlisting>
ANALYZE measurement;
</programlisting>
- will only process the master table.
+ will only process the master table. This is true even for partitioned
+ tables.
</para>
</listitem>
@@ -3451,6 +3829,12 @@ ANALYZE measurement;
action is only taken in case of unique violations on the specified
target relation, not its child relations.
</para>
+
+ <para>
+ <command>INSERT</command> statements with <literal>ON CONFLICT</>
+ clause are currently not allowed on partitioned tables, that is,
+ cause error when specified.
+ </para>
</listitem>
</itemizedlist>
@@ -3479,7 +3863,9 @@ ANALYZE measurement;
range tests for range partitioning, as illustrated in the preceding
examples. A good rule of thumb is that partitioning constraints should
contain only comparisons of the partitioning column(s) to constants
- using B-tree-indexable operators.
+ using B-tree-indexable operators, which applies even to partitioned
+ tables, because only B-tree-indexable column(s) are allowed in the
+ partition key.
</para>
</listitem>
@@ -3491,6 +3877,13 @@ ANALYZE measurement;
these techniques will work well with up to perhaps a hundred partitions;
don't try to use many thousands of partitions.
</para>
+
+ <para>
+ This restriction on the number of partitions currently applies even to
+ the partitioned tables, but it will be alleviated in future releases so
+ that the query planning time is not influenced much by the number of
+ partitions.
+ </para>
</listitem>
</itemizedlist>
--
2.11.0
>From 684393c6c3e2b44f1e8fc4170f62508303a979c3 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 2 Feb 2017 14:01:02 +0900
Subject: [PATCH 3/3] Add partitioning keywords to keywords.sgml
---
doc/src/sgml/keywords.sgml | 21 +++++++++++++++++++++
1 file changed, 21 insertions(+)
diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml
index 0a8027e3a9..4407fb928b 100644
--- a/doc/src/sgml/keywords.sgml
+++ b/doc/src/sgml/keywords.sgml
@@ -336,6 +336,13 @@
<entry></entry>
</row>
<row>
+ <entry><token>ATTACH</token></entry>
+ <entry>non-reserved</entry>
+ <entry></entry>
+ <entry></entry>
+ <entry></entry>
+ </row>
+ <row>
<entry><token>ATTRIBUTE</token></entry>
<entry>non-reserved</entry>
<entry>non-reserved</entry>
@@ -1365,6 +1372,13 @@
<entry>reserved</entry>
</row>
<row>
+ <entry><token>DETACH</token></entry>
+ <entry>non-reserved</entry>
+ <entry></entry>
+ <entry></entry>
+ <entry></entry>
+ </row>
+ <row>
<entry><token>DETERMINISTIC</token></entry>
<entry></entry>
<entry>reserved</entry>
@@ -2555,6 +2569,13 @@
<entry></entry>
</row>
<row>
+ <entry><token>LIST</token></entry>
+ <entry>non-reserved</entry>
+ <entry></entry>
+ <entry></entry>
+ <entry></entry>
+ </row>
+ <row>
<entry><token>LISTEN</token></entry>
<entry>non-reserved</entry>
<entry></entry>
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
