Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On Tue, Aug 4, 2020 at 7:49 AM Alvaro Herrera <alvherre@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=LJmug@mail.gmail.com
> [3]
> https://postgr.es/m/CA+TgmoY13KQZF-=HNTrt9UYWYx3_oYOQpu9ioNT49jGgiDpUEA@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