From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: cataloguing NOT NULL constraints
Date: Thu, 18 Aug 2022 17:00:52 +0200
Message-ID: <bd5b7cd7c5dc5565736bb1492e8d7294656e90f7.camel@cybertec.at> (raw)
In-Reply-To: <20220818090425.37co7pfwysbervgj@alvherre.pgsql>
References: <20220818090425.37co7pfwysbervgj@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: laurenz.albe@cybertec.at, alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org
Subject: Re: cataloguing NOT NULL constraints
In-Reply-To: <bd5b7cd7c5dc5565736bb1492e8d7294656e90f7.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox