Missing partitions after running partition_data_proc() then run_maintenance()

[jeanloesch]

Good afternoon,

I have a daily partitionned table. When I insert a row with a date which doesn't fit in any existing partition it goes to the default partition.
Now I run the function partman.partition_data_proc to create the missing partition and move the line from the default partition to the newly created partition (mytable_p2024_03_22).

argos_db=# \d+ public.mytable;
Table partitionnée « public.mytable »
Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de sta
tistiques | Description
-----------------+--------------------------+-----------------+-----------+----------------------------------+----------+-------------+-------------
----------+-------------
id | bigint | | not null | nextval('mytable_id_seq'::regclass) | plain | |
|
ts_notification | timestamp with time zone | | not null | | plain | |
|
Clé de partition : RANGE (ts_notification)
Index :
"pk_mytable" PRIMARY KEY, btree (id, ts_notification)
Partitions: mytable_p2024_03_16 FOR VALUES FROM ('2024-03-16 00:00:00+01') TO ('2024-03-17 00:00:00+01'),
mytable_p2024_03_17 FOR VALUES FROM ('2024-03-17 00:00:00+01') TO ('2024-03-18 00:00:00+01'),
mytable_p2024_03_18 FOR VALUES FROM ('2024-03-18 00:00:00+01') TO ('2024-03-19 00:00:00+01'),
mytable_p2024_03_19 FOR VALUES FROM ('2024-03-19 00:00:00+01') TO ('2024-03-20 00:00:00+01'),
mytable_p2024_03_20 FOR VALUES FROM ('2024-03-20 00:00:00+01') TO ('2024-03-21 00:00:00+01'),
mytable_default DEFAULT

argos_db=# insert into mytable(ts_notification) values(now()+interval '4 day');
INSERT 0 1
argos_db=# select * from mytable_default ;
id | ts_notification
----+-------------------------------
1 | 2024-03-22 11:46:16.769195+01
(1 ligne)

argos_db=# call partman.partition_data_proc('public.mytable');
NOTICE: Batch: 1, Rows moved: 1
NOTICE: Total rows moved: 1
NOTICE: Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
argos_db=# \d+ public.mytable;
Table partitionnée « public.mytable »
Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de sta
tistiques | Description
-----------------+--------------------------+-----------------+-----------+----------------------------------+----------+-------------+-------------
----------+-------------
id | bigint | | not null | nextval('mytable_id_seq'::regclass) | plain | |
|
ts_notification | timestamp with time zone | | not null | | plain | |
|
Clé de partition : RANGE (ts_notification)
Index :
"pk_mytable" PRIMARY KEY, btree (id, ts_notification)
Partitions: mytable_p2024_03_16 FOR VALUES FROM ('2024-03-16 00:00:00+01') TO ('2024-03-17 00:00:00+01'),
mytable_p2024_03_17 FOR VALUES FROM ('2024-03-17 00:00:00+01') TO ('2024-03-18 00:00:00+01'),
mytable_p2024_03_18 FOR VALUES FROM ('2024-03-18 00:00:00+01') TO ('2024-03-19 00:00:00+01'),
mytable_p2024_03_19 FOR VALUES FROM ('2024-03-19 00:00:00+01') TO ('2024-03-20 00:00:00+01'),
mytable_p2024_03_20 FOR VALUES FROM ('2024-03-20 00:00:00+01') TO ('2024-03-21 00:00:00+01'),
mytable_p2024_03_22 FOR VALUES FROM ('2024-03-22 00:00:00+01') TO ('2024-03-23 00:00:00+01'),
mytable_default DEFAULT

Now I set the premake in table partman.part_config to 7 and ask for new partitions to be created but mytable_p2024_03_21 is still missing.
argos_db=# UPDATE partman.part_config SET premake=7 WHERE parent_table = 'public.mytable';
UPDATE 1
argos_db=# SELECT * FROM partman.run_maintenance('public.mytable');
run_maintenance

(1 ligne)

argos_db=# \d+ public.mytable;
Table partitionnée « public.mytable »
Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de sta
tistiques | Description
-----------------+--------------------------+-----------------+-----------+----------------------------------+----------+-------------+-------------
----------+-------------
id | bigint | | not null | nextval('mytable_id_seq'::regclass) | plain | |
|
ts_notification | timestamp with time zone | | not null | | plain | |
|
Clé de partition : RANGE (ts_notification)
Index :
"pk_mytable" PRIMARY KEY, btree (id, ts_notification)
Partitions: mytable_p2024_03_16 FOR VALUES FROM ('2024-03-16 00:00:00+01') TO ('2024-03-17 00:00:00+01'),
mytable_p2024_03_17 FOR VALUES FROM ('2024-03-17 00:00:00+01') TO ('2024-03-18 00:00:00+01'),
mytable_p2024_03_18 FOR VALUES FROM ('2024-03-18 00:00:00+01') TO ('2024-03-19 00:00:00+01'),
mytable_p2024_03_19 FOR VALUES FROM ('2024-03-19 00:00:00+01') TO ('2024-03-20 00:00:00+01'),
mytable_p2024_03_20 FOR VALUES FROM ('2024-03-20 00:00:00+01') TO ('2024-03-21 00:00:00+01'),
mytable_p2024_03_22 FOR VALUES FROM ('2024-03-22 00:00:00+01') TO ('2024-03-23 00:00:00+01'),
mytable_p2024_03_23 FOR VALUES FROM ('2024-03-23 00:00:00+01') TO ('2024-03-24 00:00:00+01'),
mytable_p2024_03_24 FOR VALUES FROM ('2024-03-24 00:00:00+01') TO ('2024-03-25 00:00:00+01'),
mytable_p2024_03_25 FOR VALUES FROM ('2024-03-25 00:00:00+01') TO ('2024-03-26 00:00:00+01'),
mytable_p2024_03_26 FOR VALUES FROM ('2024-03-26 00:00:00+01') TO ('2024-03-27 00:00:00+01'),
mytable_default DEFAULT

Is it possible to fix the issue to allow the creation of missing partitions ?

Thank you.

[keithf4]

Use the partition_gap_fill() function to fix missing child partitions like this

Normal maintenance runs are based on whatever the "newest" partition is.

[jeanloesch]

Hi Keithf4,

It addressed my problem.

Thank you very much.

Best regards.