How to change partition_interval

[tungwaiyip]

I have initially used partman.create_parent to set p_interval to daily. Now I want to change the interval to weekly. I want this to applies to new partition. The old partition I can leave them as is. They will get dropped by retention anyway.

I tried this

UPDATE partman.part_config
SET infinite_time_partitions = true,
    partition_interval = 'weekly',
    premake = 8,
    retention = '8 weeks',
    retention_keep_table=false
WHERE parent_table = 'public.crop_pipeline';

Unfortunately, it result in an error like this

image_collection=> CALL partman.run_maintenance_proc();
ERROR:  invalid input syntax for type interval: "weekly"
CONTEXT: PL/pgSQL function show_partitions(text,text,boolean) line 83 at IF
SQL statement "SELECT partition_tablename                                   FROM partman.show_partitions(v_row.parent_table, p_include_default := true) LIMIT 1"
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 156 at SQL statement
SQL statement "SELECT partman.run_maintenance('public.crop_pipeline', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 402 at RAISE
SQL statement "SELECT partman.run_maintenance('public.crop_pipeline', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE

[keithf4]

The value in the configuration table has to either be an interval or integer type. The value weekly is just a convenience value for the create_parent() function. So in this case you could set it to 1 week or 7 days.

Changing intervals isn't something that's fully accounted for in pg_partman at this time, so it may still not work. As long as you don't change the datetime_string column in the same config table, I think this might work. Weekly partitioning typically has the week number for the partition name, but the naming pattern of the child table is based on that datetime_string value. Daily had the YYYYMMDD pattern and you'll have to keep that for new partition creation to keep working.

[milmazz]

@keithf4 First of all, thanks for this handy extension 👏

While using 1 week or 7 days works for partition_interval. I'm noticing the following gap:

Original partitions

Partitions: activities_p2022_12_01 FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2022-12-02 00:00:00'),
            activities_p2022_12_02 FOR VALUES FROM ('2022-12-02 00:00:00') TO ('2022-12-03 00:00:00'),
            activities_p2022_12_03 FOR VALUES FROM ('2022-12-03 00:00:00') TO ('2022-12-04 00:00:00'),
            activities_p2022_12_04 FOR VALUES FROM ('2022-12-04 00:00:00') TO ('2022-12-05 00:00:00'),
            activities_p2022_12_05 FOR VALUES FROM ('2022-12-05 00:00:00') TO ('2022-12-06 00:00:00'),
            activities_default DEFAULT

After changing the partition_interval value

Updated the record via:

update
  partman.part_config
set
  partition_interval = '7 days', premake = 4, retention = '3 months' 
where
  parent_table in ('public.audit_logs', 'public.activities');

and running call partman.run_maintenance_proc(); locally I get the following:

Partitions: activities_p2022_12_02 FOR VALUES FROM ('2022-12-02 00:00:00') TO ('2022-12-03 00:00:00'),
            activities_p2022_12_03 FOR VALUES FROM ('2022-12-03 00:00:00') TO ('2022-12-04 00:00:00'),
            activities_p2022_12_04 FOR VALUES FROM ('2022-12-04 00:00:00') TO ('2022-12-05 00:00:00'),
            activities_p2022_12_05 FOR VALUES FROM ('2022-12-05 00:00:00') TO ('2022-12-06 00:00:00'),
            activities_p2022_12_12 FOR VALUES FROM ('2022-12-12 00:00:00') TO ('2022-12-19 00:00:00'),
            activities_p2022_12_19 FOR VALUES FROM ('2022-12-19 00:00:00') TO ('2022-12-26 00:00:00'),
            activities_p2022_12_26 FOR VALUES FROM ('2022-12-26 00:00:00') TO ('2023-01-02 00:00:00'),
            activities_p2023_01_02 FOR VALUES FROM ('2023-01-02 00:00:00') TO ('2023-01-09 00:00:00'),
            activities_default DEFAULT

If you see carefully, you will notice a gap between 2022-12-06 00:00:00 to 2022-12-12 00:00:00; this gap will not cause an issue because we have the activities_default table, but I wonder if there is an automatic way to fill the "hole" or the only solution is to manually create and attach a new partition to cover the date range from 2022-12-06 00:00:00 to 2022-12-12 00:00:00?

[keithf4]

As I said, pg_partman is not designed to have mixed intervals at this time, so there is no automatic way to fix this. You will likely have to make that partition manually to fill that gap and you'll likely have to manually move that data out of the default as well since the built in partition functions are not going to know how to find that custom interval child.