Can someone please confirm if this behavior of foreign key is expected with the partition created through partman extension and thus we need to have our manual process written for partition purge (in order of child first and then parent) , rather relying on partman partition maintenance to take care drop partition automatically for us?
On Thu, 2024-03-21 at 22:50 +0530, veem v wrote:
> 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 ?
I don't know the capabilities of partmen, but I would be surprised if
it could automatically create foreign keys on the partitions.
Yes, the constraints on each of the child partitions and parent partitions were getting created automatically. As I see from information_schema.table_constraints, it shows one foreign key constraint in each of the child partitions created through the partman procedure.
It works smoothly without any issue, until we noticed this issue while trying to purge the partition from the parent table partition. But I believe this extension is extensively used , so I'm just wondering if I am missing something here with regards to foreign key creation using this automated partition creation/partman extension functionality.