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
