pg_partman issue in case of table relationship exists

[databasetech0073]

Hello, 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 is there a way in which order we should call the "pg_partman.run_maintenance_proc" for the parent and child tables so that it will happen seamlessly?

[keithf4]

Just trying to clarify, is this a foreign key relationship between two different partition sets? If so are both managed by partman? Or is this some sort of self-referential foreign key (foreign key back to the same table itself on another column)?

[databasetech0073]

No, Its not self referential FK. We have the foreign key relationship between the columns of two different tables and each of the table is daily range partitioned and the partition was maintained by pg_partman. But as that is giving issue while doing the partition maintenance for the parent table. Its taking longer time and also taking lock.

So we are trying to remove the foreign key from table level and add it to partition level. But for that to happen we need to add the foreign key to each new child partition when it gets created and we are planning to do that through an event trigger, which will fire on "create table" statement. But the question is in which order we should do the partition maintenance, as because , if we do the partition maintenance for the Parent first then , it will error out stating child partition exists. And if we do partition maintenance for the child table first then , it will create issue while creating foreign key through the event trigger?

[keithf4]

So have you seen it erroring out that the child table already exists? Partman should not complain if a child table already exists when maintenance goes to run. It should just see it's already there and continue on as a no-op.

[keithf4]

Sorry, I went back and read the original issue again. So you're getting an error when it tries to drop the child table saying it already exists? I'm a bit confused about that.

Is there any way you can give me a repeatable example so I can see what's happening? Foreign keys have been particularly tricky with partitioning and I don't know all the answers for how to handle it yet.