Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Amit Langote <amitlangote09@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2021-03-21T19:07:12Z
Lists: pgsql-hackers 
On 2021-Mar-21, Justin Pryzby wrote:

> On Sun, Mar 21, 2021 at 03:22:00PM -0300, Alvaro Herrera wrote:
>
> > So if we do that on DETACH, what would happen on ATTACH?
> 
> Do you mean what happens to the constraint that was already there ?
> Nothing, since it's not ours to mess with.  Checking ImpliedBy() rather than
> equal() doesn't change that.

No, I meant what happens regarding checking existing values in the
table: is the table scanned even if the partition constraint is implied
by existing table constraints?

> I proposed this a few years ago for DETACH (without concurrently), specifically
> to avoid the partition scans.
> https://www.postgresql.org/message-id/20180601221428.GU5164@telsasoft.com
> |The docs say: if detaching/re-attach a partition, should first ADD CHECK to
> |avoid a slow ATTACH operation.  Perhaps DETACHing a partition could
> |implicitly CREATE a constraint which is usable when reATTACHing?

Well, I agree with you that we should add such a constraint.

-- 
Álvaro Herrera       Valdivia, Chile
"The problem with the future is that it keeps turning into the present"
(Hobbes)

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