Re: cataloguing NOT NULL constraints

On 2023-Aug-05, Dean Rasheed wrote:

> 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.

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 also need to add tests for those scenarios, because I think there
aren't any for most of them.

There's also another a pg_upgrade problem: we now get spurious ALTER
TABLE SET NOT NULL commands in a dump after pg_upgrade for the columns
that get the constraint from a primary key. (This causes a pg_upgrade
test failure). I need to adjust pg_dump to suppress those; I think
something like flagInhTables would do.

(I had mentioned that I needed to move code from dropconstraint_internal
to RemoveConstraintById. However, now I can't figure out exactly what
case was having a problem, so I've left it alone.)

Here's v17, which is a step forward, but several holes remain.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)