Re: Not able to purge partition

On Thu, 21 Mar, 2024, 6:18 pm Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

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',
);