v29-0003-Documentation-for-ALTER-TABLE-SPLIT-PARTITION-ME.patch

text/plain
Filename: v29-0003-Documentation-for-ALTER-TABLE-SPLIT-PARTITION-ME.patch
Type: text/plain
Part: 2
Message: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: format-patch
Series: patch v29-0003
Subject: Documentation for ALTER TABLE SPLIT PARTITION/MERGE PARTITIONS commands
File	+	−
doc/src/sgml/ref/alter_table.sgml	135	3
From 0164d86e4d62cb35f514c1c32ff1659aedfa42c3 Mon Sep 17 00:00:00 2001
From: Koval Dmitry <d.koval@postgrespro.ru>
Date: Tue, 28 Mar 2023 10:50:19 +0300
Subject: [PATCH v29 3/3] Documentation for ALTER TABLE SPLIT PARTITION/MERGE
 PARTITIONS commands

---
 doc/src/sgml/ref/alter_table.sgml | 138 +++++++++++++++++++++++++++++-
 1 file changed, 135 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 6cdcd779ef..945228d84b 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -37,6 +37,13 @@ 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>
 
@@ -1118,14 +1125,113 @@ 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>
+     <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 merge several partitions into 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>,
+   <literal>SPLIT 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 +1474,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 split partition, or the name of the new merged partition.
        </para>
       </listitem>
      </varlistentry>
@@ -1784,6 +1891,31 @@ 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 ('Voronezh', 'Smolensk', 'Bryansk'),
+    PARTITION sales_east FOR VALUES IN ('Magadan', 'Khabarovsk', 'Vladivostok'),
+    PARTITION sales_central FOR VALUES IN ('Moscow', 'Kazan', 'Volgograd'));
+</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>
-- 
2.40.1.windows.1