Re: cataloguing NOT NULL constraints

On Fri, 11 Aug 2023 at 14:54, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Right, in the end I got around to that point of view. I abandoned the
> idea of adding these dependency links, and I'm back at relying on the
> coninhcount/conislocal markers. But there were a couple of bugs in the
> accounting for that, so I've fixed some of those, but it's not yet
> complete:
>
> - ALTER TABLE parent ADD PRIMARY KEY
> needs to create NOT NULL constraints in children. I added this, but
> I'm not yet sure it works correctly (for example, if a child already
> has a NOT NULL constraint, we need to bump its inhcount, but we
> don't.)
> - ALTER TABLE parent ADD PRIMARY KEY USING index
> Not sure if this is just as above or needs separate handling
> - ALTER TABLE DROP PRIMARY KEY
> needs to decrement inhcount or drop the constraint if there are no
> other sources for that constraint to exist. I've adjusted the drop
> constraint code to do this.
> - ALTER TABLE INHERIT
> needs to create a constraint on the new child, if parent has PK. Not
> implemented
> - ALTER TABLE NO INHERIT
> needs to delink any constraints (decrement inhcount, possibly drop
> the constraint).
>

I think perhaps for ALTER TABLE INHERIT, it should check that the
child has a NOT NULL constraint, and error out if not. That's the
current behaviour, and also matches other constraints types (e.g.,
CHECK constraints).

More generally though, I'm worried that this is starting to get very
complicated. I wonder if there might be a different, simpler approach.
One vague idea is to have a new attribute on the column that counts
the number of constraints (local and inherited PK and NOT NULL
constraints) that make the column not null.

Something else I noticed when reading the SQL standard is that a
user-defined CHECK (col IS NOT NULL) constraint should be recognised
by the system as also making the column not null (setting its
"nullability characteristic" to "known not nullable"). I think that's
more than just an artefact of how they say NOT NULL constraints should
be implemented, because the effect of such a CHECK constraint should
be exposed in the "columns" view of the information schema -- the
value of "is_nullable" should be "NO" if the column is "known not
nullable".

In this sense, the standard does allow multiple not null constraints
on a column, independently of whether the column is "defined as NOT
NULL". My understanding of the standard is that ALTER COLUMN ...
SET/DROP NOT NULL change whether or not the column is "defined as NOT
NULL", and manage a single system-generated constraint, but there may
be any number of other user-defined constraints that also make the
column "known not nullable", and they need to be tracked in some way.

I'm also wondering whether creating a pg_constraint entry for *every*
not-nullable column is actually going too far. If we were to
distinguish between "defined as NOT NULL" and being not null as a
result of one or more constraints, in the way that the standard seems
to suggest, perhaps the former (likely to be much more common) could
simply be a new attribute stored on the column. I think we actually
only need to create pg_constraint entries if a constraint name or any
additional constraint properties such as NOT VALID are specified. That
would lead to far fewer new constraints, less catalog bloat, and less
noise in the \d output.

Regards,
Dean