Horton

cataloguing NOT NULL constraints

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

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-08-17T18:12:49Z
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

I've been working on having NOT NULL constraints have pg_constraint
rows.

Everything is working now.  Some things are a bit weird, and I would
like opinions on them:

1. In my implementation, you can have more than one NOT NULL
   pg_constraint row for a column.  What should happen if the user does
   ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL;
   ?  Currently it throws an error about the ambiguity (ie. which
   constraint to drop).
   Using ALTER TABLE DROP CONSTRAINT works fine, and the 'attnotnull'
   bit is lost when the last one such constraint goes away.

2. If a table has a primary key, and a table is created that inherits
   from it, then the child has its column(s) marked attnotnull but there
   is no pg_constraint row for that.  This is not okay.  But what should
   happen?

   1. a CHECK(col IS NOT NULL) constraint is created for each column
   2. a PRIMARY KEY () constraint is created

Note that I've chosen not to create CHECK(foo IS NOT NULL) pg_constraint
rows for columns in the primary key, unless an explicit NOT NULL
declaration is also given.  Adding them would be a very easily solution
to problem 2 above, but ISTM that such constraints would be redundant
and not very nice.

After gathering input on these thing, I'll finish the patch and post it.
As far as I can tell, everything else is working (except the annoying
pg_dump tests, see below).

Thanks

Implementation notes:

In the current implementation I am using CHECK constraints, so these
constraints are contype='c', conkey={col} and the corresponding
expression.

pg_attribute.attnotnull is still there, and it is set true when at least
one "CHECK (col IS NOT NULL)" constraint (and it's been validated) or
PRIMARY KEY constraint exists for the column.

CHECK constraint names are no longer "tab_col_check" when the expression
is CHECK (foo IS NOT NULL).  The constraint is now going to be named
"tab_col_not_null"

If you say CREATE TABLE (a int NOT NULL), you'll get a CHECK constraint
printed by psql: (this is a bit more noisy that previously and it
changes a lot of regression tests output).

55489 16devel 1776237=# create table tab (a int not null);
CREATE TABLE
55489 16devel 1776237=# \d tab
                    Tabla «public.tab»
 Columna │  Tipo   │ Ordenamiento │ Nulable  │ Por omisión 
─────────┼─────────┼──────────────┼──────────┼─────────────
 a       │ integer │              │ not null │ 
Restricciones CHECK:
    "tab_a_not_null" CHECK (a IS NOT NULL)


pg_dump no longer prints NOT NULL in the table definition; rather, the
CHECK constraint is dumped as a separate table constraint (still within
the CREATE TABLE statement though).  This preserves any possible
constraint name, in case one was specified by the user at creation time.

In order to search for the correct constraint for each column for
various DDL actions, I just inspect each pg_constraint row for the table
and match conkey and the CHECK expression.  Some things would be easier
with a new pg_attribute column that carries a pg_constraint.oid of the
constraint for that column; however, that seems to be just catalog bloat
and is not normalized, so I decided not to do it.

Nice side-effect: if you add CHECK (foo IS NOT NULL) NOT VALID, and
later validate that constraint, the attnotnull bit becomes set.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/