pgpartman
Make retention use real child table boundaries
I don't think this is a bug, just looking for clarification. We are on 5.0.1 but it doesn't look like this behaviour changed.
https://github.com/pgpartman/pg_partman/blob/v5.2.4/doc/pg_partman.md#retention says:
For time-based partitioning, the interval value will set that any partitions containing only data older than that will be dropped (including safely handling cases where the retention interval is not a multiple of the partition size).
We had a problem where we converted an existing table to be partitioned (https://github.com/pgpartman/pg_partman/issues/527#issuecomment-2743729080). We set the existing table as the first partition with a wide time range to cover all its values.
This all worked perfectly, but running the maintenance proc (thankfully not in production!) deleted the existing table. The above docs say (emphasis added):
any partitions containing only data older than that will be dropped
Our partition contained data that was before the retention period, and data that should still be included in the retention period, so we expected it would only be dropped when all of the data in that should no longer be retained.
Brief example:
CREATE SCHEMA partman;
CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman;
CREATE TABLE test (created_at timestamp with time zone DEFAULT now() NOT NULL, text text) PARTITION BY RANGE (created_at);
CREATE INDEX test_created_at ON test (created_at);
CREATE TABLE test_template (LIKE test);
SELECT partman.create_parent(
p_parent_table => 'public.test',
p_template_table => 'public.test',
p_default_table => FALSE,
p_control => 'created_at',
p_interval => '1 month',
p_premake => 3,
p_type => 'range',
p_start_partition => to_char(CURRENT_TIMESTAMP + '1 month'::interval, 'YYYY-MM-01')
);
UPDATE partman.part_config SET infinite_time_partitions = TRUE, retention = '3 months', retention_keep_table = FALSE WHERE parent_table = 'public.test';
CREATE TABLE test_legacy (LIKE test);
ALTER TABLE test ATTACH PARTITION test_legacy FOR VALUES FROM ('2000-01-01') TO (to_char(CURRENT_TIMESTAMP + '1 month'::interval, 'YYYY-MM-01')::timestamptz);
INSERT INTO test (created_at, text) VALUES ('2020-01-01', 'old value'), (CURRENT_TIMESTAMP, 'new value');
SELECT * FROM test_legacy;
-- created_at | text
-- -------------------------------+-----------
-- 2020-01-01 00:00:00+00 | old value
-- 2025-05-12 12:56:03.226426+00 | new value
-- (2 rows)
-- postgres=# \d+ test
-- Partitioned table "public.test"
-- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-- ------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
-- created_at | timestamp with time zone | | not null | now() | plain | | |
-- text | text | | | | extended | | |
-- Partition key: RANGE (created_at)
-- Indexes:
-- "test_created_at" btree (created_at)
-- Partitions: test_legacy FOR VALUES FROM ('2000-01-01 00:00:00+00') TO ('2025-06-01 00:00:00+00'),
-- test_p20250601 FOR VALUES FROM ('2025-06-01 00:00:00+00') TO ('2025-07-01 00:00:00+00'),
-- test_p20250701 FOR VALUES FROM ('2025-07-01 00:00:00+00') TO ('2025-08-01 00:00:00+00'),
-- test_p20250801 FOR VALUES FROM ('2025-08-01 00:00:00+00') TO ('2025-09-01 00:00:00+00')
We now run maintenance and the partition is deleted, leaving us with no current partition, which is a bad state!
CALL partman.run_maintenance_proc();
-- postgres=# \d+ test
-- Partitioned table "public.test"
-- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-- ------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
-- created_at | timestamp with time zone | | not null | now() | plain | | |
-- text | text | | | | extended | | |
-- Partition key: RANGE (created_at)
-- Indexes:
-- "test_created_at" btree (created_at)
-- Partitions: test_p20250601 FOR VALUES FROM ('2025-06-01 00:00:00+00') TO ('2025-07-01 00:00:00+00'),
-- test_p20250701 FOR VALUES FROM ('2025-07-01 00:00:00+00') TO ('2025-08-01 00:00:00+00'),
-- test_p20250801 FOR VALUES FROM ('2025-08-01 00:00:00+00') TO ('2025-09-01 00:00:00+00')
Is that expected?
That is not the expected behavior. Will look into this a soon as I have a chance. Thank you for reporting it!
So I think I found the issue here and as of now this is working as designed even thought it's not intended. However, I think this can be fixed.
Currently the retention system is not set up to handle child tables that do not follow the default partitioning interval. This is actually a legacy issue from pre-5.0 when the child table boundaries were calculated from the partition name. In most cases partman does look up the boundaries from the catalog, but in the case of retention it's still calculating the upper boundary to figure out whether to drop the table based on simply adding the interval to the lower boundary.
https://github.com/pgpartman/pg_partman/blob/development/sql/functions/drop_partition_time.sql#L163
I think this should be able to be fixed to use the actual table boundaries instead. However, I'm not sure I'll get to test this out and get the fix in for the next release. I'll definitely put it on the table for the next release, though. Thank you for reporting this!
Thanks, good to know. For now we can easily work around it (just disable retention until we're ready for it to drop the entirety of the first partition).