Re: Strange presentaion related to inheritance in \d+

At Mon, 28 Aug 2023 13:36:00 +0200, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in 
> On 2023-Aug-28, Kyotaro Horiguchi wrote:
> 
> > But with these tables:
> > 
> > create table p (a int, b int not null default 0);
> > create table c1 (a int, b int not null NO INHERIT default 1) inherits (p);
> > 
> > I get:
> > 
> > > Not-null constraints:
> > >    "c1_b_not_null" NOT NULL "b" *NO INHERIT*
> > 
> > Here, "NO INHERIT" is mapped from connoinherit, and conislocal and
> > "coninhcount <> 0" align with "local" and "inherited". For a clearer
> > picuture, those values for c1 are as follows.
> 
> Hmm, I think the bug here is that we let you create a constraint in c1
> that is NOINHERIT.  If the parent already has one INHERIT constraint
> in that column, then the child must have that one also; it's not
> possible to have both a constraint that inherits and one that doesn't.

Yeah, I had the same question about the coexisting of the two.

> I understand that there are only three possibilities for a NOT NULL
> constraint in a column:
> 
> - There's a NO INHERIT constraint.  A NO INHERIT constraint is always
>   defined locally in that table.  In this case, if there is a parent
>   relation, then it must either not have a NOT NULL constraint in that
>   column, or it may also have a NO INHERIT one.  Therefore, it's
>   correct to print NO INHERIT and nothing else.  We could also print
>   "(local)" but I see no point in doing that.
> 
> - A constraint comes inherited from one or more parent tables and has no
>   local definition.  In this case, the constraint always inherits
>   (otherwise, the parent wouldn't have given it to this table).  So
>   printing "(inherited)" and nothing else is correct.
> 
> - A constraint can have a local definition and also be inherited.  In
>   this case, printing "(local, inherited)" is correct.
> 
> Have I missed other cases?

Seems correct. I don't see another case given that NO INHERIT is
inhibited when a table has an inherited constraint.

> The NO INHERIT bit is part of the syntax, which is why I put it in
> uppercase and not marked it for translation.  The other two are
> informational, so they are translatable.

Given the conditions above, I agree with you.

Attached is the initial version of the patch. It prevents "CREATE
TABLE" from executing if there is an inconsisntent not-null
constraint.  Also I noticed that "ALTER TABLE t ADD NOT NULL c NO
INHERIT" silently ignores the "NO INHERIT" part and fixed it.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center