Re: cataloguing NOT NULL constraints

On Sat, 5 Aug 2023 at 18:37, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Yeah, something like that. However, if the child had a NOT NULL
> constraint of its own, then it should not be deleted when the
> PK-on-parent is, but merely marked as no longer inherited. (This is
> also what happens with a straight NOT NULL constraint.) I think what
> this means is that at some point during the deletion of the PK we must
> remove the dependency link rather than letting it be followed. I'm not
> yet sure how to do this.
>

I'm not sure that adding that new dependency was the right thing to
do. I think perhaps this could just be made to work using conislocal
and coninhcount to track whether the child constraint needs to be
deleted, or just updated.

> Anyway, I was at the same time fixing the other problem you reported
> with inheritance (namely, adding a PK ends up with the child column
> being marked NOT NULL but no corresponding constraint).
>
> At some point I wondered if the easy way out wouldn't be to give up on
> the idea that creating a PK causes the child columns to be marked
> not-nullable. However, IIRC I decided against that because it breaks
> restoring of old dumps, so it wouldn't be acceptable.
>
> To make matters worse: pg_dump creates the PK as
>
> ALTER TABLE ONLY parent ADD PRIMARY KEY ( ... )
>
> note the ONLY there. It seems I'm forced to cause the PK to affect
> children even though ONLY is given. This is undesirable but I don't see
> a way out of that.
>
> It is all a bit of a rat's nest.
>

I wonder if that could be made to work in the same way as inherited
CHECK constraints -- dump the child's inherited NOT NULL constraints,
and then manually update conislocal in pg_constraint.

Regards,
Dean