Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf0e4475

Browse files
committed
Implement table partitioning.
Table partitioning is like table inheritance and reuses much of theexisting infrastructure, but there are some important differences.The parent is called a partitioned table and is always empty; it maynot have indexes or non-inherited constraints, since those make nosense for a relation with no data of its own. The children are calledpartitions and contain all of the actual data. Each partition has animplicit partitioning constraint. Multiple inheritance is notallowed, and partitioning and inheritance can't be mixed. Partitionscan't have extra columns and may not allow nulls unless the parentdoes. Tuples inserted into the parent are automatically routed to thecorrect partition, so tuple-routing ON INSERT triggers are not needed.Tuple routing isn't yet supported for partitions which are foreigntables, and it doesn't handle updates that cross partition boundaries.Currently, tables can be range-partitioned or list-partitioned. Listpartitioning is limited to a single column, but range partitioning caninvolve multiple columns. A partitioning "column" can be anexpression.Because table partitioning is less general than table inheritance, itis hoped that it will be easier to reason about properties ofpartitions, and therefore that this will serve as a better foundationfor a variety of possible optimizations, including query planneroptimizations. The tuple routing based which this patch does based onthe implicit partitioning constraints is an example of this, but itseems likely that many other useful optimizations are also possible.Amit Langote, reviewed and tested by Robert Haas, Ashutosh Bapat,Amit Kapila, Rajkumar Raghuwanshi, Corey Huinker, Jaime Casanova,Rushabh Lathia, Erik Rijkers, among others. Minor revisions by me.
1 parentb7e1ae2 commitf0e4475

File tree

85 files changed

+8886
-271
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

85 files changed

+8886
-271
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 128 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -225,6 +225,11 @@
225225
<entry>template data for procedural languages</entry>
226226
</row>
227227

228+
<row>
229+
<entry><link linkend="catalog-pg-partitioned-table"><structname>pg_partitioned_table</structname></link></entry>
230+
<entry>information about partition key of tables</entry>
231+
</row>
232+
228233
<row>
229234
<entry><link linkend="catalog-pg-policy"><structname>pg_policy</structname></link></entry>
230235
<entry>row-security policies</entry>
@@ -1723,7 +1728,8 @@
17231728
<entry><type>char</type></entry>
17241729
<entry></entry>
17251730
<entry>
1726-
<literal>r</> = ordinary table, <literal>i</> = index,
1731+
<literal>r</> = ordinary table, <literal>P</> = partitioned table,
1732+
<literal>i</> = index
17271733
<literal>S</> = sequence, <literal>v</> = view,
17281734
<literal>m</> = materialized view,
17291735
<literal>c</> = composite type, <literal>t</> = TOAST table,
@@ -1839,6 +1845,13 @@
18391845
</entry>
18401846
</row>
18411847

1848+
<row>
1849+
<entry><structfield>relispartition</structfield></entry>
1850+
<entry><type>bool</type></entry>
1851+
<entry></entry>
1852+
<entry>True if table is a partition</entry>
1853+
</row>
1854+
18421855
<row>
18431856
<entry><structfield>relfrozenxid</structfield></entry>
18441857
<entry><type>xid</type></entry>
@@ -1885,6 +1898,16 @@
18851898
Access-method-specific options, as <quote>keyword=value</> strings
18861899
</entry>
18871900
</row>
1901+
1902+
<row>
1903+
<entry><structfield>relpartbound</structfield></entry>
1904+
<entry><type>pg_node_tree</type></entry>
1905+
<entry></entry>
1906+
<entry>
1907+
If table is a partition (see <structfield>relispartition</structfield>),
1908+
internal representation of the partition bound
1909+
</entry>
1910+
</row>
18881911
</tbody>
18891912
</tgroup>
18901913
</table>
@@ -4689,6 +4712,110 @@
46894712

46904713
</sect1>
46914714

