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',
);
On Thu, 21 Mar, 2024, 11:07 am veem v, <veema0...@gmail.com> wrote:
> 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
>
