Getting error when try to convert regular table to be partitioned online

[yulia-simkin-deel]

Hi,
We use pg_partman of version 5.1.0.
We prepared the script to convert the regular table to be partitioned online.
We based on this doc: https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman_howto.md#partitioning-an-existing-table

During the test we got the following error when we try to copy current data (current month) from the default partition to the specific dedicated partition using command: CALL partman.partition_data_proc('public.inv_logs', p_loop_count := 200). All history data was copied successfully from the default partition using the same procedure.

ERROR: updated partition constraint for default partition "inv_logs_default" would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.inv_logs ATTACH PARTITION public.inv_logs_p20250901 FOR VALUES FROM ('2025-09-01 03:00:00+03') TO ('2025-10-01 03:00:00+03')"
PL/pgSQL function partman.create_partition_time(text,timestamp with time zone[],text) line 207 at EXECUTE
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 243 at assignment
SQL statement "SELECT partman.partition_data_time (p_parent_table := 'public.inv_logs', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)"
PL/pgSQL function partman.partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean) line 86 at EXECUTE
SQL statement "CALL partman.partition_data_proc('public.inv_logs', p_loop_count := 200)"
PL/pgSQL function inline_code_block line 4 at CALL
DETAIL: 
HINT: 
  Where: PL/pgSQL function partman.create_partition_time(text,timestamp with time zone[],text) line 383 at RAISE
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text,text[]) line 243 at assignment
SQL statement "SELECT partman.partition_data_time (p_parent_table := 'public.inv_logs', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)"
PL/pgSQL function partman.partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean) line 86 at EXECUTE
SQL statement "CALL partman.partition_data_proc('public.inv_logs', p_loop_count := 200)"
PL/pgSQL function inline_code_block line 4 at CALL

My assumption is that it fails because during the same time (when partition_data_proc creates temporary table to move current partition data and deletes it from the default, the dedicated partition still doesn't exist so the new inserted data by application service of the current partition is still inserted into the default partition).

In order to solve it, we split the script into two parts. Second part will be executed only after beginning of the next month. Do you have any additional explanation or recommendation for it ? Maybe we implemented the script in the wrong way?

---------------------------------------------------------------
-- The first PR:
----------------------------------------------------------------
DO $$

DECLARE

	l_first_future_partition TEXT;

BEGIN


    SET time zone 'UTC';


	SELECT to_char((date_trunc('month', now()) + INTERVAL '1 month'),'YYYYMMDD') INTO l_first_future_partition;
    
    CREATE TABLE public.inv_logs_backup AS TABLE public.inv_logs;
    
    CREATE TABLE public.inv_logs_partitioned (
        id INT NOT NULL,
        invoice_id integer NOT NULL,
        created_at timestamp with time zone NOT NULL
    ) PARTITION BY RANGE(created_at);

    CREATE INDEX inv_logs_partitioned_invoice_id_idx ON public.inv_logs_partitioned(invoice_id);

    CREATE TABLE public.inv_logs_template (LIKE public.inv_logs);

    ALTER TABLE public.inv_logs_template ADD PRIMARY KEY (id);

    CREATE INDEX inv_logs_template_invoice_id_idx ON public.inv_logs_template(invoice_id);
   
    PERFORM partman.create_parent(
    p_parent_table := 'public.inv_logs_partitioned', 
    p_control := 'created_at', 
    p_interval := '1 month', 
    p_template_table:= 'public.inv_logs_template', 
    p_premake := 1, 
    p_start_partition := l_first_future_partition, 
    p_default_table := false);

    UPDATE partman.part_config 
    SET parent_table = 'public.inv_logs', 
        premake = 3, inherit_privileges = TRUE, 
        infinite_time_partitions = TRUE,
        retention = '12 months',
        retention_keep_table = FALSE,
        retention_keep_index = FALSE 
        WHERE parent_table = 'public.inv_logs_partitioned';


    LOCK TABLE public.inv_logs IN ACCESS EXCLUSIVE MODE;
    LOCK TABLE public.inv_logs_partitioned IN ACCESS EXCLUSIVE MODE;

    ALTER TABLE public.inv_logs RENAME TO inv_logs_default;

    ALTER TABLE public.inv_logs_partitioned RENAME TO inv_logs;
    
    EXECUTE FORMAT('ALTER TABLE public.inv_logs_partitioned_p%1$s RENAME TO inv_logs_p%1$s',l_first_future_partition);

    ALTER TABLE public.inv_logs ALTER COLUMN id SET DEFAULT nextval('public.inv_logs_id_seq'::regclass);

    ALTER TABLE public.inv_logs ATTACH PARTITION public.inv_logs_default DEFAULT;

    ALTER INDEX inv_logs_invoice_id_idx RENAME TO inv_logs_default_id_idx;

    ALTER INDEX public.inv_logs_partitioned_invoice_id_idx RENAME TO inv_logs_invoice_id_idx;
    
    EXECUTE FORMAT('ALTER INDEX public.inv_logs_partitioned_p%1$s_invoice_id_idx RENAME TO inv_logs_p%1$s_invoice_id_idx',l_first_future_partition);
    
    COMMIT;
END; $$

----------------------------------------------------------------------------------------------------------------
--The second PR
--This PR should be executed only after the next month is started (l_first_future_partition)
----------------------------------------------------------------------------------------------------------------

DO $$

BEGIN

    SET time zone 'UTC';

    CALL partman.partition_data_proc('public.inv_logs', p_loop_count := 200);

    PERFORM partman.run_maintenance('public.inv_logs');

END; $$

-----------------------------------------------------------------------------------------------------------------
-- The third PR
-----------------------------------------------------------------------------------------------------------------
VACUUM ANALYZE public.inv_logs;

[jw1u1]

An additional check constraint on the default partition might accelerate the maintenance.

[keithf4]

I think you're probably going about this the best way if you still need to have people using the original table with current data that needs to get partitioned out.

I was thinking the new partition_data_async() function introduced in 5.3 might help here, but I think you'd still run into the same issue since the new child table could not be created until all the data for that month had been moved out. So you'd have to time it exactly right. with the correct smaller interval value, to avoid the new data for that month coming in for that split second. And during that time, the rest of the data for that month would not be visible to the users of the table.

Interesting situation here that I actually hadn't run into yet. I think mostly since the partitioning had been done during off-peak hours so just didn't hit the edge case. I may make mention of it in the How To doc!