🌐 AI搜索 & 代理 主页
Skip to content

Commit f2e4cc4

Browse files
akorotkovtenderwgguofengrichardilmariMasaoFujii
committed
Implement ALTER TABLE ... MERGE PARTITIONS ... command
This new DDL command merges several partitions into a single partition of the target table. The target partition is created using the new createPartitionTable() function with the parent partition as the template. This commit comprises a quite naive implementation which works in a single process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all the operations, including the tuple routing. This is why this new DDL command can't be recommended for large partitioned tables under a high load. However, this implementation comes in handy in certain cases, even as it is. Also, it could serve as a foundation for future implementations with less locking and possibly parallelism. Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru Author: Dmitry Koval <d.koval@postgrespro.ru> Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com> Co-authored-by: Tender Wang <tndrwang@gmail.com> Co-authored-by: Richard Guo <guofenglinux@gmail.com> Co-authored-by: Dagfinn Ilmari Mannsaker <ilmari@ilmari.org> Co-authored-by: Fujii Masao <masao.fujii@gmail.com> Co-authored-by: Jian He <jian.universality@gmail.com> Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Zhihong Yu <zyu@yugabyte.com> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Robert Haas <rhaas@postgresql.org> Reviewed-by: Stephane Tachoires <stephane.tachoires@gmail.com> Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com> Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Reviewed-by: Daniel Gustafsson <dgustafsson@postgresql.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Noah Misch <noah@leadboat.com>
1 parent 5b3ef30 commit f2e4cc4

File tree

22 files changed

+3668
-32
lines changed

22 files changed

+3668
-32
lines changed

‎doc/src/sgml/ddl.sgml‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4743,6 +4743,25 @@ ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
47434743
ALTER INDEX measurement_city_id_logdate_key
47444744
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
47454745
...
4746+
</programlisting>
4747+
</para>
4748+
4749+
<para>
4750+
There is also an option for merging multiple table partitions into
4751+
a single partition using the
4752+
<link linkend="sql-altertable-merge-partitions"><command>ALTER TABLE ... MERGE PARTITIONS</command></link>.
4753+
This feature simplifies the management of partitioned tables by allowing
4754+
users to combine partitions that are no longer needed as
4755+
separate entities. It's important to note that this operation is not
4756+
supported for hash-partitioned tables and acquires an
4757+
<literal>ACCESS EXCLUSIVE</literal> lock, which could impact high-load
4758+
systems due to the lock's restrictive nature. For example, we can
4759+
merge three monthly partitions into one quarter partition:
4760+
<programlisting>
4761+
ALTER TABLE measurement
4762+
MERGE PARTITIONS (measurement_y2006m01,
4763+
measurement_y2006m02,
4764+
measurement_y2006m03) INTO measurement_y2006q1;
47464765
</programlisting>
47474766
</para>
47484767
</sect3>

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

Lines changed: 123 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3737
ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
3838
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3939
DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]
40+
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
41+
MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable>
4042

4143
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
4244

@@ -1157,18 +1159,117 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11571159
</listitem>
11581160
</varlistentry>
11591161

