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.

[calebj]

I think this is doable from inside the DB, but am concerned that using dblink to perform the detach as part of maintenance runs the risk of a deadlock.

The ALTER TABLE actions ATTACH PARTITION and DETACH PARTITION CONCURRENTLY both acquire a SHARE UPDATE EXCLUSIVE lock on the partitioned table. The problem scenario would be when the transaction containing run_maintenance performs an attach, then calls dblink_exec() to run the ALTER TABLE DETACH PARTITION CONCURRENTLY statement, which is waiting on run_maintenance on that table to commit, which won't happen until dblink_exec() returns. I doubt Postgres would detect this deadlock, because the dblink acts like a separate client connection, but I may be wrong. Even it is detected, killing either transaction would be a problem, and the usual fix of using a statement timeout would defeat the purpose of it all.

Just throwing ideas out here, maybe run_maintenance() could record the intent to detach (and possibly drop or move) each partition for retention in a table under @extschema@ created for this purpose. Then, run_maintenance_proc() or a scheduled task can call another function which uses dblink to perform the detach, then performs the drop/move if necessary. Are there any glaring issues with this approach?

[keithf4]

The pg_jobmon extension that pg_partman can use for monitoring does this trick to allow autonomous transactions to keep function steps logged during a rollback.

I'd rather not go the dblink route in partman since that would require having to maintain some other configuration that contains the dblink credentials. I think the python script method would be what I'd accept at this point since partman already has precedent for doing such scripts for things that cannot be run inside functions.