Horton

Re: cataloguing NOT NULL constraints

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, pgsql-hackers@lists.postgresql.org
Date: 2023-08-11T13:54:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Revert structural changes to not-null constraints

  2. Fix inconsistencies in error messages

  3. Disallow direct change of NO INHERIT of not-null constraints

  4. Disallow NO INHERIT not-null constraints on partitioned tables

  5. Better handle indirect constraint drops

  6. Don't try to assign smart names to constraints

  7. Fix restore of not-null constraints with inheritance

  8. ATTACH PARTITION: Don't match a PK with a UNIQUE constraint

  9. Fix propagating attnotnull in multiple inheritance

  10. Check stack depth in new recursive functions

  11. Move privilege check to the right place

  12. Update information_schema definition for not-null constraints

  13. Fix not-null constraint test

  14. Disallow changing NO INHERIT status of a not-null constraint

  15. Catalog not-null constraints

  16. parallel_schedule: add comment on event_trigger test dependency

  17. Revert "Catalog NOT NULL constraints" and fallout

  18. Adjust contrib/sepgsql regression test expected outputs.

  19. Fix table name clash in recently introduced test

  20. Catalog NOT NULL constraints

  21. Change the rules for inherited CHECK constraints to be essentially the same

Attachments

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)