ApsaraDB RDS:What do I do if I failed to create a partitioned table by using the pg_partman extension?

Problem description

When I call the create_parent interface of the pg_partman extension to create a partitioned table in an ApsaraDB RDS for PostgreSQL database, the following error is reported:

ERROR: duplicate key value violates unique constraint "part_config_parent_table_pkey"

Cause

The pg_partman extension uses a table named part_config to manage partition information. However, the partition information in this table is maintained only when you call the interface of the extension to manage partitions. If a partitioned table is deleted by using the DROP TABLE statement, and not by calling the interface of the extension, then the information about the partitioned table in the part_config table will not be cleaned up. In this case, if you create a partitioned table with the same name as the deleted table, the following error is reported: ERROR: duplicate key value violates unique constraint "part_config_parent_table_pkey".

Reproduction method

You can execute the following SQL statements to reproduce the error:

-- Create a partitioned table
CREATE TABLE part_test(a INT PRIMARY KEY,b INT) PARTITION BY RANGE (a);
SELECT create_parent(p_parent_table=>'public.part_test',p_control=>'a',p_interval=>'10',p_premake=>10);
DROP TABLE part_test;

-- Create a partitioned table with the same name
CREATE TABLE part_test(a INT PRIMARY KEY,b INT) PARTITION BY RANGE (a);
SELECT create_parent(p_parent_table=>'public.part_test',p_control=>'a',p_interval=>'10',p_premake=>10);

Solution

The pg_partman extension provides the undo_partition interface to remove the partitions of a parent table. Before deleting a table, you must call this interface to clean up the table's metadata. For example, before deleting a table named part_test, you can execute the following SQL statements to clean up the table's metadata:

-- Create a non-partitioned table with the same schema
CREATE TABLE part_test_bak(a INT PRIMARY KEY,b INT);
-- Remove the partitions of the partitioned parent table and migrate its data to the non-partitioned table
SELECT undo_partition('public.part_test',p_target_table := 'public.part_test_bak');
-- Delete the parent table
DROP TABLE part_test;

If you use the pg_partman extension to manage paritioned tables, we recommend that you do not execute the DROP TABLE statement to directly delete a partitioned parent table. To update the metadata table immediately after you delete a partitioned table, you can configure event triggers to automatically process and update the metadata table when DDL operations are performed. This ensures data consistency and integrity.

For more information about how to handle this error, visit the pg_partman community.