Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

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