Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Hi, Jian He!

Thanks for the suggestions and patches!
This email contains comments to three emails (05/06/2025).
I hope to read two emails (for 06/06/2025) tomorrow.

1.
 >What should we do when any to be merged partition has constraints?
 >...
 >Maybe this is expected, but we need to mention it somewhere and have
 >some tests on it saying that MERGE PARTITIONS will effectively drop
 >the partitions, so if any object depends on that partition
 >then MERGE PARTITIONS can not be done.

Added following phrases to the documentation (I hope this should be 
enough?):

If merged partitions have individual constraints, those constraints will 
be dropped because command uses partitioned table as a model to create 
the constraints.
If merged partitions have some objects dependent on them, the command 
can not be done (CASCADE is not used, an error will be returned).


2.
 > ... so this error check can be performed as early as the
 >transformPartitionCmdForMerge stage?

Function createPartitionTable will be used for various other cases 
besides MERGE PARTITIONS: for SPLIT PARTITION, for PARTITION BY 
REFERENCE (I hope).
So I think it's better to minimize the amount of code and not move the 
same one check into different functions (transformPartitionCmdForMerge, 
transformPartitionCmdForSplit, ...).


3.
 >i think, we can do the following way:
 >if (modelRel->rd_rel->relam)
 >  elog(ERROR, "error");
 >relamId = modelRel->rd_rel->relam;

Can you clarify what is reason to change the current AM-logic for 
creating a new partition?

+    /* Look up the access method for new relation. */
+    relamId = (modelRel->rd_rel->relam != InvalidOid) ? 
modelRel->rd_rel->relam : HEAP_TABLE_AM_OID;

(If AM is set for a partitioned table, then use it, otherwise use AM for 
heap tables.)


4.
 > Attached is some refactoring in moveMergedTablesRows, hope it's 
straightforward.

Thanks, these changes are useful.


5.
 >bug in transformPartitionCmdForMerge "equal(name, name2))"
 > ...
 >ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022,
 >public.sales_feb2022) INTO sales_feb_mar2022;
 >ERROR:  lower bound of partition "sales_feb2022" conflicts with upper
 >bound of previous partition "sales_feb2022"
 >in this context. "sales_feb2022" is the same as "public.sales_feb2022".

Added check and test for this case.


6.
 >When using ALTER TABLE ... MERGE PARTITIONS, some of the new
 >partition's properties will not be inherited from to be merged
 >partitions; instead, they will be directly copied from the root
 >partitioned table.
 >So we need to test this behavior.
 >The attached test file is for test table properties:
 >(COMMENTS, COMPRESSION, DEFAULTS, GENERATED, STATISTICS, STORAGE).

Some tests already exist (GENERATED, DEFAULTS) - see 
partition_merge.sql, lines after:

+-- Test for:
+--   * composite partition key;
+--   * GENERATED column;
+--   * column with DEFAULT value.
...

But the complex test is probably also interesting.
Test added.

--

Similar changes are made for the second commit (SPLIT PARTITION).

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com