Parent Table Not Archived When Running pg_partman Maintenance #692
Description
@mattp
PG Version : 16
Partman Version : 5.0.1
I have two partitioned tables, booking and booking_lines, with the following setup:
booking is partitioned by created_at on a monthly basis.
booking_lines references booking via a foreign key (booking_id) and is also partitioned by created_at.
Both tables have an archive retention policy configured to move partitions older than 6 months to an archive schema (cbk_archive)
CREATE TABLE cbk.bookings(
id bigserial, -- PK
created_at timestamptz NOT NULL DEFAULT now(), -- PK
PRIMARY KEY (id, created_at) -- the partition column must be part of pk
) PARTITION BY RANGE (created_at);
CREATE INDEX "bookings_created_at" ON cbk.bookings (created_at);
SELECT partman.create_parent(
p_parent_table := 'cbk.bookings', -- Parent table
p_control := 'created_at', -- Partition key (e.g., timestamp)
p_interval := '1 month', -- Partition interval (monthly)
p_start_partition := '2023-01-01' -- Start partitioning from this date
);
CREATE TABLE cbk.booking_lines(
id bigserial, --PK
created_at timestamptz NOT NULL, -- PK
booking_id bigint NOT NULL, -- FK
booking_created_at timestamptz NOT NULL, -- FK
message text NOT NULL,
PRIMARY KEY (id, created_at),
FOREIGN KEY (booking_id, booking_created_at) -- multicolumn fk to ensure
REFERENCES cbk.bookings(id, created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX "booking_lins_created_at" ON cbk.booking_lines (created_at);
-- need this index on the fk source to lookup messages by parent
CREATE INDEX "booking_lines_booking_id_booking_created_at"
ON cbk.booking_lines (booking_id, booking_created_at);
SELECT partman.create_parent(
p_parent_table := 'cbk.booking_lines', -- Parent table
p_control := 'created_at', -- Partition key (e.g., timestamp)
p_interval := '1 month', -- Partition interval (monthly)
p_start_partition := '2023-01-01' -- Start partitioning from this date
UPDATE partman.part_config
SET
retention = '6 months', -- Set retention period to 6 months
retention_schema = 'cbk_archive', -- Specify the archive schema for old partitions
retention_keep_table = true, -- Keep the partition table when moving
retention_keep_index = true, -- Keep indexes for the detached partitions
automatic_maintenance = 'on' -- Ensure automatic maintenance is enabled
WHERE
parent_table = 'cbk.bookings'; -- Specify the parent table to update
UPDATE partman.part_config
SET
retention = '6 months', -- Set retention period to 6 months
retention_schema = 'cbk_archive', -- Specify the archive schema for old partitions
retention_keep_table = true, -- Keep the partition table when moving
retention_keep_index = true, -- Keep indexes for the detached partitions
automatic_maintenance = 'on' -- Ensure automatic maintenance is enabled
WHERE
parent_table = 'cbk.booking_lines'; -- Specify the parent table to update
-- Since select partman.run_maintenance() not working as expected when PK and FK used due to foreign key constraint , so moved to execute first child table and then parent table
select partman.run_maintenance('cbk.booking_lines');
select partman.run_maintenance('cbk.cbk.bookings');``
/* Data Setup */
INSERT INTO cbk.bookings (created_at)
SELECT generate_series(
'2023-01-01'::timestamptz,
'2024-09-25 00:00:00'::timestamptz,
interval '1 day');
INSERT INTO cbk.booking_lines (created_at, booking_id, booking_created_at, message)
SELECT
mca,
bookings.id,
bookings.created_at,
(SELECT ($$[0:3]={'hello','goodbye','How are you today','I am fine'}$$::text[])[trunc(random() * 4)::int])
FROM cbk.bookings
CROSS JOIN LATERAL (
SELECT generate_series(
bookings.created_at,
bookings.created_at + interval '1 day',
interval '1 day') AS mca) b;
Post execution , i can see the "cbk.booking_lines" moved to but not able to move the "cbk.bookings" to archive. Please suggest the bug fix or any alternative maintainable solution. we have many tables 1:m and m:m relations and we are trying to manage the 6 month of transactional data and rest moved to archive schema.
