Also, i see, its not allowing to drop the parent table partition even all the respective child partitions are dropped and no child record exists in the child table. But this started working , when we detach parent partition and then drop the partitions. So it means if we just change the setup of the parent partition table in the part_config as retention_keep_table='true', retention_keep_index='true' and retention_schema=<retention_schema>, it will work without giving error. And then we have to drop the partitions from that retention_schema through another procedure may be. Correct me if anyone has done with some other workaround.
However , i have one question now, if we have child record exists in child partition table, it wont even allow the detach the respective parent partition, so is the "CALL partman.run_maintenance_proc()" automatically ensures the ordering of child and parent table which will be passed through the partition maintenance ? Or for that also we need to write our own procedure and schedule through the cron?
Something as below,
CREATE PROCEDURE part_maintenance_proc()
LANGUAGE SQL
AS $$
declare
drop_partition_cursor CURSOR FOR
table_name from information_schema.tables where table_name like '%test_part_drop_parent%' and table_schema like '%retention_schema%';
drop_partition_record RECORD;
Begin
partman.run_maintenance('cpod.test_part_drop_child');
partman.run_maintenance('cpod.test_part_drop_parent');
OPEN drop_partition_cursor
loop
FETCH NEXT FROM drop_partition_cursor INTO drop_partition_record;
EXIT WHEN NOT FOUND;
drop table drop_partition_record.table_name;
end loop;
close drop_partition_cursor;
END;
$$;
SELECT cron.schedule('@hourly',
);
Hello All,
We created two partitioned tables one being parent and other child. Both are range partitioned and one table has a foreign key to the other parent table. Now , when we run the partition maintenance job to drop the older partitions which meet the partition retention criteria, it's not letting us drop any of the partitions from the parent table. Child table works fine though.
And when we try using cascade , it drops the foreign key constraint from all the partitions. Can you please let me know how to make the partition maintenance work in such a scenario while maintaining the foreign key intact, as we are expecting the foreign key to be attached to the respective partition only but not the full table?
And this partition maintenance job which runs through cron scheduler in an automated way is failing on a daily basis for us without being able to drop any of the historical partitions from the parent partitioned table.
Below is the test case:-
CREATE TABLE schema1.test_part_drop_parent
(
c1 varchar(36) NOT NULL ,
c2_part_date timestamp with time zone NOT NULL ,
c3 numeric(12,0) NOT NULL ,
CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);
CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC);
CREATE TABLE schema1.test_part_drop_child
(
C1_child varchar(36) NOT NULL ,
C1 varchar(36) NOT NULL ,
c2_part_date timestamp with time zone NOT NULL ,
C3 numeric(12,0) NOT NULL ,
CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date),
CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES schema1.test_part_drop_parent(c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);
CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, c2_part_date);
CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( c1_child, c2_part_date);
select partman.create_parent(
p_parent_table := 'schema1.test_part_drop_child',
p_control := 'c2_part_date',
p_type := 'native',
p_interval := '1 day',
p_premake := 5,
p_start_partition => '2024-02-01 00:00:00'
);
update partman.part_config set infinite_time_partitions = 'true' , retention = '1 months', retention_keep_table='false', retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_child';
select partman.create_parent(
p_parent_table := 'schema1.test_part_drop_parent',
p_control := 'c2_part_date',
p_type := 'native',
p_interval := '1 day',
p_premake := 5,
p_start_partition => '2024-02-01 00:00:00'
);
update partman.part_config set infinite_time_partitions = 'true' , retention = '1 months', retention_keep_table='false', retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_parent';
select partman.run_maintenance('schema1.test_part_drop_child');
select partman.run_maintenance('schema1.test_part_drop_parent');
SQL Error [P0001]: ERROR: cannot drop table schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01"
PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 213 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at assignment
DETAIL: constraint test_part_drop_child_fk1 on table schema1.test_part_drop_child depends on table schema1.test_part_drop_parent_p2024_02_01
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 308 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at assignment
DETAIL:
HINT:
Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 413 at RAISE