4715+
<sect1 id="catalog-pg-partitioned-table">
4716+
<title><structname>pg_partitioned_table</structname></title>
4717+
4718+
<indexterm zone="catalog-pg-partitioned-table">
4719+
<primary>pg_partitioned_table</primary>
4720+
</indexterm>
4721+
4722+
<para>
4723+
The catalog <structname>pg_partitioned_table</structname> stores
4724+
information about how tables are partitioned.
4725+
</para>
4726+
4727+
<table>
4728+
<title><structname>pg_partitioned_table</> Columns</title>
4729+
4730+
<tgroup cols="4">
4731+
<thead>
4732+
<row>
4733+
<entry>Name</entry>
4734+
<entry>Type</entry>
4735+
<entry>References</entry>
4736+
<entry>Description</entry>
4737+
</row>
4738+
</thead>
4739+
4740+
<tbody>
4741+
4742+
<row>
4743+
<entry><structfield>partrelid</structfield></entry>
4744+
<entry><type>oid</type></entry>
4745+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
4746+
<entry>The OID of the <structname>pg_class</> entry for this partitioned table</entry>
4747+
</row>
4748+
4749+
<row>
4750+
<entry><structfield>partstrat</structfield></entry>
4751+
<entry><type>char</type></entry>
4752+
<entry></entry>
4753+
<entry>
4754+
Partitioning strategy; <literal>l</> = list partitioned table,
4755+
<literal>r</> = range partitioned table
4756+
</entry>
4757+
</row>
4758+
4759+
<row>
4760+
<entry><structfield>partnatts</structfield></entry>
4761+
<entry><type>int2</type></entry>
4762+
<entry></entry>
4763+
<entry>The number of columns in partition key</entry>
4764+
</row>
4765+
4766+
<row>
4767+
<entry><structfield>partattrs</structfield></entry>
4768+
<entry><type>int2vector</type></entry>
4769+
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
4770+
<entry>
4771+
This is an array of <structfield>partnatts</structfield> values that
4772+
indicate which table columns are part of the partition key. For
4773+
example, a value of <literal>1 3</literal> would mean that the first
4774+
and the third table columns make up the partition key. A zero in this
4775+
array indicates that the corresponding partition key column is an
4776+
expression, rather than a simple column reference.
4777+
</entry>
4778+
</row>
4779+
4780+
<row>
4781+
<entry><structfield>partclass</structfield></entry>
4782+
<entry><type>oidvector</type></entry>
4783+
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
4784+
<entry>
4785+
For each column in the partition key, this contains the OID of the
4786+
operator class to use. See
4787+
<link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link> for details.
4788+
</entry>
4789+
</row>
4790+
4791+
<row>
4792+
<entry><structfield>partcollation</structfield></entry>
4793+
<entry><type>oidvector</type></entry>
4794+
<entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry>
4795+
<entry>
4796+
For each column in the partition key, this contains the OID of the
4797+
the collation to use for partitioning.
4798+
</entry>
4799+
</row>
4800+
4801+
<row>
4802+
<entry><structfield>partexprs</structfield></entry>
4803+
<entry><type>pg_node_tree</type></entry>
4804+
<entry></entry>
4805+
<entry>
4806+
Expression trees (in <function>nodeToString()</function>
4807+
representation) for partition key columns that are not simple column
4808+
references. This is a list with one element for each zero
4809+
entry in <structfield>partattrs</>. Null if all partition key columns
4810+
are simple references.
4811+
</entry>
4812+
</row>
4813+
4814+
</tbody>
4815+
</tgroup>
4816+
</table>
4817+
</sect1>
4818+
46924819
<sect1 id="catalog-pg-policy">
46934820
<title><structname>pg_policy</structname></title>
46944821

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 113 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
3333
SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
3434
ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
3535
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
36+
ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
37+
ATTACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable> FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable>
38+
ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
39+
DETACH PARTITION <replaceable class="PARAMETER">partition_name</replaceable>
3640

3741
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3842

@@ -166,6 +170,12 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
166170
values or to reject null values. You can only use <literal>SET
167171
NOT NULL</> when the column contains no null values.
168172
</para>
173+
174+
<para>
175+
If this table is a partition, one cannot perform <literal>DROP NOT NULL</>
176+
on a column if it is marked <literal>NOT NULL</literal> in the parent
177+
table.
178+
</para>
169179
</listitem>
170180
</varlistentry>
171181

