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-04-21T22:12:48Z
Lists: pgsql-hackers 
On 2021-Apr-10, Justin Pryzby wrote:

> If it *implies* the partition constraint, then it's at least as tight (and
> maybe tighter), yes ?
> 
> I think you're concerned with the case that someone has a partition with
> "tight" bounds like (a>=200 and a<300) and a check constraint that's "less
> tight" like (a>=100 AND a<400).  In that case, the loose check constraint
> doesn't imply the tighter partition constraint, so your patch would add a
> non-redundant constraint.

... yeah, you're right, we can do as you suggest and it seems an
improvement.  I verified, as is obvious in hindsight, that the existing
constraint makes a future ATTACH of the partition with the same bounds
as before not scan the partition.

I pushed the patch with a small change:
PartConstraintImpliedByRelConstraint wants the constraint in
implicit-AND form (that is, a list) which is what we already have, so we
can postpone make_ands_explicit() until later.

Pushed, thanks,

-- 
Álvaro Herrera       Valdivia, Chile

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