Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Amit Langote <amitlangote09@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2021-03-21T17:54:53Z
Lists: pgsql-hackers 
On Fri, Mar 19, 2021 at 10:57:37AM -0300, Alvaro Herrera wrote:
> > Also, it "fails to avoid" adding duplicate constraints:
> > 
> > Check constraints:
> >     "c" CHECK (i IS NOT NULL AND i > 1 AND i < 2)
> >     "cc" CHECK (i IS NOT NULL AND i >= 1 AND i < 2)
> >     "p1_check" CHECK (true)
> >     "p1_i_check" CHECK (i IS NOT NULL AND i >= 1 AND i < 2)
> 
> Do you mean the "cc" and "p1_i_check" one?  I mean, if you already have

No, I started with c and cc, and it added the broken constraint p1_check (which
you say you've fixed) and the redundant constraint p1_i_check.  I guess that's
what you meant.

> a constraint in the partition that duplicates the partition constraint,
> then during attach we still create our new constraint?  I guess a
> solution to this would be to scan all constraints and see if any equals
> the expression that the new one would have.  Sounds easy enough now that
> write it out loud.

But it looks like DetachAddConstraintIfNeeded already intended to do that:

+            if (equal(constraintExpr, thisconstr))                                                                                                                                                                     
+                    return;                                                                                                                                                                                            

Actually, it appears your latest notpatch resolves both these issues.
But note that it doesn't check if an existing constraint "implies" the new
constraint - maybe it should.

-- 
Justin

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