Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Andy Fan <zhihui.fan1213@gmail.com>

View thread View on postgr.es
From: Andy Fan <zhihui.fan1213@gmail.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2020-10-15T01:04:24Z
Lists: pgsql-hackers 
Hi Alvaro:

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

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.

-- 
Best Regards
Andy Fan

Commits

  1. Make detach-partition-concurrently-4 less timing sensitive

  2. Improve documentation on DETACH PARTITION lock levels

  3. Track detached partitions more accurately in partdescs

  4. Allow a partdesc-omitting-partitions to be cached

  5. Fix relcache inconsistency hazard in partition detach

  6. Don't add a redundant constraint when detaching a partition

  7. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY

  8. Let ALTER TABLE Phase 2 routines manage the relation pointer

  9. Check default partitions constraints while descending