Regarding run_maintenance_proc() with concurrently option for detaching tables

[Durgamahesh671]

Hi
With pg_partman we know the use case .By default run_maintenance_proc() does not detach tables concurrently. How do we implement tables detach concurrently without blocking other sessions

We need to detach child tables with concurrently option to ensure that detach to be happened without skipping when running queries on parent table based on the retention policy as here some of the queries not using date column which in turn to scan all available child tables

There would be no problem while detaching tables if queries using date column

• Is there any option to detach tables concurrently with the mentioned proc in case of queries not using date column ?

[keithf4]

CONCURRENT operations in PostgreSQL cannot be run within an explicit transactions (manual call of BEGIN/END). All functions and procedures run as explicit transactions, so you cannot call any CONCURRENT operation within them. Therefore, there is no way for any pg_partman function within the database to provide concurrent drops.

This does bring up a possible idea to write a python script as has been done for some other pg_partman functions (https://github.com/pgpartman/pg_partman/tree/master/bin/common). Using that sort of method or any other method to connect and run the DROP CONCURRENTLY command is the only way I can think of to do so. I'm not sure when I will have the time to do so in the near future, but I can leave this issue open as a feature request to look into it. Be happy to accept a PR to review as well if you implement a python script like this.