Duplicate Partition Exception when multiple tables are to be renamed

[pgporada]

This was detected in both staging and production for different tables.

Here's the existing partition structure for prod orderToAuthz2_new_20220419. Note the PARTITION_DESCRIPTION for p_20220522.

SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'orderToAuthz2_new_20220419';

+----------------+------------+---------------------------+
| PARTITION_NAME | TABLE_ROWS | PARTITION_DESCRIPTION     |
+----------------+------------+---------------------------+
| p_20220419     |          0 | 81567449545,99982222560   |
| p_20220519     | 2065212012 | 90007334722,110234961540  |
| p_20220520     |   95070309 | 94841841817,116162938085  |
| p_20220521     |          0 | 99676348912,122090914630  |
| p_20220522     |          0 | 102672012866,127123677707 |
| p_20220523     |          0 | MAXVALUE,MAXVALUE         |
+----------------+------------+---------------------------+

We received an alert for PartManNeeded for this table (orderToAuthz2_new_20220419).

We should be able to run the partman job and have it sort the partitions out itself. However, there's a partman bug and it can't just yet. Two partitions are predicted to be renamed which is fine, but the new p_20220524 partition will have the same PARTITION_DESCRIPTION as p_20220522 above and causes partman to bail out.

partition-manager[3835284]: 2022-05-20 18:55:16,155 - partition - INFO - Evaluating Table orderToAuthz2_new_20220419 (duration=30 days, 0:00:00) (pos={'orderID': 90408556246, 'authzID': 110749398176})
partition-manager[3835284]: 2022-05-20 18:55:16,155 - plan_partition_changes:orderToAuthz2_new_20220419 - INFO - Start-of-fill predicted at 2022-05-22 which is not 2022-05-21. This change will be marked as important to ensure that p_20220521: (99676348912, 122090914630) is moved to 2022-05-22
partition-manager[3835284]: 2022-05-20 18:55:16,155 - plan_partition_changes:orderToAuthz2_new_20220419 - INFO - Start-of-fill predicted at 2022-05-23 which is not 2022-05-22. This change will be marked as important to ensure that p_20220522: (102672012866, 127123677707) is moved to 2022-05-23
partition-manager[3835284]: 2022-05-20 18:55:16,156 - root - WARNING - Couldn't complete command: maintain
partition-manager[3835284]: 2022-05-20 18:55:16,156 - root - WARNING - Traceback (most recent call last):
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 476, in main
partition-manager[3835284]:     output = args.func(args)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 211, in partition_cmd
partition-manager[3835284]:     return do_partition(conf)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 352, in do_partition
partition-manager[3835284]:     composite_sql_command = "\n".join(sql_cmds)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/table_append_partition.py", line 703, in generate_sql_reorganize_partition_commands
partition-manager[3835284]:     raise partitionmanager.types.DuplicatePartitionException(
partition-manager[3835284]: partitionmanager.types.DuplicatePartitionException: Duplicate p_20220524: (102672012866, 127123677707)
partition-manager[3835284]: Traceback (most recent call last):
partition-manager[3835284]:   File "/usr/local/bin/partition-manager", line 11, in <module>
partition-manager[3835284]:     load_entry_point('mariadb-sequential-partition-manager==0.3.0', 'console_scripts', 'partition-manager')()
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 490, in main
partition-manager[3835284]:     raise e
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 476, in main
partition-manager[3835284]:     output = args.func(args)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 211, in partition_cmd
partition-manager[3835284]:     return do_partition(conf)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/cli.py", line 352, in do_partition
partition-manager[3835284]:     composite_sql_command = "\n".join(sql_cmds)
partition-manager[3835284]:   File "/usr/lib/python3/dist-packages/partitionmanager/table_append_partition.py", line 703, in generate_sql_reorganize_partition_commands
partition-manager[3835284]:     raise partitionmanager.types.DuplicatePartitionException(
partition-manager[3835284]: partitionmanager.types.DuplicatePartitionException: Duplicate p_20220524: (102672012866, 127123677707)

The quick option here was to drop the p_20220522 partition and re-run partman. This was a successful operation.

> ALTER TABLE `orderToAuthz2_new_20220419` DROP PARTITION IF EXISTS `p_20220522`;
$ sudo systemctl start partition-manager.service