I've been working on the ability to detach a partition from a partitioned table, without causing blockages to concurrent activity. I think this operation is critical for some use cases.
This would be a very great feature. When we can't handle thousands of partitions
very well, and user agree to detach some old partitions automatically, the blocking
issue here would be a big blocker for this solution. Thanks for working on this!
There was a lot of great discussion which ended up in Robert completing a much sought implementation of non-blocking ATTACH. DETACH was discussed too because it was a goal initially, but eventually dropped from that patch altogether. Nonetheless, that thread provided a lot of useful input to this implementation. Important ones:
Attached is a patch that implements ALTER TABLE ... DETACH PARTITION .. CONCURRENTLY.
In the previous thread we were able to implement the concurrent model without the extra keyword. For this one I think that won't work; my implementation works in two transactions so there's a restriction that you can't run it in a transaction block. Also, there's a wait phase that makes it slower than the non-concurrent one. Those two drawbacks make me think that it's better to keep both modes available, just like we offer both CREATE INDEX and CREATE INDEX CONCURRENTLY.
Why two transactions? The reason is that in order for this to work, we make a catalog change (mark it detached), and commit so that all concurrent transactions can see the change. A second transaction waits for anybody who holds any lock on the partitioned table and grabs Access Exclusive on the partition (which now no one cares about, if they're looking at the partitioned table), where the DDL action on the partition can be completed.
ALTER TABLE is normally unable to run in two transactions. I hacked it (0001) so that the relation can be closed and reopened in the Exec phase (by having the rel as part of AlteredTableInfo: when ATRewriteCatalogs returns, it uses that pointer to close the rel). It turns out that this is sufficient to make that work. This means that ALTER TABLE DETACH CONCURRENTLY cannot work as part of a multi-command ALTER TABLE, but that's alreay enforced by the grammar anyway.
DETACH CONCURRENTLY doesn't work if a default partition exists. It's just too problematic a case; you would still need to have AEL on the default partition.
I haven't yet experimented with queries running in a standby in tandem with a detach.
