Обсуждение: Question on partman extension while relation exist
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?
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?
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@gmail.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
On Tue, 2 Jul, 2024, 12:43 pm Muhammad Ikram, <mmikram@gmail.com> wrote:
Hi Yudhi,I think disabling foreign keys before maintenance will help.--Muhammad Ikram
Do you mean to say call the parent table first for maintenance followed by child, and remove all the foreign key first which are pointing to this parent table partition which is going to be dropped by the pg_partman?
As drop/create partition is being called from within pg_partman without our intervention, so where should we put this drop foreign key code? Do you mean having that with another event trigger which will fire before drop?
Hi,
Sorry for late reply, I think you will having some script that drops and creates daily partitions etc, command for disabling/removing any constraint can be placed before these statements so that when the script runs it does the needful.
Regards,
Muhammad Ikram
Bitnine global
On Tue, Jul 2, 2024 at 12:54 PM yudhi s <learnerdatabase99@gmail.com> wrote:
On Tue, 2 Jul, 2024, 12:43 pm Muhammad Ikram, <mmikram@gmail.com> wrote:Hi Yudhi,I think disabling foreign keys before maintenance will help.--Muhammad IkramDo you mean to say call the parent table first for maintenance followed by child, and remove all the foreign key first which are pointing to this parent table partition which is going to be dropped by the pg_partman?As drop/create partition is being called from within pg_partman without our intervention, so where should we put this drop foreign key code? Do you mean having that with another event trigger which will fire before drop?
Muhammad Ikram