1162+
<varlistentry id="sql-altertable-merge-partitions">
1163+
<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>
1164+
1165+
<listitem>
1166+
<para>
1167+
This form merges several partitions of the target table into a new partition.
1168+
Hash-partitioned target table is not supported.
1169+
Only simple, non-partitioned partitions can be merged.
1170+
The new partition (<replaceable class="parameter">partition_name</replaceable>)
1171+
can have the same name as one of the merged partitions
1172+
(<literal><replaceable class="parameter">partition_name1</replaceable>,
1173+
<replaceable class="parameter">partition_name2</replaceable> [, ...]</literal>).
1174+
</para>
1175+
1176+
<para>
1177+
If the <literal>DEFAULT</literal> partition is not in the
1178+
list of merged partitions:
1179+
<itemizedlist>
1180+
<listitem>
1181+
<para>
1182+
For range-partitioned tables, the ranges of merged partitions
1183+
must be adjacent in order to be merged.
1184+
The partition bounds of merged partitions are combined to form the new partition bound for
1185+
<replaceable class="parameter">partition_name</replaceable>.
1186+
</para>
1187+
</listitem>
1188+
<listitem>
1189+
<para>
1190+
For list-partitioned tables, the partition bounds of
1191+
merged partitions are combined to form the new partition bound for
1192+
<replaceable class="parameter">partition_name</replaceable>.
1193+
</para>
1194+
</listitem>
1195+
</itemizedlist>
1196+
If the <literal>DEFAULT</literal> partition is in the list of merged partitions:
1197+
<itemizedlist>
1198+
<listitem>
1199+
<para>
1200+
The partition <replaceable class="parameter">partition_name</replaceable>
1201+
will be the new <literal>DEFAULT</literal> partition of the target table.
1202+
</para>
1203+
</listitem>
1204+
<listitem>
1205+
<para>
1206+
The partition bound specifications for merged partitions can be arbitrary.
1207+
</para>
1208+
</listitem>
1209+
</itemizedlist>
1210+
</para>
1211+
<para>
1212+
All merged partitions must have the same owner.
1213+
The owner of merged partitions will be the owner of the new partition.
1214+
It is the user's responsibility to setup <acronym>ACL</acronym> on
1215+
the new partition.
1216+
</para>
1217+
1218+
<para>
1219+
<command>ALTER TABLE MERGE PARTITION</command> uses the partitioned
1220+
table itself as the template to construct the new partition.
1221+
The new partition will inherit the same table access method, persistence
1222+
type, and tablespace as the partitioned table.
1223+
1224+
Constraints, column defaults, column generation expressions, identity
1225+
columns, indexes, and triggers are copied from the partitioned table to
1226+
the new partition. But extended statistics, security policies, etc,
1227+
won't be copied from the partitioned table.
1228+
Indexes and identity columns copied from the partitioned table will be
1229+
created afterward, once the data has been moved into the new partition.
1230+
</para>
1231+
1232+
<para>
1233+
When partitions are merged, any objects depending on this partition,
1234+
such as constraints, triggers, extended statistics, etc, will be
1235+
dropped.
1236+
Eventually, we will drop all the merged partitions
1237+
(using <literal>RESTRICT</literal> mode) too; therefore, if any objects
1238+
are still dependent on them,
1239+
<command>ALTER TABLE MERGE PARTITION</command> would fail.
1240+
(see <xref linkend="ddl-depend"/>).
1241+
</para>
1242+
1243+
<note>
1244+
<para>
1245+
Merging partitions acquires an <literal>ACCESS EXCLUSIVE</literal> lock on
1246+
the parent table, in addition to the <literal>ACCESS EXCLUSIVE</literal>
1247+
locks on the tables being merged and on the default partition (if any).
1248+
</para>
1249+
</note>
1250+
<note>
1251+
<para>
1252+
<command>ALTER TABLE MERGE PARTITIONS</command> creates a new partition and
1253+
moves data from all merging partitions into it, which can take a long time.
1254+
So it is not recommended to use the command to merge very big partitions
1255+
with small ones.
1256+
</para>
1257+
</note>
1258+
</listitem>
1259+
</varlistentry>
1260+
11601261
</variablelist>
11611262
</para>
11621263

11631264
<para>
11641265
All the forms of <command>ALTER TABLE</command> that act on a single table,
11651266
except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>,
1166-
<literal>ATTACH PARTITION</literal>, and
1167-
<literal>DETACH PARTITION</literal> can be combined into
1267+
<literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>,
1268+
and <literal>MERGE PARTITIONS</literal>, can be combined into
11681269
a list of multiple alterations to be applied together. For example, it
11691270
is possible to add several columns and/or alter the type of several
11701271
columns in a single command. This is particularly useful with large
1171-
tables, since only one pass over the table need be made.
1272+
tables, since only one pass over the table needs to be made.
11721273
</para>
11731274

