On Thu, 15 Oct 2020 at 14:04, Andy Fan <zhihui.fan1213@gmail.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?
They are exactly the same (I didn't realize this parameter when I sent the email).
I think what Alvaro wants to avoid is taking the AEL in the first
place.
I'm agreed with this, that's why I said "so it can't be as good as what you are doing"
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.
Based on my current knowledge, "detach" will hold an exclusive lock
and it will have higher priority than other waiters. so it has to wait for the lock
holder before it (named as sess 1). and at the same time, block all the other
waiters which are requiring a lock even the lock mode is compatible with session 1.
So "deteach" can probably get its lock in a short time (unless some long transaction
before it). I'm not sure if I have some misunderstanding here.
Overall I'd be +1 for this patch.