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/
