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
