Subject: Re: Strange presentaion related to inheritance in \d+

From: Alvaro Herrera <[email-masked]>
Date: Mon, 28 Aug 2023 13:36:00 +0200

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.

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?

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.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them."    (Freeman Dyson)