Hi Corey,
On 2017/02/09 6:14, Corey Huinker wrote:
> On Fri, Feb 3, 2017 at 4:15 AM, Amit Langote <langote_amit...@lab.ntt.co.jp>
> wrote:
>
>> Here are some patches to improve the documentation about partitioned
>> tables:
>
> Patch applies.
>
> Overall this looks really good. It goes a long way towards stating some of
> the things I had to learn through experimentation.
Thanks a lot for taking a look at it.
> I had to read a really long way into the patch before finding a blurb that
> I felt wasn't completely clear:
>
> +
> + <para>
> + <command>INSERT</command> statements with <literal>ON CONFLICT</>
> + clause are currently not allowed on partitioned tables, that is,
> + cause error when specified.
> + </para>
>
>
> Here's some other tries at saying the same thing, none of which are
> completely satisfying:
>
> ...ON CONFLICT clause are currently not allowed on partitioned tables and
> will cause an error?
> ...ON CONFLICT clause are currently not allowed on partitioned tables and
> will instead cause an error?
> ...ON CONFLICT clause will currently cause an error if used on a
> partitioned table?
The last one sounds better.
> As far as additional issues to cover, this bit:
>
> + <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>
>
> Maybe we should add something about how one would go about dropping a NOT
> NULL constraint (parent first then partitions?)
Dropping it on the parent will cause it to be dropped on the partitions as
well. About your point whether we should add a note about how to go about
dropping it in the partition, it seems to me it would be out of place
here; it's just saying that dropping NOT NULL constraint has a different
behavior with partitioned tables than regular inheritance. That note most
likely belongs in the ALTER TABLE reference page in the DROP NOT NULL
description, so created a patch for that (patch 0004 of the attached patches).
> In reviewing this patch, do all our target formats make word spacing
> irrelevant? i.e. is there any point in looking at the number of spaces
> after a period, etc?
It seems to be a convention in the sources to include 2 spaces after a
period, which I just try to follow (both in the code comments and SGML).
I don't see that spaces are relevant as far as how the targets such as
HTML are rendered.
> A final note, because I'm really familiar with partitioning on Postgres and
> other databases, documentation which is clear to me might not be to someone
> less familiar with partitioning. Maybe we want another reviewer for that?
More eyeballs will only help make this better.
Thanks,
Amit
>From 3074d1986afe0f3ce4710f38517f2e1929ff4c48 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 26 Jan 2017 18:57:55 +0900
Subject: [PATCH 1/4] 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 21370b0bb06b6ab865c6791363f4a769ab0378ac Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 31 Jan 2017 10:57:41 +0900
Subject: [PATCH 2/4] 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 | 430 +++++++++++++++++++++++++++++++++++++++++++++++---
1 file changed, 411 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index aebe898466..f5c01928a4 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>
+ <productname>PostgreSQL</productname> 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 key.
+ <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
+ 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,11 @@ ANALYZE measurement;
action is only taken in case of unique violations on the specified
target relation, not its child relations.
</para>
+
+ <para>
+ <literal>ON CONFLICT</literal> clause will currently cause an error if
+ used on a partitioned table.
+ </para>
</listitem>
</itemizedlist>
@@ -3479,7 +3862,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 +3876,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 d031422edbd07c59ffd00aed30d933af0c5c3576 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 2 Feb 2017 14:01:02 +0900
Subject: [PATCH 3/4] 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
>From 3cdc14f40120a45d1950329df4e8226fe8c26810 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Fri, 10 Feb 2017 15:03:45 +0900
Subject: [PATCH 4/4] Add a note about DROP NOT NULL and partitions
On the ALTER TABLE refpage, it seems better to mention how to drop
drop the not null constraint of a partition's column. Per suggestion
from Corey Huinker.
---
doc/src/sgml/ref/alter_table.sgml | 8 ++++++--
1 file changed, 6 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index da431f8369..be857882bb 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -172,9 +172,13 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</para>
<para>
- If this table is a partition, one cannot perform <literal>DROP NOT NULL</>
+ If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal>
on a column if it is marked <literal>NOT NULL</literal> in the parent
- table.
+ table. To drop the <literal>NOT NULL</literal> constraint from all the
+ partitions, perform <literal>DROP NOT NULL</literal> on the parent
+ table. One might however want to set it for only some partitions,
+ which is possible by doing <literal>SET NOT NULL</literal> on individual
+ partitions.
</para>
</listitem>
</varlistentry>
--
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
