Re: cataloguing NOT NULL constraints

On 2023-Aug-05, Dean Rasheed wrote:

> On Sat, 5 Aug 2023 at 18:37, Alvaro Herrera <alvherre@alvh.no-ip.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)