@@ -704,13 +714,63 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
704714
</listitem>
705715
</varlistentry>
706716

717+
<varlistentry>
718+
<term><literal>ATTACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable> <replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
719+
<listitem>
720+
<para>
721+
This form attaches an existing table (which might itself be partitioned)
722+
as a partition of the target table using the same syntax for
723+
<replaceable class="PARAMETER">partition_bound_spec</replaceable> as
724+
<xref linkend="sql-createtable">. The partition bound specification
725+
must correspond to the partitioning strategy and partition key of the
726+
target table. The table to be attached must have all the same columns
727+
as the target table and no more; moreover, the column types must also
728+
match. Also, it must have all the <literal>NOT NULL</literal> and
729+
<literal>CHECK</literal> constraints of the target table. Currently
730+
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
731+
<literal>FOREIGN KEY</literal> constraints are not considered.
732+
If any of the <literal>CHECK</literal> constraints of the table being
733+
attached is marked <literal>NO INHERIT</literal>, the command will fail;
734+
such a constraint must be recreated without the <literal>NO INHERIT</literal>
735+
clause.
736+
</para>
737+
738+
<para>
739+
A full table scan is performed on the table being attached to check that
740+
no existing row in the table violates the partition constraint. It is
741+
possible to avoid this scan by adding a valid <literal>CHECK</literal>
742+
constraint to the table that would allow only the rows satisfying the
743+
desired partition constraint before running this command. It will be
744+
determined using such a constraint that the table need not be scanned
745+
to validate the partition constraint. This does not work, however, if
746+
any of the partition keys is an expression and the partition does not
747+
accept <literal>NULL</literal> values. If attaching a list partition
748+
that will not accept <literal>NULL</literal> values, also add
749+
<literal>NOT NULL</literal> constraint to the partition key column,
750+
unless it's an expression.
751+
</para>
752+
</listitem>
753+
</varlistentry>
754+
755+
<varlistentry>
756+
<term><literal>DETACH PARTITION</literal> <replaceable class="PARAMETER">partition_name</replaceable></term>
757+
<listitem>
758+
<para>
759+
This form detaches specified partition of the target table. The detached
760+
partition continues to exist as a standalone table, but no longer has any
761+
ties to the table from which it was detached.
762+
</para>
763+
</listitem>
764+
</varlistentry>
765+
707766
</variablelist>
708767
</para>
709768

710769
<para>
711770
All the actions except <literal>RENAME</literal>,
712-
<literal>SET TABLESPACE</literal> and <literal>SET SCHEMA</literal>
713-
can be combined into
771+
<literal>SET TABLESPACE</literal>, <literal>SET SCHEMA</literal>,
772+
<literal>ATTACH PARTITION</literal>, and
773+
<literal>DETACH PARTITION</literal> can be combined into
714774
a list of multiple alterations to apply in parallel. For example, it
715775
is possible to add several columns and/or alter the type of several
716776
columns in a single command. This is particularly useful with large
@@ -721,8 +781,9 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
721781
You must own the table to use <command>ALTER TABLE</>.
722782
To change the schema or tablespace of a table, you must also have
723783
<literal>CREATE</literal> privilege on the new schema or tablespace.
724-
To add the table as a new child of a parent table, you must own the
725-
parent table as well.
784+
To add the table as a new child of a parent table, you must own the parent
785+
table as well. Also, to attach a table as a new partition of the table,
786+
you must own the table being attached.
726787
To alter the owner, you must also be a direct or indirect member of the new
727788
owning role, and that role must have <literal>CREATE</literal> privilege on
728789
the table's schema. (These restrictions enforce that altering the owner
@@ -938,6 +999,25 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
938999
</listitem>
9391000
</varlistentry>
9401001

