What you *can* do is detach the partition and then drop it, but detatching will
be slow because PostgreSQL has to check for referencing rows.
The best solution is to create the foreign key *not* between the partitioned
tables, but between the individual table partitions. That should be easy if
you have the same partition boundaries for both.
Then you can simply drop a partition from both tables at the same time.
Thank you Laurenz.
With the existing foreign key setup, the drop partitions works fine for the child tables when they are triggered through the cron job by calling "run_maintenance_proc". But its failing for parent table partition drop. And as you mentioned if we do the detach and then drop it works fine for the parent table.
However, we are creating partitions using the create_parent procedure something as below ,and thus it creates the default template table based on the main table structure informations and also the subsequent partitions are created/dropped accordingly using the scheduled cron as below.
So when you mentioned "to create the foreign keys *not* between the partitioned table but between the individual partitions" , can that be done using the same "partman.create_parent" procedure and automated cron job schedule or has to be done any other way manually ?
Additionally , do we have to give call to the cron job in orderly fashion, i.e child first and then parent table? As because, currently we were just , scheduling "run_maintenance_proc" once and all the partition tables maintenance(i.e. creation of new partition and dropping of old partitions) was being taken care automatically by that one call.
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='true', retention_keep_index='true',retention_schema='public'
where parent_table = 'schema1.test_part_drop_child';
SELECT cron.schedule('@hourly',
);