From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-hackers@postgresql.org
Subject: Re: Declarative partitioning
Date: Fri, 30 Oct 2015 19:08:46 +0900
Message-ID: <563341AE.5010207@lab.ntt.co.jp> (raw)
In-Reply-To: <20150826140158.3678.53116.pgcf@coridan.postgresql.org>
References: <55D3093C.5010800@lab.ntt.co.jp>
<55D3093C.5010800@lab.ntt.co.jp>
<20150826140158.3678.53116.pgcf@coridan.postgresql.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2015/08/26 23:01, Simon Riggs wrote:
> The following review has been posted through the commitfest application:
>
...
>
> Needs planner work and tests of that. ALTER TABLE etc can wait.
>
> The new status of this patch is: Waiting on Author
Alright, after spending much time on this I came up with the attached. I'm
sincerely sorry this took so long.
I'll describe below what's in those patches.
The DDL and catalogs part are not much different from what I had last
described though I took a few steps to simplify things. I dropped the
multi-level partitioning bit and changed range partition creation syntax a
little. Ability to specify both min and max for every range partition had
made life much difficult to construct and use the internal representation.
Anyway, attached document patch (and regression tests to some degree)
might give a picture of what user commands look like as of now, although
things are very much subject to overhaul in this general area of user
visible commands and features.
As pointed out by Simon, there is a need to invent a better internal
representation of partitioning constraints/metadata to address the
planning considerations more effectively and scalably. To that end, I have
tried to implement a number of things - There is a proposed
set_partitioned_rel_size() that calls planner's partitioning module
(proposed) which takes RelOptInfo of a partitioned table and returns a
list of OIDs of partitions that are selected to be scanned based on the
baserestrictinfo (other partitions are pruned!) The partitioned rel size
is then computed using size estimates for selected partitions. Later,
proposed set_partitioned_rel_pathlist() will create a Append/MergeAppend
path with cheapest paths for individual partitions as its subpaths. It
seems important to point out that the selected partitions each get
simple_rte_array and a simple_rel_array slots (note that those arrays are
required to be repalloc'd to make those slots available). The partition
reloptinfos are of kind RELOPT_PARTITION_REL which are functionally quite
similar to RELOPT_OTHER_MEMBER_REL but different in that they do not
require using any auxiliary structures like AppendRelInfo and
append_rel_list. The parent-partition mapping would still be necessary and
is achieved using a field called parent_relid of partition reloptinfo.
The proposed partitioning module (in the planner) figures out which of the
clauses to use for partition pruning. The rules of matching clauses to
partition key look similar to those used to match them to an index, at
least with respect to the key representation. Key info includes attribute
number, expression tree (for expression key columns), operator family OID,
per key column. While clauses matched to an index would be returned as it
is to make index paths with, things work differently for clauses matched
to partition key column(s). For each matched clause, the Const operand and
the operator would be added to operand list and operator list,
respectively, for the matched column. Operands are later evaluated to
datums. The datum arrays along with operator OIDs are passed along to
another (general-purpose) partitioning module (proposed). That module
returns a list of OIDs of selected partitions after comparing the clause
operand datums with the partition bounds.
The operator strategies (of passed operators) coupled with partitioning
method (list or range, currently) determine how partitions are selected.
For some partitioning methods, faster partition selection is enabled, for
example, being able to use binary search helps make selecting range
partitions reasonably fast. Note that the clauses matched for a given
column are effectively ANDed with one another. At the moment, using OR
clauses disables partition pruning. But it would be a matter of improving
the structure used to pass around the operands and operators to also
convey the logical structure of clauses. Also, even though range
partitioning allows multi-column key, only the clauses referencing the
first key are used to select partitions. Multi-column range partition
pruning implementation is saved for later. Note that partition bounds are
themselves stored as arrays of datums computed on-demand (cached in
RelationData as part of a structure called PartitionDesc which stores
other info about partitions like OIDs.)
There are not many executor changes currently except for those made to
ensure sane I/U/D behaviors (made to respective ExecI/U/D functions). We
can certainly think about implementing specialized scan node for
partitioned tables but I put it off for now.
Attached user documentation and regression test patches, though not quite
comprehensive, should help one get started.
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[application/gzip] declarative-partitioning-wip-1.patch.tar.gz (71.2K, ../563341AE.5010207@lab.ntt.co.jp/2-declarative-partitioning-wip-1.patch.tar.gz)
download
[text/x-diff] decl-partitioning-docs.patch (19.8K, ../563341AE.5010207@lab.ntt.co.jp/3-decl-partitioning-docs.patch)
download | inline diff:diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgmlindex 97ef618..ce02af9 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -214,6 +214,16 @@
</row>
<row>
+ <entry><link linkend="catalog-pg-partition"><structname>pg_partition</structname></link></entry>+ <entry>information about partitions, including partition boundary</entry>+ </row>++ <row>+ <entry><link linkend="catalog-pg-partitioned-rel"><structname>pg_partitioned_rel</structname></link></entry>+ <entry>information about partitioned tables, including the partition key</entry>+ </row>++ <row>
<entry><link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link></entry>
<entry>template data for procedural languages</entry>
</row>
@@ -4629,6 +4639,161 @@
</sect1>
+ <sect1 id="catalog-pg-partition">+ <title><structname>pg_partition</structname></title>++ <indexterm zone="catalog-pg-partition">+ <primary>pg_partition</primary>+ </indexterm>++ <para>+ The catalog <structname>pg_partition</structname> stores+ information about partitions of partitioned tables.+ </para>++ <table>+ <title><structname>pg_partition</> Columns</title>++ <tgroup cols="4">+ <thead>+ <row>+ <entry>Name</entry>+ <entry>Type</entry>+ <entry>References</entry>+ <entry>Description</entry>+ </row>+ </thead>++ <tbody>++ <row>+ <entry><structfield>partrelid</structfield></entry>+ <entry><type>oid</type></entry>+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>+ <entry>The OID of the <structname>pg_class</> entry for this partition</entry>+ </row>++ <row>+ <entry><structfield>partparent</structfield></entry>+ <entry><type>oid</type></entry>+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>+ <entry>The OID of the partitioned table this partition is part of</entry>+ </row>++ <row>+ <entry><structfield>partlistvals</structfield></entry>+ <entry><type>anyarray</type></entry>+ <entry></entry>+ <entry>For partitions of a list partitioned table, list of values assigned to this partition</entry>+ </row>++ <row>+ <entry><structfield>partrangemaxs</structfield></entry>+ <entry><type>anyarray</type></entry>+ <entry></entry>+ <entry>For partitions of a range partitioned table, array of maximum values per partition key column</entry>+ </row>++ </tbody>+ </tgroup>+ </table>++ </sect1>++ <sect1 id="catalog-pg-partitioned-rel">+ <title><structname>pg_partitioned_rel</structname></title>++ <indexterm zone="catalog-pg-partitioned-rel">+ <primary>pg_partitioned_rel</primary>+ </indexterm>++ <para>+ The catalog <structname>pg_partitioned</structname> stores+ information about partition key of partitioned tables.+ </para>++ <table>+ <title><structname>pg_partitioned_rel</> Columns</title>++ <tgroup cols="4">+ <thead>+ <row>+ <entry>Name</entry>+ <entry>Type</entry>+ <entry>References</entry>+ <entry>Description</entry>+ </row>+ </thead>++ <tbody>++ <row>+ <entry><structfield>partrelid</structfield></entry>+ <entry><type>oid</type></entry>+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>+ <entry>The OID of the <structname>pg_class</> entry for this partitioned table</entry>+ </row>++ <row>+ <entry><structfield>partstrat</structfield></entry>+ <entry><type>char</type></entry>+ <entry></entry>+ <entry>+ Partitioning strategy (method); <literal>l</> = list partitioned table,+ <literal>r</> = range partitioned table+ </entry>+ </row>++ <row>+ <entry><structfield>partnatts</structfield></entry>+ <entry><type>int2</type></entry>+ <entry></entry>+ <entry>The number of columns in partition key</entry>+ </row>++ <row>+ <entry><structfield>partkey</structfield></entry>+ <entry><type>int2vector</type></entry>+ <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>+ <entry>+ This is an array of <structfield>partnatts</structfield> values that+ correspond to table columns used as partition key. For example, a value+ of <literal>1 3</literal> would mean that the first and the third table+ columns make up the partition key. A zero in this array indicates that the+ corresponding partition key column is an expression over the table columns,+ rather than a simple column reference.+ </entry>+ </row>++ <row>+ <entry><structfield>partclass</structfield></entry>+ <entry><type>oidvector</type></entry>+ <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>+ <entry>+ For each column in the partition key, this contains the OID of+ the operator class to use. See+ <link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.+ </entry>+ </row>++ <row>+ <entry><structfield>partexprs</structfield></entry>+ <entry><type>pg_node_tree</type></entry>+ <entry></entry>+ <entry>+ Expression trees (in <function>nodeToString()</function>+ representation) for partition key columns that are not simple column+ references. This is a list with one element for each zero+ entry in <structfield>partkey</>. Null if all partition key columns+ are simple references.+ </entry>+ </row>++ </tbody>+ </tgroup>+ </table>++ </sect1>
<sect1 id="catalog-pg-pltemplate">
<title><structname>pg_pltemplate</structname></title>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex aca40f5..07b3000 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -77,12 +77,23 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
NOT OF
OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX <replaceable class="PARAMETER">index_name</replaceable> | FULL | NOTHING }
+ ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES { <replaceable class="PARAMETER">list_partition_values</replaceable> | <replaceable class="PARAMETER">range_partition_values</replaceable> } USING [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable>+ DETACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> [ USING [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> ]
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
++<phrase>and <replaceable class="PARAMETER">list_partition_values</replaceable> is:</phrase>++[ IN ] ( { <replaceable class="PARAMETER">expression</replaceable> } [, ...] )++<phrase>and <replaceable class="PARAMETER">range_partition_values</replaceable> is:</phrase>++LESS THAN ( { <replaceable class="PARAMETER">expression</replaceable> } [, ...] )+
</synopsis>
</refsynopsisdiv>
@@ -704,6 +715,32 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>+ <term><literal>ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES { <replaceable class="PARAMETER">list_partition_values</replaceable> | <replaceable class="PARAMETER">range_partition_values</replaceable> } USING [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable></literal></term>+ <listitem>+ <para>+ This form attaches a table as partition of the target table (presumably+ partitioned.) To be attached as a partition, the source table must+ already contain all the same columns as the parent. The columns must+ have matching data types, and if they have <literal>NOT NULL</literal>+ constraints in the target table then they must also have <literal>+ NOT NULL</literal> constraints in the source table. The table is renamed+ to the specified partition name. [Note: this command is currently+ unimplemented.]+ </para>+ </listitem>+ </varlistentry> ++ <varlistentry>+ <term><literal>DETACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> [ USING [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> ] </literal></term>+ <listitem>+ <para>+ This form detaches a partition from the target table (presumably+ partitioned.) The resulting table can optionally have a different+ name after being detached.+ </para>+ </listitem>+ </varlistentry>
</variablelist>
</para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgmlindex a2d0b0c..2fa7bc8 100644--- a/doc/src/sgml/ref/create_table.sgml+++ b/doc/src/sgml/ref/create_table.sgml@@ -28,6 +28,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
+[ PARTITION BY {RANGE | LIST} ON ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] )]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -42,6 +43,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>+ PARTITION OF <replaceable class="PARAMETER">table_name</replaceable>+ FOR VALUES { <replaceable class="PARAMETER">list_partition_values</replaceable> | <replaceable class="PARAMETER">range_partition_values</replaceable> }+[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]+[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]+[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]+
<phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -70,6 +78,14 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
+<phrase>and <replaceable class="PARAMETER">list_partition_values</replaceable> is:</phrase>++[ IN ] ( { <replaceable class="PARAMETER">expression</replaceable> } [, ...] )++<phrase>and <replaceable class="PARAMETER">range_partition_values</replaceable> is:</phrase>++LESS THAN ( { <replaceable class="PARAMETER">expression</replaceable> } [, ...] )+
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
@@ -230,6 +246,40 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
+ <term><literal>PARTITION OF <replaceable class="PARAMETER">table_name</replaceable> FOR VALUES { <replaceable class="PARAMETER">list_partition_values</replaceable> | <replaceable class="PARAMETER">range_partition_values</replaceable> }</literal></term>+ <listitem>+ <para>+ Creates the table as a <firstterm>partition</firstterm> of the+ specified partitioned table.+ </para>++ <para>+ When a table is created as partition, the data types of the+ columns, constraints are determined by the underlying partitioned+ table and are not specified by the <literal>CREATE TABLE</literal>+ command nor can they be added or altered after the fact using+ ALTER TABLE. But the <literal>CREATE TABLE</literal> command+ command can specify table storage parameters and tablespace for the+ partition (and can be altered after the fact). The <literal>+ FOR VALUES</literal> specification describes partition bounds,+ that is, either a list of values or of maximum values of partition+ key columns for list partitions and range partitions, respectively.+ </para>++ <para>+ Care must be taken to specify correct values for the bounds.+ Specifying bounds such that new partition overlaps with some existing+ partition results in error. Rules for whether such overlaps occurs+ are different based on the partitioning method used. For list+ bounds, make sure that the new partition's list of values does+ not contain any values already present in some existing partition's+ list. For range bounds, make sure that the new partition's rangemax+ value is greater than the last existing partition's rangemax.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>
<term><replaceable class="PARAMETER">column_name</replaceable></term>
<listitem>
<para>
@@ -314,6 +364,32 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
+ <term><literal>PARTITION BY {RANGE | LIST} ON ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] )</literal></term>+ <listitem>+ <para>+ The optional <literal>PARTITION BY</> clause specifies a list+ of columns (or expressions) to use as the partition key of table.+ Currently, <literal>LIST</> and <literal>RANGE</> can be specified+ as the partitioning method. Such a table (hereafter referred to as+ partitioned table) is divided into sub-tables called partitions which+ are created after the fact.+ </para>++ <para>+ Scanning a partitioned table results in scanning its partitions+ and appending the results. Rows inserted into a partitioned table+ are mapped to and stored in a partition based on values of partition+ key columns unless an appropriate partition has not been defined yet.+ </para>++ <para>+ There are currently a number of limitations on constraints that+ can be defined on partitioned tables as detailed elsewhere.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>
<term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
<listitem>
<para>
@@ -498,6 +574,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
primary key constraint defined for the table. (Otherwise it
would just be the same constraint listed twice.)
</para>
++ <para>+ UNIQUE constraints are not supported on partitioned tables at the moment.+ </para>
</listitem>
</varlistentry>
@@ -526,6 +606,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
different from other sets of columns named by any unique
constraint defined for the same table.
</para>
++ <para>+ Primary key constraints are not supported on partitioned tables at the moment.+ </para>
</listitem>
</varlistentry>
@@ -576,6 +660,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
exclusion constraint on a subset of the table; internally this creates a
partial index. Note that parentheses are required around the predicate.
</para>
++ <para>+ Exclusion constraints are not supported on partitioned tables at the moment. + </para>
</listitem>
</varlistentry>
@@ -699,6 +787,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
associated with the foreign key constraint can be performed more
efficiently.
</para>
++ <para>+ Partitioned tables cannot use the REFERENCES clause at the moment.+ </para>
</listitem>
</varlistentry>
@@ -1119,7 +1211,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<para>
Unique constraints and primary keys are not inherited in the
current implementation. This makes the combination of
- inheritance and unique constraints rather dysfunctional.+ inheritance and unique constraints rather dysfunctional. As+ noted before, they cannot be used with partitioned tables+ either.
</para>
<para>
@@ -1341,6 +1435,76 @@ CREATE TABLE employees OF employee_type (
salary WITH OPTIONS DEFAULT 1000
);
</programlisting></para>
++ <para>+ Create a range partitioned table and partitions:+<programlisting>+CREATE TABLE employee (+ name text,+ age int NOT NULL+)+PARTITION BY RANGE ON (age);++CREATE TABLE employees_less_than_30+ PARTITION OF employee+ FOR VALUES LESS THAN (30)+ WITH (fillfactor=70)+ TABLESPACE diskvol1;++CREATE TABLE employees_between_30_and_40+ PARTITION OF employee+ FOR VALUES LESS THAN (40)+ WITH (fillfactor=70)+ TABLESPACE diskvol2;+</programlisting></para>++ <para>+ Create a list partitioned table and partitions:+<programlisting>+CREATE TABLE store (+ id int,+ city text NOT NULL+)+PARTITION BY LIST ON (city);++CREATE TABLE store_east+ PARTITION OF store+ FOR VALUES IN ('New York', 'Boston');++CREATE TABLE store_west+ PARTITION OF store+ FOR VALUES ('San Fransisco', 'Seattle');+</programlisting></para>++ <para>+ Create a multi-column range partitioned table and partitions:+<programlisting>+CREATE TABLE sales (+ tos timestamp NOT NULL,+ item text+)+PARTITION BY RANGE ON (extract(year from tos), extract(month from tos));++CREATE TABLE sales_before_2015+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 1);++CREATE TABLE sales_2015_q1+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 4);++CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 7);++CREATE TABLE sales_2015_q3+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 10);++CREATE TABLE sales_2015_q4+ PARTITION OF sales+ FOR VALUES LESS THAN (2016, 1);+</programlisting></para>
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
@@ -1511,6 +1675,16 @@ CREATE TABLE employees OF employee_type (
effect can be had using the OID feature.
</para>
</refsect2>
++ <refsect2>+ <title>Partitioned Tables</title>++ <para>+ <literal>PARTITION BY</literal> and <literal>PARTITION OF</literal> (including+ <literal>FOR VALUES</literal>) are <productname>PostgreSQL</productname>+ extensions.+ </para>+ </refsect2>
</refsect1>
[text/x-diff] decl-partitioning-tests.patch (55.4K, ../563341AE.5010207@lab.ntt.co.jp/4-decl-partitioning-tests.patch)
download | inline diff:diff --git a/src/test/regress/expected/partitioned_table.out b/src/test/regress/expected/partitioned_table.outnew file mode 100644index 0000000..da2e784--- /dev/null+++ b/src/test/regress/expected/partitioned_table.out@@ -0,0 +1,1044 @@+--+-- create a range partitioned table+--+CREATE TABLE person_partitioned (+ name text,+ age int,+ city text+) PARTITION BY RANGE ON (age);+-- select (empty result yet)+EXPLAIN (costs off) SELECT * FROM person_partitioned;+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++SELECT * FROM person_partitioned;+ name | age | city +------+-----+------+(0 rows)++-- cannot insert data into it without creating a partition+INSERT INTO person_partitioned VALUES ('Bob', 23, 'Boston'); -- fail+ERROR: cannot find partition for the new row for relation "person_partitioned"+DETAIL: Failing row contains (Bob, 23, Boston).+-- create a couple of range partitions+CREATE TABLE person_partitioned_30+ PARTITION OF person_partitioned+ FOR VALUES LESS THAN (30);+CREATE TABLE person_partitioned_50+ PARTITION OF person_partitioned+ FOR VALUES LESS THAN (50);+INSERT INTO person_partitioned VALUES ('Bob', 23, 'Boston'); -- ok+INSERT INTO person_partitioned VALUES ('Alice', 32, 'Austin'); -- ok+INSERT INTO person_partitioned VALUES ('Jon', 52, 'Seattle'); -- fail+ERROR: cannot find partition for the new row for relation "person_partitioned"+DETAIL: Failing row contains (Jon, 52, Seattle).+-- select (non-empty result)+EXPLAIN (costs off) SELECT * FROM person_partitioned;+ QUERY PLAN +-----------------------------------------+ Append+ -> Seq Scan on person_partitioned_30+ -> Seq Scan on person_partitioned_50+(3 rows)++SELECT * FROM person_partitioned;+ name | age | city +-------+-----+--------+ Bob | 23 | Boston+ Alice | 32 | Austin+(2 rows)++-- no partition selected+EXPLAIN (costs off) SELECT * FROM person_partitioned WHERE age > 50;+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++SELECT * FROM person_partitioned WHERE age > 50;+ name | age | city +------+-----+------+(0 rows)++-- one partition selected+EXPLAIN (costs off) SELECT * FROM person_partitioned WHERE age < 25;+ QUERY PLAN +-----------------------------------------+ Append+ -> Seq Scan on person_partitioned_30+ Filter: (age < 25)+(3 rows)++SELECT * FROM person_partitioned WHERE age < 25;+ name | age | city +------+-----+--------+ Bob | 23 | Boston+(1 row)++-- cannot update partition directly+UPDATE person_partitioned_50 SET city = 'Dallas' WHERE name = 'Alice';+ERROR: cannot update a partition of another table+HINT: Perform UPDATE on the parent instead.+-- so update parent+EXPLAIN (costs off) UPDATE person_partitioned SET city = 'Dallas' WHERE name = 'Alice';+ QUERY PLAN +-----------------------------------------------------+ Update on person_partitioned+ -> Result+ -> Append+ -> Seq Scan on person_partitioned_30+ Filter: (name = 'Alice'::text)+ -> Seq Scan on person_partitioned_50+ Filter: (name = 'Alice'::text)+(7 rows)++UPDATE person_partitioned SET city = 'Dallas' WHERE name = 'Alice';+-- following update changes the value of partition key+UPDATE person_partitioned SET age = 52 WHERE name = 'Alice'; -- fail+ERROR: cannot perform update that causes a row to change partition+UPDATE person_partitioned SET age = 42 WHERE name = 'Alice'; -- ok+--+-- create a list partitioend table+--+CREATE TABLE city_partitioned (+ name text+) PARTITION BY LIST ON (name);+-- select (empty result yet)+EXPLAIN (costs off) SELECT * FROM city_partitioned;+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++SELECT * FROM city_partitioned;+ name +------+(0 rows)++-- cannot insert data into it without creating a partition+INSERT INTO city_partitioned VALUES ('Boston'); -- fail+ERROR: cannot find partition for the new row for relation "city_partitioned"+DETAIL: Failing row contains (Boston).+-- create a couple of list partitions+CREATE TABLE city_east+ PARTITION OF city_partitioned+ FOR VALUES IN ('New York', 'Boston');+CREATE TABLE city_west+ PARTITION OF city_partitioned+ FOR VALUES IN ('Los Angeles');+INSERT INTO city_partitioned VALUES ('Boston'); -- ok+INSERT INTO city_partitioned VALUES ('Austin'); -- fail+ERROR: cannot find partition for the new row for relation "city_partitioned"+DETAIL: Failing row contains (Austin).+-- select (non-empty result)+EXPLAIN (costs off) SELECT * FROM city_partitioned;+ QUERY PLAN +-----------------------------+ Append+ -> Seq Scan on city_east+ -> Seq Scan on city_west+(3 rows)++SELECT * FROM city_partitioned;+ name +--------+ Boston+(1 row)++-- no partition selected+EXPLAIN (costs off) SELECT * FROM city_partitioned WHERE name = 'Austin';+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++SELECT * FROM city_partitioned WHERE name = 'Austin';+ name +------+(0 rows)++-- one partition selected+EXPLAIN (costs off) SELECT * FROM city_partitioned WHERE name = 'Boston';+ QUERY PLAN +-----------------------------------------+ Append+ -> Seq Scan on city_east+ Filter: (name = 'Boston'::text)+(3 rows)++SELECT * FROM city_partitioned WHERE name = 'Boston';+ name +--------+ Boston+(1 row)++-- can delete from partition directly+DELETE FROM city_east WHERE name = 'Boston'; -- ok+-- or from parent+DELETE FROM city_east WHERE name = 'Boston'; -- 0 deleted+-- dropping partitioned table requires CASCADE if partitions exist+DROP TABLE person_partitioned; -- fail+ERROR: cannot drop partitioned table person_partitioned because other objects depend on it+DETAIL: partition person_partitioned_30 depends on partitioned table person_partitioned+partition person_partitioned_50 depends on partitioned table person_partitioned+HINT: Use DROP ... CASCADE to drop the dependent objects too.+DROP TABLE person_partitioned CASCADE; -- ok+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to partition person_partitioned_30+drop cascades to partition person_partitioned_50+DROP TABLE city_partitioned CASCADE;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to partition city_east+drop cascades to partition city_west+--+-- test some planner bits+--+CREATE TABLE rangetab (a int) PARTITION BY RANGE ON (a);+CREATE TABLE rangetab1 PARTITION OF rangetab FOR VALUES LESS THAN (10);+CREATE INDEX rangetab1_a_idx ON rangetab1 (a);+CREATE TABLE rangetab2 PARTITION OF rangetab FOR VALUES LESS THAN (20);+CREATE INDEX rangetab2_a_idx ON rangetab2 (a);+CREATE TABLE rangetab3 PARTITION OF rangetab FOR VALUES LESS THAN (30);+CREATE INDEX rangetab3_a_idx ON rangetab3 (a);+CREATE TABLE rangetab4 PARTITION OF rangetab FOR VALUES LESS THAN (40);+CREATE INDEX rangetab4_a_idx ON rangetab3 (a);+INSERT INTO rangetab SELECT generate_series(1, 39);+-- scan rangetab2 and rangetab3+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a BETWEEN 15 AND 25;+ QUERY PLAN +-----------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: ((a >= 15) AND (a <= 25))+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: ((a >= 15) AND (a <= 25))+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: ((a >= 15) AND (a <= 25))+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: ((a >= 15) AND (a <= 25))+(9 rows)++-- scan rangetab1 and rangetab2+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a <= 10;+ QUERY PLAN +--------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: (a <= 10)+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a <= 10)+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: (a <= 10)+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a <= 10)+(9 rows)++-- scan rangetab2, rangetab3, rangetab4+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 10;+ QUERY PLAN +--------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: (a > 10)+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a > 10)+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: (a > 10)+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a > 10)+ -> Seq Scan on rangetab4+ Filter: (a > 10)+(11 rows)++-- scan rangetab1+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a < 10 AND a < 20;+ QUERY PLAN +---------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: ((a < 10) AND (a < 20))+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: ((a < 10) AND (a < 20))+(5 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 1 AND a < 20;+ QUERY PLAN +--------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: ((a < 20) AND (a = 1))+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: ((a < 20) AND (a = 1))+(5 rows)++-- scan rangetab4+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 35 AND a > 25;+ QUERY PLAN +-----------------------------------------+ Append+ -> Seq Scan on rangetab4+ Filter: ((a > 35) AND (a > 25))+(3 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 35 AND a > 20;+ QUERY PLAN +-----------------------------------------+ Append+ -> Seq Scan on rangetab4+ Filter: ((a > 20) AND (a = 35))+(3 rows)++-- scan nothing+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 AND a < 5; -- mutually refute+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 AND a > 15; -- ditto+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 40; -- no partitions beyond 40+ QUERY PLAN +--------------------------+ Result+ One-Time Filter: false+(2 rows)++-- scan everything+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 1;+ QUERY PLAN +--------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: (a > 1)+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a > 1)+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: (a > 1)+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a > 1)+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: (a > 1)+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a > 1)+ -> Seq Scan on rangetab4+ Filter: (a > 1)+(15 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 OR a > 1; -- OR clause turns pruning off (for now)+ QUERY PLAN +--------------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: ((a = 10) OR (a > 1))+ -> BitmapOr+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a = 10)+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a > 1)+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: ((a = 10) OR (a > 1))+ -> BitmapOr+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a = 10)+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a > 1)+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: ((a = 10) OR (a > 1))+ -> BitmapOr+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a = 10)+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a > 1)+ -> Seq Scan on rangetab4+ Filter: ((a = 10) OR (a > 1))+(24 rows)++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a IN (1, 11, 21); -- array op turns pruning off (for now)+ QUERY PLAN +--------------------------------------------------------------+ Append+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a = ANY ('{1,11,21}'::integer[]))+ -> Seq Scan on rangetab4+ Filter: (a = ANY ('{1,11,21}'::integer[]))+(15 rows)++CREATE TABLE plaintab (a int, b text);+CREATE INDEX plaintab_a_idx ON plaintab (a);+INSERT INTO plaintab SELECT generate_series(1, 15), 'c';+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = (SELECT max(a) FROM plaintab); -- only use Consts to prune+ QUERY PLAN +---------------------------------------------------------------------------------+ Append+ InitPlan 2 (returns $1)+ -> Result+ InitPlan 1 (returns $0)+ -> Limit+ -> Index Only Scan Backward using plaintab_a_idx on plaintab+ Index Cond: (a IS NOT NULL)+ -> Bitmap Heap Scan on rangetab1+ Recheck Cond: (a = $1)+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a = $1)+ -> Bitmap Heap Scan on rangetab2+ Recheck Cond: (a = $1)+ -> Bitmap Index Scan on rangetab2_a_idx+ Index Cond: (a = $1)+ -> Bitmap Heap Scan on rangetab3+ Recheck Cond: (a = $1)+ -> Bitmap Index Scan on rangetab4_a_idx+ Index Cond: (a = $1)+ -> Seq Scan on rangetab4+ Filter: (a = $1)+(21 rows)++-- might as well check prepared statement+PREPARE x AS SELECT * FROM rangetab WHERE a = $1;+EXPLAIN EXECUTE x(1); -- scan rangetab+ QUERY PLAN +-------------------------------------------------------------------------------------+ Append (cost=4.26..14.95 rows=13 width=4)+ -> Bitmap Heap Scan on rangetab1 (cost=4.26..14.95 rows=13 width=4)+ Recheck Cond: (a = 1)+ -> Bitmap Index Scan on rangetab1_a_idx (cost=0.00..4.25 rows=13 width=0)+ Index Cond: (a = 1)+(5 rows)++EXPLAIN EXECUTE x(10); -- scan rangetab2+ QUERY PLAN +-------------------------------------------------------------------------------------+ Append (cost=4.26..14.95 rows=13 width=4)+ -> Bitmap Heap Scan on rangetab2 (cost=4.26..14.95 rows=13 width=4)+ Recheck Cond: (a = 10)+ -> Bitmap Index Scan on rangetab2_a_idx (cost=0.00..4.25 rows=13 width=0)+ Index Cond: (a = 10)+(5 rows)++DEALLOCATE x;+-- check equivalence processing+EXPLAIN (costs off) SELECT * FROM rangetab t1, plaintab t2 WHERE t1.a = t2.a AND t2.a = 1; -- scan only rangetab1+ QUERY PLAN +--------------------------------------------------------+ Nested Loop+ -> Append+ -> Bitmap Heap Scan on rangetab1 t1+ Recheck Cond: (a = 1)+ -> Bitmap Index Scan on rangetab1_a_idx+ Index Cond: (a = 1)+ -> Materialize+ -> Bitmap Heap Scan on plaintab t2+ Recheck Cond: (a = 1)+ -> Bitmap Index Scan on plaintab_a_idx+ Index Cond: (a = 1)+(11 rows)++DROP TABLE rangetab CASCADE;+NOTICE: drop cascades to 4 other objects+DETAIL: drop cascades to partition rangetab1+drop cascades to partition rangetab2+drop cascades to partition rangetab3+drop cascades to partition rangetab4+-- why not check for list partitioned case, too+CREATE TABLE listtab (a text) PARTITION BY LIST ON (a);+CREATE TABLE listtab1 PARTITION OF listtab FOR VALUES IN ('a', 'b');+CREATE INDEX listtab1_a_idx ON listtab1 (a);+CREATE TABLE listtab2 PARTITION OF listtab FOR VALUES IN ('c');+CREATE INDEX listtab2_a_idx ON listtab2 (a);+-- one might think only scans parttab1 - scans everything+EXPLAIN (costs off) SELECT * FROM listtab WHERE a IN ('a', 'b'); -- array op turns off pruning (for now)+ QUERY PLAN +-------------------------------------------------------+ Append+ -> Bitmap Heap Scan on listtab1+ Recheck Cond: (a = ANY ('{a,b}'::text[]))+ -> Bitmap Index Scan on listtab1_a_idx+ Index Cond: (a = ANY ('{a,b}'::text[]))+ -> Bitmap Heap Scan on listtab2+ Recheck Cond: (a = ANY ('{a,b}'::text[]))+ -> Bitmap Index Scan on listtab2_a_idx+ Index Cond: (a = ANY ('{a,b}'::text[]))+(9 rows)++EXPLAIN (costs off) SELECT * FROM listtab WHERE a = 'a' OR a = 'b'; -- OR turns off pruning (for now)+ QUERY PLAN +------------------------------------------------------------+ Append+ -> Bitmap Heap Scan on listtab1+ Recheck Cond: ((a = 'a'::text) OR (a = 'b'::text))+ -> BitmapOr+ -> Bitmap Index Scan on listtab1_a_idx+ Index Cond: (a = 'a'::text)+ -> Bitmap Index Scan on listtab1_a_idx+ Index Cond: (a = 'b'::text)+ -> Bitmap Heap Scan on listtab2+ Recheck Cond: ((a = 'a'::text) OR (a = 'b'::text))+ -> BitmapOr+ -> Bitmap Index Scan on listtab2_a_idx+ Index Cond: (a = 'a'::text)+ -> Bitmap Index Scan on listtab2_a_idx+ Index Cond: (a = 'b'::text)+(15 rows)++-- scan listtab1+EXPLAIN (costs off) SELECT * FROM listtab WHERE a = 'b';+ QUERY PLAN +-------------------------------------------------+ Append+ -> Bitmap Heap Scan on listtab1+ Recheck Cond: (a = 'b'::text)+ -> Bitmap Index Scan on listtab1_a_idx+ Index Cond: (a = 'b'::text)+(5 rows)++-- check equivalence processing+EXPLAIN (costs off) SELECT * FROM listtab t1, plaintab t2 WHERE t1.a = t2.b AND t2.b = 'c'; -- scan only listtab2+ QUERY PLAN +-------------------------------------------------------+ Nested Loop+ -> Append+ -> Bitmap Heap Scan on listtab2 t1+ Recheck Cond: (a = 'c'::text)+ -> Bitmap Index Scan on listtab2_a_idx+ Index Cond: (a = 'c'::text)+ -> Materialize+ -> Seq Scan on plaintab t2+ Filter: (b = 'c'::text)+(9 rows)++DROP TABLE plaintab;+DROP TABLE listtab CASCADE;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to partition listtab1+drop cascades to partition listtab2+--+-- ddl related tests+--+-- cannot use system columns in partition key+CREATE TABLE fail_oid_pk (+ a int+) PARTITION BY RANGE ON (oid) WITH OIDS; -- fail+ERROR: cannot use system column "oid" in partition key+-- cannot use more than 1 column as partition key for list partitioned table+CREATE TABLE two_col_tab (+ a1 int,+ a2 int+) PARTITION BY LIST ON (a1, a2); -- fail+ERROR: cannot use more than one column in partition key+DETAIL: Only one column allowed with list partitioning.+-- cannot use more than 16 columns as partition key for range partitioned table+CREATE TABLE wide_tab (+ a1 int,+ a2 int,+ a3 int,+ a4 int,+ a5 int,+ a6 int,+ a7 int,+ a8 int,+ a9 int,+ a10 int,+ a11 int,+ a12 int,+ a13 int,+ a14 int,+ a15 int,+ a16 int+) PARTITION BY RANGE ON (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16); -- ok+DROP TABLE wide_tab CASCADE;+CREATE TABLE extra_wide_tab (+ a1 int,+ a2 int,+ a3 int,+ a4 int,+ a5 int,+ a6 int,+ a7 int,+ a8 int,+ a9 int,+ a10 int,+ a11 int,+ a12 int,+ a13 int,+ a14 int,+ a15 int,+ a16 int,+ a17 int+) PARTITION BY RANGE ON (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17); -- fail+ERROR: cannot use more than 16 columns in partition key+-- At the moment, partitioning requires to use columns with data type having+-- a suitable btree operator class+CREATE TABLE point_tab (a point) PARTITION BY LIST ON (a); -- fail+ERROR: data type point has no default btree operator class+HINT: You must specify an existing btree operator class or define one for the data type.+CREATE TABLE point_tab (a point) PARTITION BY LIST ON (a USING point_ops); -- fail+ERROR: operator class "point_ops" does not exist for access method "btree"+CREATE TABLE point_tab (a point) PARTITION BY RANGE ON (a); -- fail+ERROR: data type point has no default btree operator class+HINT: You must specify an existing btree operator class or define one for the data type.+CREATE TABLE point_tab (a point) PARTITION BY RANGE ON (a USING point_ops); -- fail+ERROR: operator class "point_ops" does not exist for access method "btree"+-- fail to create a partitioned table with PRIMARY KEY constraint+CREATE TABLE pk_fail (a int PRIMARY KEY) PARTITION BY RANGE ON (a);+ERROR: primary key constraints are not supported on partitioned tables+LINE 1: CREATE TABLE pk_fail (a int PRIMARY KEY) PARTITION BY RANGE ...+ ^+-- fail to create a partitioned table with foreign key constraint+CREATE TABLE pk_rel(a int PRIMARY KEY);+CREATE TABLE pk_fail (a int REFERENCES pk_rel(a)) PARTITION BY RANGE ON (a);+ERROR: foreign key constraints are not supported on partitioned tables+LINE 1: CREATE TABLE pk_fail (a int REFERENCES pk_rel(a)) PARTITION ...+ ^+DROP TABLE pk_rel;+-- fail to let a partitioned table be referenced in a foreign key+CREATE TABLE foo (a int) PARTITION BY RANGE ON (a);+CREATE TABLE bar_ref_foo (a int REFERENCES foo(a));+ERROR: cannot reference relation "foo"+DETAIL: Referencing partitioned tables in foreign key constraints is not supported.+DROP TABLE foo CASCADE;+-- fail to create a partitioned table with prohibited expressions in key+CREATE TABLE fail_const_key (a int) PARTITION BY RANGE ON (('a'));+ERROR: cannot use a constant expression as partition key+CREATE TABLE fail_agg_in_key (a int) PARTITION BY RANGE ON ((avg(a)));+ERROR: aggregate functions are not allowed in partition key expressions+CREATE TABLE fail_window_fun_in_key (a int, b int) PARTITION BY RANGE ON ((avg(a) OVER (PARTITION BY b)));+ERROR: window functions are not allowed in partition key expressions+--+-- creating partitions with sane FOR VALUES specification+--+CREATE TABLE range_partitioned (+ a int+) PARTITION BY RANGE ON (a);+CREATE TABLE fail_partition+ PARTITION OF range_partitioned+ FOR VALUES IN (1, 2); -- fail+ERROR: invalid FOR VALUES specification for a range partition+DROP TABLE range_partitioned CASCADE;+CREATE TABLE list_partitioned (+ a int+) PARTITION BY LIST ON (a);+CREATE TABLE fail_partition+ PARTITION OF list_partitioned+ FOR VALUES LESS THAN (100); -- fail+ERROR: invalid FOR VALUES specification for a list partition+DROP TABLE list_partitioned CASCADE;+--+-- creating range partitions sanely+--+CREATE TABLE sales (+ tos timestamp NOT NULL,+ item text+) PARTITION BY RANGE ON (extract(year from tos), extract(month from tos));+CREATE TABLE sales_before_2015+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 1); -- ok+CREATE TABLE sales_2015_q1+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 4); -- ok+-- effective partition range empty or overlapping+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 4); -- fail+ERROR: cannot create range partition with empty range+HINT: Please specify END value that is greater than max bound of the last partition+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 3); -- fail+ERROR: cannot create range partition with range overlapping existing partitions+HINT: Please specify END value that is greater than max bound of the last partition+-- more bound values than key columns+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 3, 1); -- fail+ERROR: range partition FOR VALUES specification does not match the partition key+DROP TABLE sales CASCADE;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to partition sales_before_2015+drop cascades to partition sales_2015_q1+--+-- creating list partitions sanely+--+CREATE TABLE store (+ id int,+ city text NOT NULL+) PARTITION BY LIST ON (city);+CREATE TABLE store_east+ PARTITION OF store+ FOR VALUES IN ('New York', 'Boston'); -- ok+CREATE TABLE store_west+ PARTITION OF store+ FOR VALUES ('San Fransisco', 'Seattle'); -- ok+-- overlap with some existing partition+CREATE TABLE store_ca+ PARTITION OF store+ FOR VALUES ('Los Angeles', 'San Fransisco'); -- fail+ERROR: cannot create list partition that overlaps with an existing partition+DETAIL: New partition's definition overlaps with partition "store_west".+DROP TABLE store CASCADE;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to partition store_east+drop cascades to partition store_west+--+-- relpersistence of partitioned table and partitions+--+CREATE TEMP TABLE foo (+ a int+) PARTITION BY RANGE ON (a);+CREATE TEMP TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- ok+-- cannot be non-temp partition of temp table+CREATE TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+ERROR: cannot create a non-temporary partition of temporary table "foo"+DROP TABLE foo CASCADE;+NOTICE: drop cascades to partition foo_1+CREATE UNLOGGED TABLE foo (+ a int+) PARTITION BY RANGE ON (a);+CREATE UNLOGGED TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100);+-- cannot be logged/permanent partition of unlogged table+CREATE TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+ERROR: cannot create a permanent partition of unlogged table "foo"+DROP TABLE foo CASCADE;+NOTICE: drop cascades to partition foo_1+CREATE TABLE foo (+ a int+) PARTITION BY RANGE ON (a);+-- cannot be temp partition of non-temp table+CREATE TEMP TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (200); -- fail+ERROR: cannot create a temporary partition of non-temporary table+-- cannot be unlogged partition of permanent table+CREATE UNLOGGED TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+ERROR: cannot create a unlogged partition of permanent table "foo"+DROP TABLE foo CASCADE;+--+-- ALTER TABLE tests+--+CREATE TABLE foo (+ a int+) PARTITION BY RANGE ON (a);+CREATE TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (10);+-- cannot add column to a partition+ALTER TABLE foo_1 ADD COLUMN b char; -- fail+ERROR: "foo_1" is not a table, composite type, or foreign table+ALTER TABLE foo ADD COLUMN b char; -- ok+SELECT * FROM foo;+ a | b +---+---+(0 rows)++-- cannot drop a column from a partition+ALTER TABLE foo_1 DROP COLUMN b; -- fail+ERROR: "foo_1" is not a table, composite type, or foreign table+ALTER TABLE foo DROP COLUMN b; -- ok+SELECT * FROM foo;+ a +---+(0 rows)++-- cannot drop a partition key column+ALTER TABLE foo DROP COLUMN a;+ERROR: cannot drop partition key column "a"+-- cannot alter NOT NULL constraint of column of a partition+ALTER TABLE foo_1 ALTER a SET NOT NULL; -- fail+ERROR: "foo_1" is not a table or foreign table+ALTER TABLE foo ALTER a SET NOT NULL; -- ok+\d foo_1;+ Table "public.foo_1"+ Column | Type | Modifiers +--------+---------+-----------+ a | integer | not null++ALTER TABLE foo_1 ALTER a DROP NOT NULL; -- fail+ERROR: "foo_1" is not a table or foreign table+ALTER TABLE foo ALTER a DROP NOT NULL; -- ok+\d foo_1;+ Table "public.foo_1"+ Column | Type | Modifiers +--------+---------+-----------+ a | integer | ++-- cannot alter column default of a partition+ALTER TABLE foo_1 ALTER COLUMN a SET DEFAULT 0; -- fail+ERROR: "foo_1" is not a table, view, or foreign table+ALTER TABLE foo ALTER COLUMN a SET DEFAULT 0; -- ok+INSERT INTO foo DEFAULT VALUES;+SELECT * FROM foo;+ a +---+ 0+(1 row)++ALTER TABLE foo_1 ALTER COLUMN a DROP DEFAULT; -- fail+ERROR: "foo_1" is not a table, view, or foreign table+ALTER TABLE foo ALTER COLUMN a DROP DEFAULT; -- ok+INSERT INTO foo DEFAULT VALUES; -- fail (null default)+ERROR: null value for partition key column "a" not allowed+-- cannot alter column type+ALTER TABLE foo ADD COLUMN b text;+INSERT INTO foo VALUES (9, 'ab');+ALTER TABLE foo_1 ALTER b TYPE char; -- fail+ERROR: "foo_1" is not a table, composite type, or foreign table+ALTER TABLE foo ALTER b TYPE char; -- fail (found values too long)+ERROR: value too long for type character(1)+UPDATE foo SET b = 'a' WHERE length(b) > 1;+ALTER TABLE foo ALTER b TYPE char; -- ok+SELECT * FROM foo;+ a | b +---+---+ 0 | + 9 | a+(2 rows)++-- cannot alter type of a partition key column+ALTER TABLE foo ALTER a TYPE char;+ERROR: cannot alter type of partition key column "a"+-- cannot add/drop a table constraint on a partition+ALTER TABLE foo_1 ADD CONSTRAINT foo_chk_a CHECK (a >= 0); -- fail+ERROR: "foo_1" is not a table or foreign table+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0); -- ok+INSERT INTO foo VALUES (-1); -- fails+ERROR: new row for relation "foo" violates check constraint "foo_chk_a"+DETAIL: Failing row contains (-1, null).+INSERT INTO foo VALUES (0);+UPDATE foo SET a = -1 WHERE = a = 0 -- fails+-- cannot change persistence of a partition+ALTER TABLE foo_1 SET UNLOGGED; -- fail+ERROR: syntax error at or near "="+LINE 1: UPDATE foo SET a = -1 WHERE = a = 0 + ^+ALTER TABLE foo SET UNLOGGED; -- ok+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | ++--+-- OTOH, there are some AT commands that are OK to do on partitions+--+-- can validate a constraint+ALTER TABLE foo DROP CONSTRAINT foo_chk_a;+INSERT INTO foo VALUES (-1);+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0) NOT VALID;+ALTER TABLE foo_1 VALIDATE CONSTRAINT foo_chk_a; -- fail+ERROR: check constraint "foo_chk_a" is violated by some row+DELETE FROM foo WHERE a = -1;+ALTER TABLE foo_1 VALIDATE CONSTRAINT foo_chk_a; -- ok+-- or validate it on the whole partitioned table+ALTER TABLE foo DROP CONSTRAINT foo_chk_a;+INSERT INTO foo VALUES (-1); -- ok+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0) NOT VALID;+ALTER TABLE foo VALIDATE CONSTRAINT foo_chk_a; -- fail+ERROR: check constraint "foo_chk_a" is violated by some row+DELETE FROM foo WHERE a = -1;+ALTER TABLE foo VALIDATE CONSTRAINT foo_chk_a; -- ok+-- can set/reset attribute options+ALTER TABLE foo_1 ALTER a SET (n_distinct=10); -- ok+-- can set attribute storage+ALTER TABLE foo ADD COLUMN b text;+ERROR: column "b" of relation "foo" already exists+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | ++ALTER TABLE foo_1 ALTER b SET STORAGE EXTERNAL;+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | ++-- can create index-creating constraint+DELETE FROM foo;+ALTER TABLE foo_1 ADD UNIQUE (a);+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | +Indexes:+ "foo_1_a_key" UNIQUE CONSTRAINT, btree (a)++-- can create a constraint using index+CREATE INDEX foo_1_b_idx ON foo_1 (b);+ALTER TABLE foo_1 ADD UNIQUE USING foo_1_b_idx;+ERROR: syntax error at or near "foo_1_b_idx"+LINE 1: ALTER TABLE foo_1 ADD UNIQUE USING foo_1_b_idx;+ ^+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | +Indexes:+ "foo_1_a_key" UNIQUE CONSTRAINT, btree (a)+ "foo_1_b_idx" btree (b)++-- can drop index constraints (others cannot be created at all)+ALTER TABLE foo_1 DROP CONSTRAINT foo_1_a_key;+-- can set/reset CLUSTER ON+ALTER TABLE foo_1 CLUSTER ON foo_1_a_idx;+ERROR: index "foo_1_a_idx" for table "foo_1" does not exist+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | +Indexes:+ "foo_1_b_idx" btree (b)++ALTER TABLE foo_1 SET WITHOUT CLUSTER;+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | +Indexes:+ "foo_1_b_idx" btree (b)++-- can set/reset table storage options+ALTER TABLE foo_1 SET (fillfactor=80)+\d+ foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers | Storage | Stats target | Description +--------+--------------+-----------+----------+--------------+-------------+ a | integer | | plain | | + b | character(1) | | external | | +Indexes:+ "foo_1_b_idx" btree (b)++ALTER TABLE foo_1 RESET (fillfactor)+\d+ foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers | Storage | Stats target | Description +--------+--------------+-----------+----------+--------------+-------------+ a | integer | | plain | | + b | character(1) | | external | | +Indexes:+ "foo_1_b_idx" btree (b)++-- can set replica identity+ALTER TABLE foo ALTER a SET NOT NULL;+ERROR: syntax error at or near "ALTER"+LINE 2: ALTER TABLE foo_1 RESET (fillfactor)+ ^+ALTER TABLE foo_1 REPLICA IDENTITY USING INDEX foo_1_a_idx;+ERROR: index "foo_1_a_idx" for table "foo_1" does not exist+\d foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers +--------+--------------+-----------+ a | integer | + b | character(1) | +Indexes:+ "foo_1_b_idx" btree (b)++ALTER TABLE foo_1 REPLICA IDENTITY NOTHING;+\d+ foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers | Storage | Stats target | Description +--------+--------------+-----------+----------+--------------+-------------+ a | integer | | plain | | + b | character(1) | | external | | +Indexes:+ "foo_1_b_idx" btree (b)+Replica Identity: NOTHING++-- can set statistics on a column+ALTER TABLE foo_1 ALTER a SET STATISTICS 1;+\d+ foo_1+ Unlogged table "public.foo_1"+ Column | Type | Modifiers | Storage | Stats target | Description +--------+--------------+-----------+----------+--------------+-------------+ a | integer | | plain | 1 | + b | character(1) | | external | | +Indexes:+ "foo_1_b_idx" btree (b)+Replica Identity: NOTHING++--+-- some more tests+--+-- cannot drop paritions+DROP TABLE foo_1; -- fail+ERROR: cannot drop table "foo_1"+DETAIL: "foo_1" is a partition of table "foo".+HINT: You will need to detach the partition first.+-- detach partition+ALTER TABLE foo DETACH PARTITION foo_1 USING foo_1_table;+-- now drop 'table' foo_1_table+DROP TABLE foo_1_table; -- ok+-- feature not implemented message+ALTER TABLE foo ATTACH PARTITION foo_1 FOR VALUES LESS THAN (10) USING TABLE foo_1_table;+ERROR: ALTER TABLE ATTACH PARTITION (...) USING TABLE not implemented+DROP TABLE foo CASCADE;+--+-- some commands are not ready for either partitioned tables or partitions+--+CREATE TABLE command_fail_tab (a int) PARTITION BY RANGE ON (a);+CREATE TABLE command_fail_partition PARTITION OF command_fail_tab FOR VALUES LESS THAN (10);+VACUUM command_fail_tab; -- fail+ERROR: cannot vacuum partitioned table "command_fail_tab"+HINT: Run VACUUM on individual partitions one-by-one.+CLUSTER command_fail_tab; -- fail+ERROR: cannot cluster partitioned table "command_fail_tab"+HINT: Run CLUSTER on individual partitions one-by-one.+REINDEX TABLE command_fail_tab; -- fail+ERROR: cannot reindex partitioned table "command_fail_tab"+HINT: Run REINDEX on individual partitions one-by-one.+ANALYZE command_fail_tab; -- fail+ERROR: cannot analyze partitioned table "command_fail_tab"+DETAIL: ANALYZE on partitioned tables is not implemented yet.+HINT: ANALYZE individual partitions separately.+COPY command_fail_tab TO stdout; -- fail;+ERROR: cannot copy from partitioned table "command_fail_tab"+HINT: Try the COPY (SELECT ...) TO variant.+COPY command_fail_partition FROM stdin; -- fail+ERROR: cannot copy to a partition of another table+HINT: Perform COPY on the parent instead.+DROP TABLE command_fail_tab CASCADE;+NOTICE: drop cascades to partition command_fail_partitiondiff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex c63abf4..38d78b8 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -60,7 +60,7 @@ test: create_index create_view
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes+test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes partitioned_table
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_scheduleindex 88dcd64..b98865e 100644--- a/src/test/regress/serial_schedule+++ b/src/test/regress/serial_schedule@@ -159,3 +159,4 @@ test: with
test: xml
test: event_trigger
test: stats
+test: partitioned_tablediff --git a/src/test/regress/sql/partitioned_table.sql b/src/test/regress/sql/partitioned_table.sqlnew file mode 100644index 0000000..e58e427--- /dev/null+++ b/src/test/regress/sql/partitioned_table.sql@@ -0,0 +1,551 @@+--+-- create a range partitioned table+--+CREATE TABLE person_partitioned (+ name text,+ age int,+ city text+) PARTITION BY RANGE ON (age);++-- select (empty result yet)+EXPLAIN (costs off) SELECT * FROM person_partitioned;+SELECT * FROM person_partitioned;++-- cannot insert data into it without creating a partition+INSERT INTO person_partitioned VALUES ('Bob', 23, 'Boston'); -- fail++-- create a couple of range partitions+CREATE TABLE person_partitioned_30+ PARTITION OF person_partitioned+ FOR VALUES LESS THAN (30);+CREATE TABLE person_partitioned_50+ PARTITION OF person_partitioned+ FOR VALUES LESS THAN (50);++INSERT INTO person_partitioned VALUES ('Bob', 23, 'Boston'); -- ok+INSERT INTO person_partitioned VALUES ('Alice', 32, 'Austin'); -- ok+INSERT INTO person_partitioned VALUES ('Jon', 52, 'Seattle'); -- fail++-- select (non-empty result)+EXPLAIN (costs off) SELECT * FROM person_partitioned;+SELECT * FROM person_partitioned;++-- no partition selected+EXPLAIN (costs off) SELECT * FROM person_partitioned WHERE age > 50;+SELECT * FROM person_partitioned WHERE age > 50;++-- one partition selected+EXPLAIN (costs off) SELECT * FROM person_partitioned WHERE age < 25;+SELECT * FROM person_partitioned WHERE age < 25;++-- cannot update partition directly+UPDATE person_partitioned_50 SET city = 'Dallas' WHERE name = 'Alice';++-- so update parent+EXPLAIN (costs off) UPDATE person_partitioned SET city = 'Dallas' WHERE name = 'Alice';+UPDATE person_partitioned SET city = 'Dallas' WHERE name = 'Alice';++-- following update changes the value of partition key+UPDATE person_partitioned SET age = 52 WHERE name = 'Alice'; -- fail+UPDATE person_partitioned SET age = 42 WHERE name = 'Alice'; -- ok++--+-- create a list partitioend table+--+CREATE TABLE city_partitioned (+ name text+) PARTITION BY LIST ON (name);++-- select (empty result yet)+EXPLAIN (costs off) SELECT * FROM city_partitioned;+SELECT * FROM city_partitioned;++-- cannot insert data into it without creating a partition+INSERT INTO city_partitioned VALUES ('Boston'); -- fail++-- create a couple of list partitions+CREATE TABLE city_east+ PARTITION OF city_partitioned+ FOR VALUES IN ('New York', 'Boston');+CREATE TABLE city_west+ PARTITION OF city_partitioned+ FOR VALUES IN ('Los Angeles');++INSERT INTO city_partitioned VALUES ('Boston'); -- ok+INSERT INTO city_partitioned VALUES ('Austin'); -- fail++-- select (non-empty result)+EXPLAIN (costs off) SELECT * FROM city_partitioned;+SELECT * FROM city_partitioned;++-- no partition selected+EXPLAIN (costs off) SELECT * FROM city_partitioned WHERE name = 'Austin';+SELECT * FROM city_partitioned WHERE name = 'Austin';++-- one partition selected+EXPLAIN (costs off) SELECT * FROM city_partitioned WHERE name = 'Boston';+SELECT * FROM city_partitioned WHERE name = 'Boston';++-- can delete from partition directly+DELETE FROM city_east WHERE name = 'Boston'; -- ok++-- or from parent+DELETE FROM city_east WHERE name = 'Boston'; -- 0 deleted++-- dropping partitioned table requires CASCADE if partitions exist+DROP TABLE person_partitioned; -- fail+DROP TABLE person_partitioned CASCADE; -- ok+DROP TABLE city_partitioned CASCADE;+++--+-- test some planner bits+--+CREATE TABLE rangetab (a int) PARTITION BY RANGE ON (a);+CREATE TABLE rangetab1 PARTITION OF rangetab FOR VALUES LESS THAN (10);+CREATE INDEX rangetab1_a_idx ON rangetab1 (a);+CREATE TABLE rangetab2 PARTITION OF rangetab FOR VALUES LESS THAN (20);+CREATE INDEX rangetab2_a_idx ON rangetab2 (a);+CREATE TABLE rangetab3 PARTITION OF rangetab FOR VALUES LESS THAN (30);+CREATE INDEX rangetab3_a_idx ON rangetab3 (a);+CREATE TABLE rangetab4 PARTITION OF rangetab FOR VALUES LESS THAN (40);+CREATE INDEX rangetab4_a_idx ON rangetab3 (a);+INSERT INTO rangetab SELECT generate_series(1, 39);++-- scan rangetab2 and rangetab3+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a BETWEEN 15 AND 25;++-- scan rangetab1 and rangetab2+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a <= 10;++-- scan rangetab2, rangetab3, rangetab4+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 10;++-- scan rangetab1+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a < 10 AND a < 20;+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 1 AND a < 20;++-- scan rangetab4+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 35 AND a > 25;+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 35 AND a > 20;++-- scan nothing+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 AND a < 5; -- mutually refute+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 AND a > 15; -- ditto+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 40; -- no partitions beyond 40++-- scan everything+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a > 1;+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = 10 OR a > 1; -- OR clause turns pruning off (for now)+EXPLAIN (costs off) SELECT * FROM rangetab WHERE a IN (1, 11, 21); -- array op turns pruning off (for now)++CREATE TABLE plaintab (a int, b text);+CREATE INDEX plaintab_a_idx ON plaintab (a);+INSERT INTO plaintab SELECT generate_series(1, 15), 'c';++EXPLAIN (costs off) SELECT * FROM rangetab WHERE a = (SELECT max(a) FROM plaintab); -- only use Consts to prune++-- might as well check prepared statement+PREPARE x AS SELECT * FROM rangetab WHERE a = $1;+EXPLAIN EXECUTE x(1); -- scan rangetab+EXPLAIN EXECUTE x(10); -- scan rangetab2+DEALLOCATE x;++-- check equivalence processing+EXPLAIN (costs off) SELECT * FROM rangetab t1, plaintab t2 WHERE t1.a = t2.a AND t2.a = 1; -- scan only rangetab1++DROP TABLE rangetab CASCADE;++-- why not check for list partitioned case, too+CREATE TABLE listtab (a text) PARTITION BY LIST ON (a);+CREATE TABLE listtab1 PARTITION OF listtab FOR VALUES IN ('a', 'b');+CREATE INDEX listtab1_a_idx ON listtab1 (a);+CREATE TABLE listtab2 PARTITION OF listtab FOR VALUES IN ('c');+CREATE INDEX listtab2_a_idx ON listtab2 (a);++-- one might think only scans parttab1 - scans everything+EXPLAIN (costs off) SELECT * FROM listtab WHERE a IN ('a', 'b'); -- array op turns off pruning (for now)+EXPLAIN (costs off) SELECT * FROM listtab WHERE a = 'a' OR a = 'b'; -- OR turns off pruning (for now)++-- scan listtab1+EXPLAIN (costs off) SELECT * FROM listtab WHERE a = 'b';++-- check equivalence processing+EXPLAIN (costs off) SELECT * FROM listtab t1, plaintab t2 WHERE t1.a = t2.b AND t2.b = 'c'; -- scan only listtab2++DROP TABLE plaintab;+DROP TABLE listtab CASCADE;++--+-- ddl related tests+--++-- cannot use system columns in partition key+CREATE TABLE fail_oid_pk (+ a int+) PARTITION BY RANGE ON (oid) WITH OIDS; -- fail++-- cannot use more than 1 column as partition key for list partitioned table+CREATE TABLE two_col_tab (+ a1 int,+ a2 int+) PARTITION BY LIST ON (a1, a2); -- fail++-- cannot use more than 16 columns as partition key for range partitioned table+CREATE TABLE wide_tab (+ a1 int,+ a2 int,+ a3 int,+ a4 int,+ a5 int,+ a6 int,+ a7 int,+ a8 int,+ a9 int,+ a10 int,+ a11 int,+ a12 int,+ a13 int,+ a14 int,+ a15 int,+ a16 int+) PARTITION BY RANGE ON (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16); -- ok+DROP TABLE wide_tab CASCADE;++CREATE TABLE extra_wide_tab (+ a1 int,+ a2 int,+ a3 int,+ a4 int,+ a5 int,+ a6 int,+ a7 int,+ a8 int,+ a9 int,+ a10 int,+ a11 int,+ a12 int,+ a13 int,+ a14 int,+ a15 int,+ a16 int,+ a17 int+) PARTITION BY RANGE ON (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17); -- fail++-- At the moment, partitioning requires to use columns with data type having+-- a suitable btree operator class+CREATE TABLE point_tab (a point) PARTITION BY LIST ON (a); -- fail+CREATE TABLE point_tab (a point) PARTITION BY LIST ON (a USING point_ops); -- fail+CREATE TABLE point_tab (a point) PARTITION BY RANGE ON (a); -- fail+CREATE TABLE point_tab (a point) PARTITION BY RANGE ON (a USING point_ops); -- fail++-- fail to create a partitioned table with PRIMARY KEY constraint+CREATE TABLE pk_fail (a int PRIMARY KEY) PARTITION BY RANGE ON (a);++-- fail to create a partitioned table with foreign key constraint+CREATE TABLE pk_rel(a int PRIMARY KEY);+CREATE TABLE pk_fail (a int REFERENCES pk_rel(a)) PARTITION BY RANGE ON (a);+DROP TABLE pk_rel;++-- fail to let a partitioned table be referenced in a foreign key+CREATE TABLE foo (a int) PARTITION BY RANGE ON (a);+CREATE TABLE bar_ref_foo (a int REFERENCES foo(a));+DROP TABLE foo CASCADE;++-- fail to create a partitioned table with prohibited expressions in key+CREATE TABLE fail_const_key (a int) PARTITION BY RANGE ON (('a'));+CREATE TABLE fail_agg_in_key (a int) PARTITION BY RANGE ON ((avg(a)));+CREATE TABLE fail_window_fun_in_key (a int, b int) PARTITION BY RANGE ON ((avg(a) OVER (PARTITION BY b)));++--+-- creating partitions with sane FOR VALUES specification+--+CREATE TABLE range_partitioned (+ a int+) PARTITION BY RANGE ON (a);++CREATE TABLE fail_partition+ PARTITION OF range_partitioned+ FOR VALUES IN (1, 2); -- fail+DROP TABLE range_partitioned CASCADE;++CREATE TABLE list_partitioned (+ a int+) PARTITION BY LIST ON (a);++CREATE TABLE fail_partition+ PARTITION OF list_partitioned+ FOR VALUES LESS THAN (100); -- fail+DROP TABLE list_partitioned CASCADE;++--+-- creating range partitions sanely+--+CREATE TABLE sales (+ tos timestamp NOT NULL,+ item text+) PARTITION BY RANGE ON (extract(year from tos), extract(month from tos));++CREATE TABLE sales_before_2015+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 1); -- ok++CREATE TABLE sales_2015_q1+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 4); -- ok++-- effective partition range empty or overlapping+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 4); -- fail+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 3); -- fail++-- more bound values than key columns+CREATE TABLE sales_2015_q2+ PARTITION OF sales+ FOR VALUES LESS THAN (2015, 3, 1); -- fail+DROP TABLE sales CASCADE;++--+-- creating list partitions sanely+--+CREATE TABLE store (+ id int,+ city text NOT NULL+) PARTITION BY LIST ON (city);++CREATE TABLE store_east+ PARTITION OF store+ FOR VALUES IN ('New York', 'Boston'); -- ok++CREATE TABLE store_west+ PARTITION OF store+ FOR VALUES ('San Fransisco', 'Seattle'); -- ok++-- overlap with some existing partition+CREATE TABLE store_ca+ PARTITION OF store+ FOR VALUES ('Los Angeles', 'San Fransisco'); -- fail++DROP TABLE store CASCADE;++--+-- relpersistence of partitioned table and partitions+--+CREATE TEMP TABLE foo (+ a int+) PARTITION BY RANGE ON (a);++CREATE TEMP TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- ok++-- cannot be non-temp partition of temp table+CREATE TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+DROP TABLE foo CASCADE;++CREATE UNLOGGED TABLE foo (+ a int+) PARTITION BY RANGE ON (a);++CREATE UNLOGGED TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100);++-- cannot be logged/permanent partition of unlogged table+CREATE TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+DROP TABLE foo CASCADE;++CREATE TABLE foo (+ a int+) PARTITION BY RANGE ON (a);++-- cannot be temp partition of non-temp table+CREATE TEMP TABLE foo_2+ PARTITION OF foo+ FOR VALUES LESS THAN (200); -- fail++-- cannot be unlogged partition of permanent table+CREATE UNLOGGED TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (100); -- fail+DROP TABLE foo CASCADE;++--+-- ALTER TABLE tests+--+CREATE TABLE foo (+ a int+) PARTITION BY RANGE ON (a);++CREATE TABLE foo_1+ PARTITION OF foo+ FOR VALUES LESS THAN (10);++-- cannot add column to a partition+ALTER TABLE foo_1 ADD COLUMN b char; -- fail+ALTER TABLE foo ADD COLUMN b char; -- ok+SELECT * FROM foo;++-- cannot drop a column from a partition+ALTER TABLE foo_1 DROP COLUMN b; -- fail+ALTER TABLE foo DROP COLUMN b; -- ok+SELECT * FROM foo;++-- cannot drop a partition key column+ALTER TABLE foo DROP COLUMN a;++-- cannot alter NOT NULL constraint of column of a partition+ALTER TABLE foo_1 ALTER a SET NOT NULL; -- fail+ALTER TABLE foo ALTER a SET NOT NULL; -- ok+\d foo_1;++ALTER TABLE foo_1 ALTER a DROP NOT NULL; -- fail+ALTER TABLE foo ALTER a DROP NOT NULL; -- ok+\d foo_1;++-- cannot alter column default of a partition+ALTER TABLE foo_1 ALTER COLUMN a SET DEFAULT 0; -- fail+ALTER TABLE foo ALTER COLUMN a SET DEFAULT 0; -- ok++INSERT INTO foo DEFAULT VALUES;+SELECT * FROM foo;++ALTER TABLE foo_1 ALTER COLUMN a DROP DEFAULT; -- fail+ALTER TABLE foo ALTER COLUMN a DROP DEFAULT; -- ok++INSERT INTO foo DEFAULT VALUES; -- fail (null default)++-- cannot alter column type+ALTER TABLE foo ADD COLUMN b text;+INSERT INTO foo VALUES (9, 'ab');+ALTER TABLE foo_1 ALTER b TYPE char; -- fail+ALTER TABLE foo ALTER b TYPE char; -- fail (found values too long)+UPDATE foo SET b = 'a' WHERE length(b) > 1;+ALTER TABLE foo ALTER b TYPE char; -- ok+SELECT * FROM foo;++-- cannot alter type of a partition key column+ALTER TABLE foo ALTER a TYPE char;++-- cannot add/drop a table constraint on a partition+ALTER TABLE foo_1 ADD CONSTRAINT foo_chk_a CHECK (a >= 0); -- fail+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0); -- ok++INSERT INTO foo VALUES (-1); -- fails+INSERT INTO foo VALUES (0);+UPDATE foo SET a = -1 WHERE = a = 0 -- fails++-- cannot change persistence of a partition+ALTER TABLE foo_1 SET UNLOGGED; -- fail+ALTER TABLE foo SET UNLOGGED; -- ok+\d foo_1++--+-- OTOH, there are some AT commands that are OK to do on partitions+--++-- can validate a constraint+ALTER TABLE foo DROP CONSTRAINT foo_chk_a;+INSERT INTO foo VALUES (-1);+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0) NOT VALID;+ALTER TABLE foo_1 VALIDATE CONSTRAINT foo_chk_a; -- fail+DELETE FROM foo WHERE a = -1;+ALTER TABLE foo_1 VALIDATE CONSTRAINT foo_chk_a; -- ok++-- or validate it on the whole partitioned table+ALTER TABLE foo DROP CONSTRAINT foo_chk_a;+INSERT INTO foo VALUES (-1); -- ok+ALTER TABLE foo ADD CONSTRAINT foo_chk_a CHECK (a >= 0) NOT VALID;+ALTER TABLE foo VALIDATE CONSTRAINT foo_chk_a; -- fail+DELETE FROM foo WHERE a = -1;+ALTER TABLE foo VALIDATE CONSTRAINT foo_chk_a; -- ok++-- can set/reset attribute options+ALTER TABLE foo_1 ALTER a SET (n_distinct=10); -- ok++-- can set attribute storage+ALTER TABLE foo ADD COLUMN b text;+\d foo_1+ALTER TABLE foo_1 ALTER b SET STORAGE EXTERNAL;+\d foo_1++-- can create index-creating constraint+DELETE FROM foo;+ALTER TABLE foo_1 ADD UNIQUE (a);+\d foo_1++-- can create a constraint using index+CREATE INDEX foo_1_b_idx ON foo_1 (b);+ALTER TABLE foo_1 ADD UNIQUE USING foo_1_b_idx;+\d foo_1++-- can drop index constraints (others cannot be created at all)+ALTER TABLE foo_1 DROP CONSTRAINT foo_1_a_key;++-- can set/reset CLUSTER ON+ALTER TABLE foo_1 CLUSTER ON foo_1_a_idx;+\d foo_1+ALTER TABLE foo_1 SET WITHOUT CLUSTER;+\d foo_1++-- can set/reset table storage options+ALTER TABLE foo_1 SET (fillfactor=80)+\d+ foo_1+ALTER TABLE foo_1 RESET (fillfactor)+\d+ foo_1++-- can set replica identity+ALTER TABLE foo ALTER a SET NOT NULL;+ALTER TABLE foo_1 REPLICA IDENTITY USING INDEX foo_1_a_idx;+\d foo_1++ALTER TABLE foo_1 REPLICA IDENTITY NOTHING;+\d+ foo_1++-- can set statistics on a column+ALTER TABLE foo_1 ALTER a SET STATISTICS 1;+\d+ foo_1++--+-- some more tests+--++-- cannot drop paritions+DROP TABLE foo_1; -- fail++-- detach partition+ALTER TABLE foo DETACH PARTITION foo_1 USING foo_1_table;++-- now drop 'table' foo_1_table+DROP TABLE foo_1_table; -- ok++-- feature not implemented message+ALTER TABLE foo ATTACH PARTITION foo_1 FOR VALUES LESS THAN (10) USING TABLE foo_1_table;++DROP TABLE foo CASCADE;++--+-- some commands are not ready for either partitioned tables or partitions+--+CREATE TABLE command_fail_tab (a int) PARTITION BY RANGE ON (a);+CREATE TABLE command_fail_partition PARTITION OF command_fail_tab FOR VALUES LESS THAN (10);++VACUUM command_fail_tab; -- fail+CLUSTER command_fail_tab; -- fail+REINDEX TABLE command_fail_tab; -- fail+ANALYZE command_fail_tab; -- fail++COPY command_fail_tab TO stdout; -- fail;+COPY command_fail_partition FROM stdin; -- fail+1+2+\.++DROP TABLE command_fail_tab CASCADE;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, simon@2ndquadrant.com
Subject: Re: Declarative partitioning
In-Reply-To: <563341AE.5010207@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox