On Tue, Aug 4, 2020 at 7:49 AM Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
> 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: > > [1] > https://postgr.es/m/CA+TgmoYg4x7AH=_QSptvuBKf+3hUdiCa4frPkt+RvXZyjX1n=w...@mail.gmail.com > [2] > https://postgr.es/m/CA+TgmoaAjkTibkEr=xJg3ndbRsHHSiYi2SJgX69MVosj=lj...@mail.gmail.com > [3] > https://postgr.es/m/CA+TgmoY13KQZF-=hntrt9uywyx3_oyoqpu9iont49jggidp...@mail.gmail.com > > 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. > > -- > Álvaro Herrera > -- Best Regards Andy Fan
