Horton

Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Dmitry Koval <d.koval@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Date: 2025-06-11T07:28:36Z
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 →

  1. Adjust errcode in checkPartition()

  2. Fix usage of palloc() in MERGE/SPLIT PARTITION(s) code

  3. Implement ALTER TABLE ... SPLIT PARTITION ... command

  4. Implement ALTER TABLE ... MERGE PARTITIONS ... command

  5. Calculate agglevelsup correctly when Aggref contains a CTE.

  6. Use PqMsg_* macros in applyparallelworker.c.

  7. Restrict psql meta-commands in plain-text dumps.

  8. Ensure we have a snapshot when updating various system catalogs.

  9. Use specific collation where needed in new test

  10. Expand virtual generated columns in the planner

  11. Virtual generated columns

  12. Define PG_LOGICAL_DIR for path pg_logical/ in data folder

  13. Revert support for ALTER TABLE ... MERGE/SPLIT PARTITION(S) commands

  14. Avoid repeated table name lookups in createPartitionTable()

  15. Provide deterministic order for catalog queries in partition_split.sql

  16. Don't copy extended statistics during MERGE/SPLIT partition operations

  17. Fix the name collision detection in MERGE/SPLIT partition operations

  18. Fix regression tests conflict in 3ca43dbbb6

  19. Add permission check for MERGE/SPLIT partition operations

  20. Fix one more portability shortcoming in new test_pg_dump test.

  21. Inherit parent's AM for partition MERGE/SPLIT operations

  22. Add tab completion for partition MERGE/SPLIT operations

  23. Rename tables in tests of partition MERGE/SPLIT operations

  24. Make new partitions with parent's persistence during MERGE/SPLIT

  25. Document the way partition MERGE/SPLIT operations create new partitions

  26. Change the way ATExecMergePartitions() handles the name collision

  27. Grammar fixes for split/merge partitions code

  28. Checks for ALTER TABLE ... SPLIT/MERGE PARTITIONS ... commands

  29. Fix some grammer errors from error messages and codes comments

  30. Support TZ and OF format codes in to_timestamp().

  31. Support identity columns in partitioned tables

  32. Fix indentation in twophase.c

  33. Fix corner-case planner failure for MERGE.

  34. Doc: fix documentation example for bytea hex output format.

  35. Avoid repeated name lookups during table and index DDL.

On Wed, Jun 11, 2025 at 8:06 AM Dmitry Koval <d.koval@postgrespro.ru> wrote:
>
>  >Do getAttributesList need to care about pg_attribute.attidentity?
>  >currently MERGE PARTITION seems to work fine with identity columns,
>  >this issue i didn't dig deeper.
>
> Probably after commit [3] partition's identity columns shares the
> identity space (i.e. underlying sequence) as the corresponding
> columns of the partitioned table. So call BuildDescForRelation in
> createPartitionTable function should copy pg_attribute.attidentity
> for new partition.
>
but BuildDescForRelation is based on getAttributesList,
in getAttributesList, assign pg_attribute.attidentity to def->identity
should be safe, IMHO.

+     <para>
+      If merged partitions have different owners, an error will be generated.
+      The owner of the merged partitions will be the owner of the new
partition.
+     </para>
+     <para>
+      It is the user's responsibility to setup <acronym>ACL</acronym> on the
+      new partition.
+     </para>
since they <para> are related, these two can be one <para>?


+     <para>
+      If merged partitions have individual constraints, those constraints will
+      be dropped because command uses partitioned table as a model to create
+      the constraints.
+     </para>

I feel like it's not fully accurate, the following is what I can come up with:
+     <para>
+ When partitions are merged, any individual objects belonging to those
+ partitions, such as constraints or statistics will be dropped. This occurs
+ because ALTER TABLE MERGE PARTITIONS uses the partitioned table itself as the
+ template to define these objects.
+     </para>


>
>  >I am wondering right after createPartitionTable,
>  >do we need a CommandCounterIncrement?
>  >because later moveMergedTablesRows will use the output of
>  >createPartitionTable.
>
> We call CommandCounterIncrement in createPartitionTable function right
> after heap_create_with_catalog (same code in create_toast_table,
> make_new_heap, DefineRelation functions). We need an additional
> CommandCounterIncrement call in case we use objects created after this
> point. But we probably don't use these objects (in function
> moveMergedTablesRows too).
>
As mentioned in the previous thread [1], moveMergedTablesRows need
latest relcache entry for newPartRel. so I guess, put one
CommandCounterIncrement at the end of createPartitionTable
should be fine, which I already did in [1].
[1]: https://postgr.es/m/CACJufxH3mfNYfHy9+dCUZPhOsmVRtJUJbWU1vH248Lg0eZjhzQ@mail.gmail.com

> 3.
>  >I only want to allow HEAP_TABLE_AM_OID to be used
>  >in the merge partition,
>  >I guess that would avoid unintended consequences.
>
> Thanks for the clarification. Isn't this limitation too strong?
> It is very likely that the user will create an AM based on
> HEAP_TABLE_AM_OID, in which case the code should work.
>
ok.


if you looking at ATExecDetachPartition, we have:
    /*
     * Detaching the partition might involve TOAST table access, so ensure we
     * have a valid snapshot.
     */
    PushActiveSnapshot(GetTransactionSnapshot());
    /* Do the final part of detaching */
    DetachPartitionFinalize(rel, partRel, concurrent, defaultPartOid);
    PopActiveSnapshot();

do we need do the same to the following DetachPartitionFinalize:
    foreach_ptr(RelationData, mergingPartition, mergingPartitionsList)
    {
        /* Remove the pg_inherits row first. */
        RemoveInheritance(mergingPartition, rel, false);
        /* Do the final part of detaching. */
        DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
    }