Re: ALTER TABLE ONLY .. DROP CONSTRAINT on partitioned tables

On Mon, Sep 30, 2024 at 6:01 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Hello,
>
> On 2024-Sep-27, Amit Langote wrote:
>
> > On Fri, Sep 27, 2024 at 2:52 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> > > While studying a review note from Jian He on not-null constraints, I
> > > came across some behavior introduced by commit 9139aa19423b[1] that I
> > > think is mistaken.
>
> > Yeah, I don’t quite recall why I thought the behavior for both ADD and
> > DROP had to be the same. I went back and reviewed the thread, trying
> > to understand why DROP was included in the decision, but couldn’t find
> > anything that explained it. It also doesn’t seem to be related to the
> > pg_dump issue that was being discussed at the time.
>
> Right.
>
> > So, I think you might be right that the restriction on DROP is
> > overkill, and we should consider removing it, at least in the master
> > branch.
>
> Thanks for looking! I have pushed the removal now.
>

works fine with check constraint, drop not null.
but seems not with primary key/unique constraint.

drop table if exists idxpart, idxpart0 cascade;
create table idxpart (a int not null primary key, constraint nc check
(a > 1)) partition by range (a);
create table idxpart0 (a int not null primary key, constraint nc check (a > 1));
alter table idxpart attach partition idxpart0 for values from (0) to (1000);
alter table only idxpart drop constraint nc;
alter table only idxpart drop constraint idxpart_pkey;

in the above case:
alter table only idxpart drop constraint idxpart_pkey;
is logical equivalent to
alter table idxpart drop constraint idxpart_pkey;

not sure this is desired behavior.