Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On Thu, 15 Oct 2020 at 14:04, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
> I think if it is possible to implement the detech with a NoWait option .
>
> ALTER TABLE ... DETACH PARTITION .. [NoWait].
>
> if it can't get the lock, raise "Resource is Busy" immediately, without blocking others.
> this should be a default behavior. If people do want to keep trying, it can set
> a ddl_lock_timeout to 'some-interval', in this case, it will still block others(so it
> can't be as good as what you are doing, but very simple), however the user
> would know what would happen exactly and can coordinate with their
> application accordingly. I'm sorry about this since it is a bit of off-topics
> or it has been discussed already.

How would that differ from setting a low lock_timeout and running the DDL?

I think what Alvaro wants to avoid is taking the AEL in the first
place. When you have multiple long overlapping queries to the
partitioned table, then there be no point in time where there are zero
locks on the table. It does not sound like your idea would help with
that.

David