Re: cataloguing NOT NULL constraints

On Thu, 2022-08-18 at 11:04 +0200, Alvaro Herrera wrote:
> On 2022-Aug-18, Laurenz Albe wrote:
> > On Wed, 2022-08-17 at 20:12 +0200, Alvaro Herrera wrote:
> > >    Using ALTER TABLE DROP CONSTRAINT works fine, and the 'attnotnull'
> > >    bit is lost when the last one such constraint goes away.
> >
> > Wouldn't it be the correct solution to set "attnotnumm" to FALSE only
> > when the last NOT NULL constraint is dropped?
>
> ... when the last NOT NULL or PRIMARY KEY constraint is dropped.  We
> have to keep attnotnull set when a PK exists even if there's no specific
> NOT NULL constraint.

Of course, I forgot that.
I hope that is not too hard to implement.

> > > 2. If a table has a primary key, and a table is created that inherits
> > >    from it, then the child has its column(s) marked attnotnull but there
> > >    is no pg_constraint row for that.  This is not okay.  But what should
> > >    happen?
> > >
> > >    1. a CHECK(col IS NOT NULL) constraint is created for each column
> > >    2. a PRIMARY KEY () constraint is created
> >
> > I think it would be best to create a primary key constraint on the
> > partition.
>
> Sorry, I wasn't specific enough.  This applies to legacy inheritance
> only; partitioning has its own solution (as you say: the PK constraint
> exists), but legacy inheritance works differently.  Creating a PK in
> children tables is not feasible (because unicity cannot be maintained),
> but creating a CHECK (NOT NULL) constraint is possible.
>
> I think a PRIMARY KEY should not be allowed to exist in an inheritance
> parent, precisely because of this problem, but it seems too late to add
> that restriction now.  This behavior is absurd, but longstanding:

My mistake; you clearly said "inherits".

Since such an inheritance child currently does not have a primary key, you
can insert duplicates. So automatically adding a NUT NULL constraint on the
inheritance child seems the only solution that does not break backwards
compatibility. pg_upgrade would have to be able to cope with that.

Forcing a primary key constraint on the inheritance child could present an
upgrade problem. Even if that is probably a rare and strange case, I don't
think we should risk that. Moreover, if we force a primary key on the
inheritance child, using ALTER TABLE ... INHERIT might have to create a
unique index on the table, which can be cumbersome if the table is large.

So I think a NOT NULL constraint is the least evil.

Yours,
Laurenz Albe