Future partition names generated incorrectly for one table

[andreykhan]

Hi everyone,

I have partitioned several tables using pg_partman (5.1.0), and everything works fine except for one table. The future partitions for this table are being created with incorrect names.

Here’s an example of a table where future partitions are generated correctly:
dap_dau_activebase_default
dap_dau_activebase_p20231101
dap_dau_activebase_p20231201
dap_dau_activebase_p20240101
dap_dau_activebase_p20240201
dap_dau_activebase_p20240301
...
dap_dau_activebase_p20251101
dap_dau_activebase_p20251201

And here’s an example of the table with the issue:
dap_mindbox_mailings_default
dap_mindbox_mailings_p20231101
dap_mindbox_mailings_p20231201
dap_mindbox_mailings_p20240101
dap_mindbox_mailings_p20240201
dap_mindbox_mailings_p20240301
...
dap_mindbox_mailings_p20251101
dap_mindbox_mailings_p20251130
As you can see, the partition for 2025-11-30 is named incorrectly, and there’s no partition generated for December 2025.

I’ve double-checked the configuration in partman.part_config and confirmed that it matches the settings for other tables where everything works as expected.

Queried the minimum and maximum values of the partitioning column action_dtm:
SELECT MIN(action_dtm), MAX(action_dtm)
FROM public.dap_mindbox_mailings;
2023-01-25 15:07:16.370 +0500 2024-12-20 11:01:30.000 +0500

Checked for NULL values in the partitioning column action_dtm:
SELECT COUNT(*)
FROM public.dap_mindbox_mailings
WHERE action_dtm IS NULL;
0

You can also find my partman.part_config table in the attached file
part_config_202412201219.xlsx

Could you please help me understand why this issue occurs and how I can fix it?
Thank you

BR Andrey

[keithf4]

Going to admit you've got me stumped on this one. With a partition interval of 1 month, it should always be truncating it to the beginning of the month. I tried recreating your scenario and it seems to have worked without issue. See below.

The only thing I can think of at the moment is that somehow the part config interval got changed temporarily for a certain maintenance run then changed back. If you drop that table then run run_maintenance() again, does it recreate the proper child table? Assuming Dec 2025 is still your final partition. If not, you can try using the partition_gap_fill() function if you have tables beyond Dec 25 now.

Will be hard for me to debug this anymore unless you can provide a reproducible scenario. Not saying there is no bug, there certainly could be. But I'd have to see it happening to diagnose it any further.

keith@github722=# create table dap_mindbox_mailings (action_dtm timestamptz not null) partition by range (action_dtm);
CREATE TABLE
Time: 15.591 ms

keith@github722=# select partman.create_parent('public.dap_mindbox_mailings', 'action_dtm', '1 month');
 create_parent 
---------------
 t
(1 row)
Time: 21.238 ms

keith@github722=# update partman.part_config set premake = 12 where parent_table = 'public.dap_mindbox_mailings';
UPDATE 1
Time: 15.483 ms

keith@github722=# insert into dap_mindbox_mailings values (generate_series('2023-01-25 15:00:00'::timestamptz, '2024-12-20 11:00:00'::timestamptz, '1 day'::interval));
INSERT 0 695
Time: 17.809 ms

keith@github722=# select min(action_dtm), max(action_dtm) from dap_mindbox_mailings;
          min           |          max           
------------------------+------------------------
 2023-01-25 15:00:00-05 | 2024-12-19 15:00:00-05
(1 row)


keith@github722=# \d+ dap_mindbox_mailings
                                       Partitioned table "public.dap_mindbox_mailings"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 action_dtm | timestamp with time zone |           | not null |         | plain   |             |              | 