11741275
<para>
@@ -1407,7 +1508,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14071508
<term><replaceable class="parameter">partition_name</replaceable></term>
14081509
<listitem>
14091510
<para>
1410-
The name of the table to attach as a new partition or to detach from this table.
1511+
The name of the table to attach as a new partition or to detach from this table,
1512+
or the name of the new merged partition.
1513+
</para>
1514+
</listitem>
1515+
</varlistentry>
1516+
1517+
<varlistentry id="sql-altertable-parms-partition-name1">
1518+
<term><replaceable class="parameter">partition_name1</replaceable></term>
1519+
<term><replaceable class="parameter">partition_name2</replaceable></term>
1520+
<listitem>
1521+
<para>
1522+
The names of the tables being merged into the new partition.
14111523
</para>
14121524
</listitem>
14131525
</varlistentry>
@@ -1840,6 +1952,13 @@ ALTER TABLE measurement
18401952
DETACH PARTITION measurement_y2015m12;
18411953
</programlisting></para>
18421954

1955+
<para>
1956+
To merge several partitions into one partition of the target table:
1957+
<programlisting>
1958+
ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central)
1959+
INTO sales_all;
1960+
</programlisting></para>
1961+
18431962
</refsect1>
18441963

18451964
<refsect1>

‎src/backend/catalog/dependency.c‎

Lines changed: 52 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -321,13 +321,63 @@ performDeletion(const ObjectAddress *object,
321321
}
322322

323323
/*
324-
* performMultipleDeletions: Similar to performDeletion, but act on multiple
324+
* performDeletionCheck: Check whether a specific object can be safely deleted.
325+
* This function does not perform any deletion; instead, it raises an error
326+
* if the object cannot be deleted due to existing dependencies.
327+
*
328+
* It can be useful when you need to delete some objects later. See comments
329+
* in performDeletion too.
330+
* The behavior must be specified as DROP_RESTRICT.
331+
*/
332+
void
333+
performDeletionCheck(const ObjectAddress *object,
334+
DropBehavior behavior, int flags)
335+
{
336+
Relation depRel;
337+
ObjectAddresses *targetObjects;
338+
339+
Assert(behavior == DROP_RESTRICT);
340+
341+
depRel = table_open(DependRelationId, RowExclusiveLock);
342+
343+
AcquireDeletionLock(object, 0);
344+
345+
/*
346+
* Construct a list of objects we want to delete later (ie, the given
347+
* object plus everything directly or indirectly dependent on it).
348+
*/
349+
targetObjects = new_object_addresses();
350+
351+
findDependentObjects(object,
352+
DEPFLAG_ORIGINAL,
353+
flags,
354+
NULL, /* empty stack */
355+
targetObjects,
356+
NULL, /* no pendingObjects */
357+
&depRel);
358+
359+
/*
360+
* Check if deletion is allowed.
361+
*/
362+
reportDependentObjects(targetObjects,
363+
behavior,
364+
flags,
365+
object);
366+
367+
/* And clean up */
368+
free_object_addresses(targetObjects);
369+
370+
table_close(depRel, RowExclusiveLock);
371+
}
372+
373+
/*
374+
* performMultipleDeletions: Similar to performDeletion, but acts on multiple
325375
* objects at once.
326376
*
327377
* The main difference from issuing multiple performDeletion calls is that the
328378
* list of objects that would be implicitly dropped, for each object to be
329379
* dropped, is the union of the implicit-object list for all objects. This
330-
* makes each check be more relaxed.
380+
* makes each check more relaxed.
331381
*/
332382
void
333383
performMultipleDeletions(const ObjectAddresses *objects,

‎src/backend/catalog/pg_constraint.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -875,7 +875,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
875875
false)));
876876
constr->is_enforced = true;
877877
constr->skip_validation = !conForm->convalidated;
878-
constr->initially_valid = true;
878+
constr->initially_valid = conForm->convalidated;
879879
constr->is_no_inherit = conForm->connoinherit;
880880
notnulls = lappend(notnulls, constr);
881881
}

0 commit comments

Comments
 (0)