Add a way to explicitly order table maintenance

[axelfaure]

Hi,

I have two (actually more than two) tables to manage with partman (I want to create and detach partition automatically) and there is a foreign_key between these tables. As a result I cannot detach partitions randomly or it will break the foreign keys, I need to call run_maintenance in a specific order.

It would be nice in future versions of pg_partman to have the ability to define in which orders tables are altered when calling run_maintenance_proc to avoid having to maintain our own script to handle this case. Obviously a killer feature would be to detect foreign keys automatically and handle the ordering without additional input from the user but that might be too complicated and a manual ordering would be a very reasonable starting point.

[keithf4]

Interesting feature request. I'll see what can be done.

[axelfaure]

Hi !
I am still extending my partitioning process and I found a new issue more or less related to this ticket.

When I detach my partitions (without dropping them) in this situation, it works but breaks my foreign keys because they still target the main table but the partitions are no longer present. I think I will call a function after detaching to remove all foreign keys from detached partitions.

If you want to handle this issue in a future version, it might be good to (optionally?) drop foreign keys on detached partitions.

[keithf4]

Created a new issue to discuss your FK question.
Ordering of tables in maintenance will be available in the next release. Thanks for the suggestion!

[keithf4]

Version 5.1 has been released with the maintenance order fix