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