Parent Table Not Archived When Running pg_partman Maintenance

[anjay-gupta]

@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.

[keithf4]

So just to be clear, did calling the run_maintance() functions explicitly on the given tables work as you expected then?

If so, then version 5.1 of pg_partman should have a solution for you. It added a maintenance_order column to the part_config table. With it, you can explicitly set which order each partition set will run in. So in your case here, just make sure the tables with the FK relations are set to run in the order that works for them.
Any partition sets without maintenance_order set will run after all other ordered partitions.

https://github.com/pgpartman/pg_partman/blob/master/CHANGELOG.md#510
https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#configuration-tables

[keithf4]

Just checking to see if you're still having an issue and if version 5.1 provided a solution for you