From f2e4cc427951b7c46629fb7625a22f7898586f3a Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Sun, 14 Dec 2025 13:29:17 +0200 Subject: 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 Co-authored-by: Alexander Korotkov Co-authored-by: Tender Wang Co-authored-by: Richard Guo Co-authored-by: Dagfinn Ilmari Mannsaker Co-authored-by: Fujii Masao Co-authored-by: Jian He Reviewed-by: Matthias van de Meent Reviewed-by: Laurenz Albe Reviewed-by: Zhihong Yu Reviewed-by: Justin Pryzby Reviewed-by: Alvaro Herrera Reviewed-by: Robert Haas Reviewed-by: Stephane Tachoires Reviewed-by: Jian He Reviewed-by: Alexander Korotkov Reviewed-by: Pavel Borisov Reviewed-by: Masahiko Sawada Reviewed-by: Alexander Lakhin Reviewed-by: Kyotaro Horiguchi Reviewed-by: Daniel Gustafsson Reviewed-by: Tom Lane Reviewed-by: Noah Misch --- src/backend/parser/gram.y | 22 +++++- src/backend/parser/parse_utilcmd.c | 152 ++++++++++++++++++++++++++++++++++++- 2 files changed, 170 insertions(+), 4 deletions(-) (limited to 'src/backend/parser') diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7856ce9d78f..562f11d6677 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -762,7 +762,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH + PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH PERIOD PLACING PLAN PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -2395,6 +2395,7 @@ partition_cmd: n->subtype = AT_AttachPartition; cmd->name = $3; cmd->bound = $4; + cmd->partlist = NIL; cmd->concurrent = false; n->def = (Node *) cmd; @@ -2409,6 +2410,7 @@ partition_cmd: n->subtype = AT_DetachPartition; cmd->name = $3; cmd->bound = NULL; + cmd->partlist = NIL; cmd->concurrent = $4; n->def = (Node *) cmd; @@ -2422,6 +2424,21 @@ partition_cmd: n->subtype = AT_DetachPartitionFinalize; cmd->name = $3; cmd->bound = NULL; + cmd->partlist = NIL; + cmd->concurrent = false; + n->def = (Node *) cmd; + $$ = (Node *) n; + } + /* ALTER TABLE MERGE PARTITIONS () INTO */ + | MERGE PARTITIONS '(' qualified_name_list ')' INTO qualified_name + { + AlterTableCmd *n = makeNode(AlterTableCmd); + PartitionCmd *cmd = makeNode(PartitionCmd); + + n->subtype = AT_MergePartitions; + cmd->name = $7; + cmd->bound = NULL; + cmd->partlist = $4; cmd->concurrent = false; n->def = (Node *) cmd; $$ = (Node *) n; @@ -2438,6 +2455,7 @@ index_partition_cmd: n->subtype = AT_AttachPartition; cmd->name = $3; cmd->bound = NULL; + cmd->partlist = NIL; cmd->concurrent = false; n->def = (Node *) cmd; @@ -18038,6 +18056,7 @@ unreserved_keyword: | PARSER | PARTIAL | PARTITION + | PARTITIONS | PASSING | PASSWORD | PATH @@ -18668,6 +18687,7 @@ bare_label_keyword: | PARSER | PARTIAL | PARTITION + | PARTITIONS | PASSING | PASSWORD | PATH diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index e96b38a59d5..bf02e38785d 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -32,6 +32,7 @@ #include "catalog/heap.h" #include "catalog/index.h" #include "catalog/namespace.h" +#include "catalog/partition.h" #include "catalog/pg_am.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" @@ -58,6 +59,8 @@ #include "parser/parse_type.h" #include "parser/parse_utilcmd.h" #include "parser/parser.h" +#include "partitioning/partbounds.h" +#include "partitioning/partdesc.h" #include "rewrite/rewriteManip.h" #include "utils/acl.h" #include "utils/builtins.h" @@ -3509,6 +3512,135 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString, } +/* + * checkPartition + * Check whether partRelOid is a leaf partition of the parent table (rel). + */ +static void +checkPartition(Relation rel, Oid partRelOid) +{ + Relation partRel; + + partRel = table_open(partRelOid, NoLock); + + if (partRel->rd_rel->relkind != RELKIND_RELATION) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table", RelationGetRelationName(partRel)), + errhint("ALTER TABLE ... MERGE PARTITIONS can only merge partitions don't have sub-partitions")); + + if (!partRel->rd_rel->relispartition) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a partition of partitioned table \"%s\"", + RelationGetRelationName(partRel), RelationGetRelationName(rel)), + errhint("ALTER TABLE ... MERGE PARTITIONS can only merge partitions don't have sub-partitions")); + + if (get_partition_parent(partRelOid, false) != RelationGetRelid(rel)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_TABLE), + errmsg("relation \"%s\" is not a partition of relation \"%s\"", + RelationGetRelationName(partRel), RelationGetRelationName(rel)), + errhint("ALTER TABLE ... MERGE PARTITIONS can only merge partitions don't have sub-partitions")); + + table_close(partRel, NoLock); +} + +/* + * transformPartitionCmdForMerge - + * analyze the ALTER TABLE ... MERGE PARTITIONS command + * + * Does simple checks for merged partitions. Calculates bound of the resulting + * partition. + */ +static void +transformPartitionCmdForMerge(CreateStmtContext *cxt, PartitionCmd *partcmd) +{ + Oid defaultPartOid; + Oid partOid; + Relation parent = cxt->rel; + PartitionKey key; + char strategy; + ListCell *listptr, + *listptr2; + bool isDefaultPart = false; + List *partOids = NIL; + + key = RelationGetPartitionKey(parent); + strategy = get_partition_strategy(key); + + if (strategy == PARTITION_STRATEGY_HASH) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("partition of hash-partitioned table cannot be merged")); + + /* Does the partitioned table (parent) have a default partition? */ + defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(parent, true)); + + foreach(listptr, partcmd->partlist) + { + RangeVar *name = (RangeVar *) lfirst(listptr); + + /* Partitions in the list should have different names. */ + for_each_cell(listptr2, partcmd->partlist, lnext(partcmd->partlist, listptr)) + { + RangeVar *name2 = (RangeVar *) lfirst(listptr2); + + if (equal(name, name2)) + ereport(ERROR, + errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("partition with name \"%s\" is already used", name->relname), + parser_errposition(cxt->pstate, name2->location)); + } + + /* + * Search the DEFAULT partition in the list. Open and lock partitions + * before calculating the boundary for resulting partition, we also + * check for ownership along the way. We need to use + * AccessExclusiveLock here, because these merged partitions will be + * detached and then dropped in ATExecMergePartitions. + */ + partOid = RangeVarGetRelidExtended(name, AccessExclusiveLock, 0, + RangeVarCallbackOwnsRelation, + NULL); + /* Is the current partition a DEFAULT partition? */ + if (partOid == defaultPartOid) + isDefaultPart = true; + + /* + * Extended check because the same partition can have different names + * (for example, "part_name" and "public.part_name"). + */ + foreach(listptr2, partOids) + { + Oid curOid = lfirst_oid(listptr2); + + if (curOid == partOid) + ereport(ERROR, + errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("partition with name \"%s\" is already used", name->relname), + parser_errposition(cxt->pstate, name->location)); + } + + checkPartition(parent, partOid); + + partOids = lappend_oid(partOids, partOid); + } + + /* Allocate the bound of the resulting partition. */ + Assert(partcmd->bound == NULL); + partcmd->bound = makeNode(PartitionBoundSpec); + + /* Fill the partition bound. */ + partcmd->bound->strategy = strategy; + partcmd->bound->location = -1; + partcmd->bound->is_default = isDefaultPart; + if (!isDefaultPart) + calculate_partition_bound_for_merge(parent, partcmd->partlist, + partOids, partcmd->bound, + cxt->pstate); +} + /* * transformAlterTableStmt - * parse analysis for ALTER TABLE @@ -3786,12 +3918,26 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, newcmds = lappend(newcmds, cmd); break; + case AT_MergePartitions: + { + PartitionCmd *partcmd = (PartitionCmd *) cmd->def; + + if (list_length(partcmd->partlist) < 2) + ereport(ERROR, + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("list of partitions to be merged should include at least two partitions")); + + transformPartitionCmdForMerge(&cxt, partcmd); + newcmds = lappend(newcmds, cmd); + break; + } + default: /* - * Currently, we shouldn't actually get here for subcommand - * types that don't require transformation; but if we do, just - * emit them unchanged. + * Currently, we shouldn't actually get here for the + * subcommand types that don't require transformation; but if + * we do, just emit them unchanged. */ newcmds = lappend(newcmds, cmd); break; -- cgit v1.2.3