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
