Re: cataloguing NOT NULL constraints

On Wed, 12 Jul 2023 at 18:11, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> v13, because a conflict was just committed to alter_table.sql.
>
> Here I also call out the relcache.c change by making it a separate
> commit. I'm likely to commit it that way, too. To recap: the change is
> to have a partitioned table's index list include the primary key, even
> when said primary key is marked invalid. This turns out to be necessary
> for the currently proposed pg_dump strategy to work; if this is not in
> place, attaching the per-partition PK indexes to the parent index fails
> because it sees that the columns are not marked NOT NULL.
>

Hmm, looking at that change, it looks a little ugly. I think someone
reading that code in the future will have no idea why it's including
some invalid indexes, and not others.

> There are no other changes from v12. One thing I should probably get
> to, is fixing the constraint name comparison code in pg_dump. Right now
> it's a bit dumb and will get in silly trouble with overlength
> table/column names (nothing that would actually break, just that it will
> emit constraint names when there's no need to.)
>

Yeah, that seems a bit ugly. Presumably, also, if something like a
column rename happens, the constraint name will no longer match.

I see that it's already been discussed, but I don't like the fact that
there is no way to get hold of the new constraint names in psql. I
think for the purposes of dropping named constraints, and also
possible future stuff like NOT VALID / DEFERRABLE constraints, having
some way to get their names will be important.

Something else I noticed is that the result from "ALTER TABLE ...
ALTER COLUMN ... DROP NOT NULL" is no longer easily predictable -- if
there are multiple NOT NULL constraints on the column, it just drops
one (chosen at random?) and leaves the others. I think that it should
either drop all the constraints, or throw an error. Either way, I
would expect that if DROP NOT NULL succeeds, the result is that the
column is nullable.

Regards,
Dean