Partition key: RANGE (action_dtm)
Partitions: dap_mindbox_mailings_p20240801 FOR VALUES FROM ('2024-08-01 00:00:00-04') TO ('2024-09-01 00:00:00-04'),
            dap_mindbox_mailings_p20240901 FOR VALUES FROM ('2024-09-01 00:00:00-04') TO ('2024-10-01 00:00:00-04'),
            dap_mindbox_mailings_p20241001 FOR VALUES FROM ('2024-10-01 00:00:00-04') TO ('2024-11-01 00:00:00-04'),
            dap_mindbox_mailings_p20241101 FOR VALUES FROM ('2024-11-01 00:00:00-04') TO ('2024-12-01 00:00:00-05'),
            dap_mindbox_mailings_p20241201 FOR VALUES FROM ('2024-12-01 00:00:00-05') TO ('2025-01-01 00:00:00-05'),
            dap_mindbox_mailings_p20250101 FOR VALUES FROM ('2025-01-01 00:00:00-05') TO ('2025-02-01 00:00:00-05'),
            dap_mindbox_mailings_p20250201 FOR VALUES FROM ('2025-02-01 00:00:00-05') TO ('2025-03-01 00:00:00-05'),
            dap_mindbox_mailings_p20250301 FOR VALUES FROM ('2025-03-01 00:00:00-05') TO ('2025-04-01 00:00:00-04'),
            dap_mindbox_mailings_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00-04') TO ('2025-05-01 00:00:00-04'),
            dap_mindbox_mailings_default DEFAULT

keith@github722=# select partman.run_maintenance();
 run_maintenance 
-----------------
 
(1 row)
Time: 30.240 ms

keith@github722=# \d+ dap_mindbox_mailings
                                       Partitioned table "public.dap_mindbox_mailings"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 action_dtm | timestamp with time zone |           | not null |         | plain   |             |              | 
Partition key: RANGE (action_dtm)
Partitions: dap_mindbox_mailings_p20240801 FOR VALUES FROM ('2024-08-01 00:00:00-04') TO ('2024-09-01 00:00:00-04'),
            dap_mindbox_mailings_p20240901 FOR VALUES FROM ('2024-09-01 00:00:00-04') TO ('2024-10-01 00:00:00-04'),
            dap_mindbox_mailings_p20241001 FOR VALUES FROM ('2024-10-01 00:00:00-04') TO ('2024-11-01 00:00:00-04'),
            dap_mindbox_mailings_p20241101 FOR VALUES FROM ('2024-11-01 00:00:00-04') TO ('2024-12-01 00:00:00-05'),
            dap_mindbox_mailings_p20241201 FOR VALUES FROM ('2024-12-01 00:00:00-05') TO ('2025-01-01 00:00:00-05'),
            dap_mindbox_mailings_p20250101 FOR VALUES FROM ('2025-01-01 00:00:00-05') TO ('2025-02-01 00:00:00-05'),
            dap_mindbox_mailings_p20250201 FOR VALUES FROM ('2025-02-01 00:00:00-05') TO ('2025-03-01 00:00:00-05'),
            dap_mindbox_mailings_p20250301 FOR VALUES FROM ('2025-03-01 00:00:00-05') TO ('2025-04-01 00:00:00-04'),
            dap_mindbox_mailings_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00-04') TO ('2025-05-01 00:00:00-04'),
            dap_mindbox_mailings_p20250501 FOR VALUES FROM ('2025-05-01 00:00:00-04') TO ('2025-06-01 00:00:00-04'),
            dap_mindbox_mailings_p20250601 FOR VALUES FROM ('2025-06-01 00:00:00-04') TO ('2025-07-01 00:00:00-04'),
            dap_mindbox_mailings_p20250701 FOR VALUES FROM ('2025-07-01 00:00:00-04') TO ('2025-08-01 00:00:00-04'),
            dap_mindbox_mailings_p20250801 FOR VALUES FROM ('2025-08-01 00:00:00-04') TO ('2025-09-01 00:00:00-04'),
            dap_mindbox_mailings_p20250901 FOR VALUES FROM ('2025-09-01 00:00:00-04') TO ('2025-10-01 00:00:00-04'),
            dap_mindbox_mailings_p20251001 FOR VALUES FROM ('2025-10-01 00:00:00-04') TO ('2025-11-01 00:00:00-04'),
            dap_mindbox_mailings_p20251101 FOR VALUES FROM ('2025-11-01 00:00:00-04') TO ('2025-12-01 00:00:00-05'),
            dap_mindbox_mailings_p20251201 FOR VALUES FROM ('2025-12-01 00:00:00-05') TO ('2026-01-01 00:00:00-05'),
            dap_mindbox_mailings_default DEFAULT

