Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Hi David/Alvaro:

On Thu, Oct 15, 2020 at 9:09 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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?
>

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.

--
Best Regards
Andy Fan