Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On 2020-Oct-15, Andy Fan 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.

Hi. I don't think this has been discussed, but it doesn't really solve
the use case I want to -- in many cases where the tables are
continuously busy, this would lead to starvation. I think the proposal
to make the algorithm work with reduced lock level is much more useful.

I think you can already do NOWAIT behavior, with LOCK TABLE .. NOWAIT
followed by DETACH PARTITION, perhaps with a nonzero statement timeout.