Tried increasing premake even more to see if it was an issue beyond another year

keith@github722=# update partman.part_config set premake = 15 where parent_table = 'public.dap_mindbox_mailings';
UPDATE 1
Time: 15.592 ms
keith@github722=# select partman.run_maintenance();
 run_maintenance 
-----------------
 
(1 row)

Time: 18.817 ms
keith@github722=# \d+ dap_mindbox_mailings
                                       Partitioned table "public.dap_mindbox_mailings"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 action_dtm | timestamp with time zone |           | not null |         | plain   |             |              | 
Partition key: RANGE (action_dtm)
Partitions: dap_mindbox_mailings_p20240801 FOR VALUES FROM ('2024-08-01 00:00:00-04') TO ('2024-09-01 00:00:00-04'),
            dap_mindbox_mailings_p20240901 FOR VALUES FROM ('2024-09-01 00:00:00-04') TO ('2024-10-01 00:00:00-04'),
            dap_mindbox_mailings_p20241001 FOR VALUES FROM ('2024-10-01 00:00:00-04') TO ('2024-11-01 00:00:00-04'),
            dap_mindbox_mailings_p20241101 FOR VALUES FROM ('2024-11-01 00:00:00-04') TO ('2024-12-01 00:00:00-05'),
            dap_mindbox_mailings_p20241201 FOR VALUES FROM ('2024-12-01 00:00:00-05') TO ('2025-01-01 00:00:00-05'),
            dap_mindbox_mailings_p20250101 FOR VALUES FROM ('2025-01-01 00:00:00-05') TO ('2025-02-01 00:00:00-05'),
            dap_mindbox_mailings_p20250201 FOR VALUES FROM ('2025-02-01 00:00:00-05') TO ('2025-03-01 00:00:00-05'),
            dap_mindbox_mailings_p20250301 FOR VALUES FROM ('2025-03-01 00:00:00-05') TO ('2025-04-01 00:00:00-04'),
            dap_mindbox_mailings_p20250401 FOR VALUES FROM ('2025-04-01 00:00:00-04') TO ('2025-05-01 00:00:00-04'),
            dap_mindbox_mailings_p20250501 FOR VALUES FROM ('2025-05-01 00:00:00-04') TO ('2025-06-01 00:00:00-04'),
            dap_mindbox_mailings_p20250601 FOR VALUES FROM ('2025-06-01 00:00:00-04') TO ('2025-07-01 00:00:00-04'),
            dap_mindbox_mailings_p20250701 FOR VALUES FROM ('2025-07-01 00:00:00-04') TO ('2025-08-01 00:00:00-04'),
            dap_mindbox_mailings_p20250801 FOR VALUES FROM ('2025-08-01 00:00:00-04') TO ('2025-09-01 00:00:00-04'),
            dap_mindbox_mailings_p20250901 FOR VALUES FROM ('2025-09-01 00:00:00-04') TO ('2025-10-01 00:00:00-04'),
            dap_mindbox_mailings_p20251001 FOR VALUES FROM ('2025-10-01 00:00:00-04') TO ('2025-11-01 00:00:00-04'),
            dap_mindbox_mailings_p20251101 FOR VALUES FROM ('2025-11-01 00:00:00-04') TO ('2025-12-01 00:00:00-05'),
            dap_mindbox_mailings_p20251201 FOR VALUES FROM ('2025-12-01 00:00:00-05') TO ('2026-01-01 00:00:00-05'),
            dap_mindbox_mailings_p20260101 FOR VALUES FROM ('2026-01-01 00:00:00-05') TO ('2026-02-01 00:00:00-05'),
            dap_mindbox_mailings_p20260201 FOR VALUES FROM ('2026-02-01 00:00:00-05') TO ('2026-03-01 00:00:00-05'),
            dap_mindbox_mailings_p20260301 FOR VALUES FROM ('2026-03-01 00:00:00-05') TO ('2026-04-01 00:00:00-04'),
            dap_mindbox_mailings_default DEFAULT