On 2016/12/10 7:55, Keith Fiske wrote:
> Working on a blog post for this feature and just found some more
> inconsistencies with the doc examples. Looks like the city_id column was
> defined in the measurements table when it should be in the cities table.
> The addition of the partition to the cities table fails since it's missing.
>
> Examples should look like this:
>
> CREATE TABLE measurement (
> logdate date not null,
> peaktemp int,
> unitsales int
> ) PARTITION BY RANGE (logdate);
>
> CREATE TABLE cities (
> city_id bigserial not null,
> name text not null,
> population int
> ) PARTITION BY LIST (initcap(name));
>
> I actually changed my example to have city_id use bigserial to show that
> sequences are inherited automatically. May be good to show that in the docs.
Attached is a documentation patch fixing inconsistencies in the examples
that Keith reports and also improve them a bit (cities_west example sounds
a bit contrived now that I think).
Also, I posted a patch earlier [1] to mention the limitation that row
movement caused by UPDATE is treated an error. I have combined it into
this patch, so that all the documentation fixes proposed are together.
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/a4f261c2-8554-f443-05ff-d97dddc19689%40lab.ntt.co.jp
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a6a43c4b30..333b01db36 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -715,7 +715,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</varlistentry>
<varlistentry>
- <term><literal>ATTACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable> <replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
+ <term><literal>ATTACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
<listitem>
<para>
This form attaches an existing table (which might itself be partitioned)
@@ -1332,7 +1332,7 @@ ALTER TABLE measurement
Attach a partition to list partitioned table:
<programlisting>
ALTER TABLE cities
- ATTACH PARTITION cities_west FOR VALUES IN ('Los Angeles', 'San Francisco');
+ ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
</programlisting></para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 8bf8af302b..58f8bf6d6a 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -248,7 +248,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
- <term><literal>PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
+ <term><literal>PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable></literal> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
<listitem>
<para>
Creates the table as <firstterm>partition</firstterm> of the specified
@@ -275,7 +275,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
Rows inserted into a partitioned table will be automatically routed to
the correct partition. If no suitable partition exists, an error will
- occur.
+ occur. Also, if updating a row in a given partition causes it to move
+ to another partition due to the new partition key, an error will occur.
</para>
<para>
@@ -1477,7 +1478,6 @@ CREATE TABLE employees OF employee_type (
Create a range partitioned table:
<programlisting>
CREATE TABLE measurement (
- city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
@@ -1488,9 +1488,10 @@ CREATE TABLE measurement (
Create a list partitioned table:
<programlisting>
CREATE TABLE cities (
+ city_id bigserial not null,
name text not null,
- population int,
-) PARTITION BY LIST (initcap(name));
+ population bigint,
+) PARTITION BY LIST (left(lower(name), 1));
</programlisting></para>
<para>
@@ -1498,30 +1499,30 @@ CREATE TABLE cities (
<programlisting>
CREATE TABLE measurement_y2016m07
PARTITION OF measurement (
- unitsales WITH OPTIONS DEFAULT 0
+ unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
</programlisting></para>
<para>
Create partition of a list partitioned table:
<programlisting>
-CREATE TABLE cities_west
+CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
-) FOR VALUES IN ('Los Angeles', 'San Francisco');
+) FOR VALUES IN ('a', 'b');
</programlisting></para>
<para>
Create partition of a list partitioned table that is itself further
partitioned and then add a partition to it:
<programlisting>
-CREATE TABLE cities_west
+CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
-) FOR VALUES IN ('Los Angeles', 'San Francisco') PARTITION BY RANGE (population);
+) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);
-CREATE TABLE cities_west_10000_to_100000
- PARTITION OF cities_west FOR VALUES FROM (10000) TO (100000);
+CREATE TABLE cities_ab_10000_to_100000
+ PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
</programlisting></para>
</refsect1>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 06f416039b..00c984d8d5 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -526,6 +526,17 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
updated by the command.
</para>
</refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ If the specified table is a partitioned table, each row is routed to
+ the appropriate partition and inserted into it. If the specified table
+ is a partition, an error will occur if one of the input rows violates
+ the partition constraint.
+ </para>
+ </refsect1>
<refsect1>
<title>Examples</title>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 2de0f4aad1..e86993b9cf 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -279,6 +279,13 @@ UPDATE <replaceable class="parameter">count</replaceable>
sub-selects is safer, though often harder to read and slower than
using a join.
</para>
+
+ <para>
+ In case of partitioned tables, updating a row might cause it to move
+ to a new partition due to the new partition key. An error will occur
+ in this case. Also, if the specified table is a partition, an error
+ will occur if the new row violates the partition constraint.
+ </para>
</refsect1>
<refsect1>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
