Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>

To: Dmitry Koval <d.koval@postgrespro.ru>

Cc: pgsql-hackers@lists.postgresql.org

Date: 2025-07-12T14:01:22Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Adjust errcode in checkPartition()
- d51a5d8e5692 19 (unreleased) landed
Fix usage of palloc() in MERGE/SPLIT PARTITION(s) code
- c5ae07a90a0f 19 (unreleased) landed
Implement ALTER TABLE ... SPLIT PARTITION ... command
- 4b3d173629f4 19 (unreleased) landed
- 87c21bb9412c 17.0 landed
Implement ALTER TABLE ... MERGE PARTITIONS ... command
- f2e4cc427951 19 (unreleased) landed
- 1adf16b8fba4 17.0 landed
Calculate agglevelsup correctly when Aggref contains a CTE.
- b0cc0a71e0a0 19 (unreleased) cited
Use PqMsg_* macros in applyparallelworker.c.
- 989b2e4d5c95 19 (unreleased) cited
Restrict psql meta-commands in plain-text dumps.
- 71ea0d679543 19 (unreleased) cited
Ensure we have a snapshot when updating various system catalogs.
- 706054b11b95 18.0 cited
Use specific collation where needed in new test
- 17bcf4f54504 18.0 cited
Expand virtual generated columns in the planner
- 1e4351af329f 18.0 cited
Virtual generated columns
- 83ea6c54025b 18.0 cited
Define PG_LOGICAL_DIR for path pg_logical/ in data folder
- c39afc38cfec 18.0 cited
Revert support for ALTER TABLE ... MERGE/SPLIT PARTITION(S) commands
- 84f594da3588 17.0 landed
- 3890d90c1508 18.0 landed
Avoid repeated table name lookups in createPartitionTable()
- f636ab41aba2 17.0 landed
- 04158e7fa37c 18.0 landed
Provide deterministic order for catalog queries in partition_split.sql
- d53a4286d772 17.0 landed
Don't copy extended statistics during MERGE/SPLIT partition operations
- fbd4321fd5b4 17.0 landed
Fix the name collision detection in MERGE/SPLIT partition operations
- 3a82c689fd1b 17.0 landed
Fix regression tests conflict in 3ca43dbbb6
- 2a679ae94e46 17.0 landed
Add permission check for MERGE/SPLIT partition operations
- 3ca43dbbb67f 17.0 landed
Fix one more portability shortcoming in new test_pg_dump test.
- d12b4ba1bd3e 17.0 cited
Inherit parent's AM for partition MERGE/SPLIT operations
- 259c96fa8f78 17.0 landed
Add tab completion for partition MERGE/SPLIT operations
- 60ae37a8bc02 17.0 landed
Rename tables in tests of partition MERGE/SPLIT operations
- f4fc7cb54b6a 17.0 landed
Make new partitions with parent's persistence during MERGE/SPLIT
- fcf80c5d5f0f 17.0 landed
Document the way partition MERGE/SPLIT operations create new partitions
- 842c9b27057e 17.0 landed
Change the way ATExecMergePartitions() handles the name collision
- 885742b9f88b 17.0 landed
Grammar fixes for split/merge partitions code
- 9dfcac8e15ac 17.0 landed
Checks for ALTER TABLE ... SPLIT/MERGE PARTITIONS ... commands
- c99ef1811a06 17.0 landed
Fix some grammer errors from error messages and codes comments
- df64c81ca9cb 17.0 landed
Support TZ and OF format codes in to_timestamp().
- 8ba6fdf905d0 17.0 cited
Support identity columns in partitioned tables
- 699586315704 17.0 cited
Fix indentation in twophase.c
- 4e465aac36ce 17.0 cited
Fix corner-case planner failure for MERGE.
- 326a33a289c7 16.0 cited
Doc: fix documentation example for bytea hex output format.
- 4f46f870fa56 16.0 cited
Avoid repeated name lookups during table and index DDL.
- 5f173040e324 9.4.0 cited

On Wed, Jun 25, 2025 at 5:28 AM Dmitry Koval <d.koval@postgrespro.ru> wrote:
>
> Hi!
> Thanks for notes!
>
hi.

+static void
+transformPartitionCmdForMerge(CreateStmtContext *cxt, PartitionCmd *partcmd)
+{
+
+ /* Is current partition a DEFAULT partition? */
+ defaultPartOid =
get_default_oid_from_partdesc(RelationGetPartitionDesc(parent, true));
+
this comment seems wrong?
it should be "does this partitioned table (parent) have a default partition?


+ case AT_MergePartitions:
+ {
+ PartitionCmd *partcmd = (PartitionCmd *) cmd->def;
+
+ if (list_length(partcmd->partlist) < 2)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("list of new partitions should contain at least two items"));
This error message is not good, IMHO. I don't really have any good ideas though.
if we polish this message later, now we can add a comment: "FIXME
improve this error message".


in ATExecMergePartitions we have:
    RangeVarGetAndCheckCreationNamespace(cmd->name, NoLock, &existingRelid);
it will call RangeVarAdjustRelationPersistence
after it, the cmd->name->relpersistence will be set properly.

so the following in createPartitionTable can be checked way earlier.
    /*
     * We intended to create the partition with the same persistence as the
     * parent table, but we still need to recheck because that might be
     * affected by the search_path.  If the parent is permanent, so must be
     * all of its partitions.
     */
    if (parent_rel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
        newRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
        ereport(ERROR,
                errcode(ERRCODE_WRONG_OBJECT_TYPE),
                errmsg("cannot create a temporary relation as
partition of permanent relation \"%s\"",
                       RelationGetRelationName(parent_rel)));
    /* Permanent rels cannot be partitions belonging to temporary parent */
    if (newRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
        parent_rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
        ereport(ERROR,
                errcode(ERRCODE_WRONG_OBJECT_TYPE),
                errmsg("cannot create a permanent relation as
partition of temporary relation \"%s\"",
                       RelationGetRelationName(parent_rel)));

after createPartitionTable->heap_create_with_catalog do the error check seems
unintuitive to me.
it can be checked right after RangeVarGetAndCheckCreationNamespace.