diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 38 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 165 |
2 files changed, 3 insertions, 200 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 626d35514cc..b671858627b 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4355,44 +4355,6 @@ ALTER INDEX measurement_city_id_logdate_key ... </programlisting> </para> - - <para> - There is also an option for merging multiple table partitions into - a single partition using the - <link linkend="sql-altertable-merge-partitions"><command>ALTER TABLE ... MERGE PARTITIONS</command></link>. - This feature simplifies the management of partitioned tables by allowing - users to combine partitions that are no longer needed as - separate entities. It's important to note that this operation is not - supported for hash-partitioned tables and acquires an - <literal>ACCESS EXCLUSIVE</literal> lock, which could impact high-load - systems due to the lock's restrictive nature. For example, we can - merge three monthly partitions into one quarter partition: -<programlisting> -ALTER TABLE measurement - MERGE PARTITIONS (measurement_y2006m01, - measurement_y2006m02, - measurement_y2006m03) INTO measurement_y2006q1; -</programlisting> - </para> - - <para> - Similarly to merging multiple table partitions, there is an option for - splitting a single partition into multiple using the - <link linkend="sql-altertable-split-partition"><command>ALTER TABLE ... SPLIT PARTITION</command></link>. - This feature could come in handy when one partition grows too big - and needs to be split into multiple. It's important to note that - this operation is not supported for hash-partitioned tables and acquires - an <literal>ACCESS EXCLUSIVE</literal> lock, which could impact high-load - systems due to the lock's restrictive nature. For example, we can split - the quarter partition back to monthly partitions: -<programlisting> -ALTER TABLE measurement SPLIT PARTITION measurement_y2006q1 INTO - (PARTITION measurement_y2006m01 FOR VALUES FROM ('2006-01-01') TO ('2006-02-01'), - PARTITION measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'), - PARTITION measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')); -</programlisting> - </para> - </sect3> <sect3 id="ddl-partitioning-declarative-limitations"> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 6a2822adad7..1a49f321cf7 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -37,13 +37,6 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ] -ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> - SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO - (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, - PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...]) -ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> - MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) - INTO <replaceable class="parameter">partition_name</replaceable> <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> @@ -1124,140 +1117,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> - <varlistentry id="sql-altertable-split-partition"> - <term><literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])</literal></term> - - <listitem> - <para> - This form splits a single partition of the target table. Hash-partitioning - is not supported. Bounds of new partitions should not overlap with new and - existing partitions (except <replaceable class="parameter">partition_name</replaceable>). - If the split partition is a DEFAULT partition, one of the new partitions must be DEFAULT. - In case one of the new partitions or one of existing partitions is DEFAULT, - new partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable>, ... can have spaces - between partitions bounds. If the partitioned table does not have a DEFAULT - partition, the DEFAULT partition can be defined as one of the new partitions. - </para> - <para> - In case new partitions do not contain a DEFAULT partition and the partitioned table - does not have a DEFAULT partition, the following must be true: sum bounds of - new partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable>, ... should be - equal to bound of split partition <replaceable class="parameter">partition_name</replaceable>. - One of the new partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable>, ... can have - the same name as split partition <replaceable class="parameter">partition_name</replaceable> - (this is suitable in case of splitting a DEFAULT partition: we split it, but after - splitting we have a partition with the same name). - Only simple, non-partitioned partition can be split. - </para> - <para> - The new partitions will be created the same as tables created with the - SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_nameN</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>. - The indexes and identity are created later, after moving the data - into the new partitions. - Extended statistics aren't copied from the parent table, for consistency with - <command>CREATE TABLE PARTITION OF</command>. - - New partitions will have the same table access method as the parent. - If the parent table is persistent then new partitions are created - persistent. If the parent table is temporary then new partitions - are also created temporary. New partitions will also be created in - the same tablespace as the parent. - </para> - <note> - <para> - This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock. - This is a significant limitation, which limits the usage of this - command with large partitioned tables under a high load. - </para> - </note> - </listitem> - </varlistentry> - - <varlistentry id="sql-altertable-merge-partitions"> - <term><literal>MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable></literal></term> - - <listitem> - <para> - This form merges several partitions into the one partition of the target table. - Hash-partitioning is not supported. If DEFAULT partition is not in the - list of partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable> [, ...]: - <itemizedlist> - <listitem> - <para> - For range-partitioned tables it is necessary that the ranges - of the partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable> [, ...] can - be merged into one range without spaces and overlaps (otherwise an error - will be generated). The combined range will be the range for the partition - <replaceable class="parameter">partition_name</replaceable>. - </para> - </listitem> - <listitem> - <para> - For list-partitioned tables the value lists of all partitions - <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable> [, ...] are - combined and form the list of values of partition - <replaceable class="parameter">partition_name</replaceable>. - </para> - </listitem> - </itemizedlist> - If DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>, - <replaceable class="parameter">partition_name2</replaceable> [, ...]: - <itemizedlist> - <listitem> - <para> - The partition <replaceable class="parameter">partition_name</replaceable> - will be the DEFAULT partition. - </para> - </listitem> - <listitem> - <para> - For range- and list-partitioned tables the ranges and lists of values - of the merged partitions can be any. - </para> - </listitem> - </itemizedlist> - The new partition <replaceable class="parameter">partition_name</replaceable> - can have the same name as one of the merged partitions. Only simple, - non-partitioned partitions can be merged. - </para> - <para> - The new partition will be created the same as a table created with the - SQL command <literal>CREATE TABLE <replaceable class="parameter">partition_name</replaceable> (LIKE <replaceable class="parameter">name</replaceable> INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY EXCLUDING STATISTICS)</literal>. - The indexes and identity are created later, after moving the data - into the new partition. - Extended statistics aren't copied from the parent table, for consistency with - <command>CREATE TABLE PARTITION OF</command>. - The new partition will have the same table access method as the parent. - If the parent table is persistent then the new partition is created - persistent. If the parent table is temporary then the new partition - is also created temporary. The new partition will also be created in - the same tablespace as the parent. - </para> - <note> - <para> - This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock. - This is a significant limitation, which limits the usage of this - command with large partitioned tables under a high load. - </para> - </note> - </listitem> - </varlistentry> - </variablelist> </para> <para> All the forms of ALTER TABLE that act on a single table, except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>, - <literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>, - <literal>SPLIT PARTITION</literal>, and <literal>MERGE PARTITIONS</literal> - can be combined into + <literal>ATTACH PARTITION</literal>, and + <literal>DETACH PARTITION</literal> can be combined into a list of multiple alterations to be applied together. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large @@ -1500,8 +1367,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <term><replaceable class="parameter">partition_name</replaceable></term> <listitem> <para> - The name of the table to attach as a new partition or to detach from this table, - or the name of split partition, or the name of the new merged partition. + The name of the table to attach as a new partition or to detach from this table. </para> </listitem> </varlistentry> @@ -1917,31 +1783,6 @@ ALTER TABLE measurement DETACH PARTITION measurement_y2015m12; </programlisting></para> - <para> - To split a single partition of the range-partitioned table: -<programlisting> -ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2023 INTO - (PARTITION sales_feb2023 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'), - PARTITION sales_mar2023 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'), - PARTITION sales_apr2023 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01')); -</programlisting></para> - - <para> - To split a single partition of the list-partitioned table: -<programlisting> -ALTER TABLE sales_list SPLIT PARTITION sales_all INTO - (PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'), - PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'), - PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv')); -</programlisting></para> - - <para> - To merge several partitions into one partition of the target table: -<programlisting> -ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) - INTO sales_all; -</programlisting></para> - </refsect1> <refsect1> |