Re: Question on partman extension while relation exist

Hi Yudhi,

I think disabling foreign keys before maintenance will help.

Regards,
Muhammad Ikram
Bitnine global

On Tue, Jul 2, 2024 at 11:41 AM yudhi s <learnerdatabase99(at)gmail(dot)com> wrote:

> Hello All,
> In postgres we are seeing issues during automatic partition maintenance
> using pg_partman extension. So basically it automatically creates one new
> partition and drops one historical partition each day based on the set
> retention period in part_config. We just call it like
> partman.run_maintenance_proc('table_name');
>
> While there exists foreign key relationships between the partitioned
> tables, Mainly during dropping the parent partitions it takes a lot of
> time, as it validates every child table partitions record and also is
> taking lock longer. Ideally it should check only the respective parent
> partition, but it's not doing that because the foreign key is defined in
> table level rather than partition level. So we are planning to create the
> foreign keys on the partition level but not at table level.
>
> And we were thinking of doing it dynamically by having an "event trigger"
> which will fire on "create statement" i.e while the "create new partition"
> statement will be triggered by the Pg_partman. It will try to also create
> the foreign key constraints on the new child partition referring to the
> respective parent partition during the same time. So that things will be
> automated.
>
> But now we are stuck in one scenario , say for example if we execute the
> pg_partman for the parent table first then it will create the new partition
> independently which is fine, but when it will try to drop the historical
> partition, it will complain stating the child partition already exists.
>
> On the other hand,
>
> If we run the pg_partman for the child table first, then it will drop the
> historical child partition without any issue , however it will throw an
> error while creating the foreign key , as because the respective parent
> partition has not yet been created.
>
> Need advice, how we should handle this scenario. Basically in which order
> we should call the "pg_partman.run_maintenance_proc" for the parent and
> child tables?
>

--
Muhammad Ikram