Message-ID: <bd5b7cd7c5dc5565736bb1492e8d7294656e90f7.camel@cybertec.at>
Subject: Re: cataloguing NOT NULL constraints
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: Thu, 18 Aug 2022 17:00:52 +0200
In-Reply-To: <20220818090425.37co7pfwysbervgj@alvherre.pgsql>
References: <20220818090425.37co7pfwysbervgj@alvherre.pgsql>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/bd5b7cd7c5dc5565736bb1492e8d7294656e90f7.camel%40cybertec.at>
Precedence: bulk

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