1002+
<varlistentry>
1003+
<term><replaceable class="PARAMETER">partition_name</replaceable></term>
1004+
<listitem>
1005+
<para>
1006+
The name of the table to attach as a new partition or to detach from this table.
1007+
</para>
1008+
</listitem>
1009+
</varlistentry>
1010+
1011+
<varlistentry>
1012+
<term><replaceable class="PARAMETER">partition_bound_spec</replaceable></term>
1013+
<listitem>
1014+
<para>
1015+
The partition bound specification for a new partition. Refer to
1016+
<xref linkend="sql-createtable"> for more details on the syntax of the same.
1017+
</para>
1018+
</listitem>
1019+
</varlistentry>
1020+
9411021
</variablelist>
9421022
</refsect1>
9431023

@@ -977,6 +1057,11 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
9771057
but does not require a table rewrite.
9781058
</para>
9791059

1060+
<para>
1061+
Similarly, when attaching a new partition it may be scanned to verify that
1062+
existing rows meet the partition constraint.
1063+
</para>
1064+
9801065
<para>
9811066
The main reason for providing the option to specify multiple changes
9821067
in a single <command>ALTER TABLE</> is that multiple table scans or
@@ -1047,6 +1132,9 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
10471132
COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
10481133
COLUMN</command>) never removes any descendant columns, but
10491134
instead marks them as independently defined rather than inherited.
1135+
A nonrecursive <literal>DROP COLUMN</literal> command will fail for a
1136+
partitioned table, because all partitions of a table must have the same
1137+
columns as the partitioning root.
10501138
</para>
10511139

10521140
<para>
@@ -1233,6 +1321,27 @@ ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
12331321
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
12341322
</programlisting></para>
12351323

1324+
<para>
1325+
Attach a partition to range partitioned table:
1326+
<programlisting>
1327+
ALTER TABLE measurement
1328+
ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1329+
</programlisting></para>
1330+
1331+
<para>
1332+
Attach a partition to list partitioned table:
1333+
<programlisting>
1334+
ALTER TABLE cities
1335+
ATTACH PARTITION cities_west FOR VALUES IN ('Los Angeles', 'San Francisco');
1336+
</programlisting></para>
1337+
1338+
<para>
1339+
Detach a partition from partitioned table:
1340+
<programlisting>
1341+
ALTER TABLE cities
1342+
DETACH PARTITION measurement_y2015m12;
1343+
</programlisting></para>
1344+
12361345
</refsect1>
12371346

12381347
<refsect1>

‎doc/src/sgml/ref/create_foreign_table.sgml

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,15 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
2727
SERVER <replaceable class="parameter">server_name</replaceable>
2828
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
2929

30+
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable>
31+
PARTITION OF <replaceable class="PARAMETER">parent_table</replaceable> [ (
32+
{ <replaceable class="PARAMETER">column_name</replaceable> WITH OPTIONS [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
33+
| <replaceable>table_constraint</replaceable> }
34+
[, ... ]
35+
) ] <replaceable class="PARAMETER">partition_bound_spec</replaceable>
36+
SERVER <replaceable class="parameter">server_name</replaceable>
37+
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
38+
3039
<phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
3140

3241
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -67,6 +76,12 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
6776
name as any existing data type in the same schema.
6877
</para>
6978

79+
<para>
80+
If <literal>PARTITION OF</literal> clause is specified then the table is
81+
created as a partition of <literal>parent_table</literal> with specified
82+
bounds.
83+
</para>
84+
7085
<para>
7186
To be able to create a foreign table, you must have <literal>USAGE</literal>
7287
privilege on the foreign server, as well as <literal>USAGE</literal>
@@ -314,6 +329,17 @@ CREATE FOREIGN TABLE films (
314329
SERVER film_server;
315330
</programlisting></para>
316331

332+
<para>
333+
Create foreign table <structname>measurement_y2016m07</>, which will be
334+
accessed through the server <structname>server_07</>, as a partition
335+
of the range partitioned table <structname>measurement</>:
336+
337+
<programlisting>
338+
CREATE FOREIGN TABLE measurement_y2016m07
339+
PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
340+
SERVER server_07;
341+
</programlisting></para>
342+
317343
</refsect1>
318344

319345
<refsect1 id="SQL-CREATEFOREIGNTABLE-compatibility">

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp