diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 19 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 77 |
2 files changed, 93 insertions, 3 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 6bc43ad10ca..f9f2778b3d7 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4381,6 +4381,25 @@ 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 + administrators 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> </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 6cdcd779ef8..e0a98bc5385 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -37,6 +37,9 @@ 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> + 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> @@ -1118,14 +1121,74 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </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 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 values lists of all partitions + <replaceable class="parameter">partition_name1</replaceable>, + <replaceable class="parameter">partition_name2</replaceable> [, ...] are + combined and form a 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> + <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>, and - <literal>DETACH PARTITION</literal> can be combined into + <literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>, + and <literal>MERGE PARTITIONS</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 @@ -1368,7 +1431,8 @@ 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. + The name of the table to attach as a new partition or to detach from this table, + or the name of the new merged partition. </para> </listitem> </varlistentry> @@ -1784,6 +1848,13 @@ ALTER TABLE measurement DETACH PARTITION measurement_y2015m12; </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> |