Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011 - Mailing list pgsql-hackers

Hello, Hackers!

TODO list has an entry "Move NOT NULL constraint information to
pg_constraint" with four links and without two with the newest
work[1][2].

I rebased the patch from [2] (in attachment). At least it applies
cleanly on top of c477e84fe2471cb675234fce75cd6bb4bc2cf481 and does
not generate a core dump during "make check". There are no tests for
it and it fails "make check" (by difference) which leads inability to
run "make check-world".


But before starting working on it I had a look at the SQL-2011
standard (ISO/IEC 9075-2)[3] and found that:

1. A name for a "NOT NULL" constraint <NNC> can be given by a table
definition (subcl. 11.4, "Format"->"column constraint definition").
2. The standard splits NNC and CHECK constraints (subcl. 11.4,
"Format"-> "column constraint")
3. A _descriptor_ of a column must include a "nullability
characteristic" (subcl. 11.4 GR 4.d and 4.L).
4. At the same time the _descriptor_ of the column must include an
_indication_ whether the column is defined as "NOT NULL" or not (near
subcl. 4.13 Note 41) and a name of the constraint.
5. "Nullability characteristic" is set only by nondeferrable
constraints (subcl. 4.13) but there can be several constraints which
have influence on it (e.g. PK + CHECK but without NNC => is_nullable).
6. If an SQL-implementation can deduce CHECK constraint and/or DOMAIN
constraint to "column IS NULL" can never be TRUE it can claim support
for the feature "T101. Enhanced nullability determination" (subcl.
4.13, Note 38). See also (subcl. 6.35 SR 4.b)
7. NNC can be "deferrable" via "constraint characteristics" which are
set in addition to "column constraint" (i.e. the column can be "NOT
NULL" _and_ not "is_nullable").
8. NNC is an _equivalent_ to a table CHECK constraint (subcl. 11.4 SR 17.a).
9. There is no way to set NNC for a table except "... ALTER COLUMN ...
SET NOT NULL" clause (subcl. 11.6).
10. "... ALTER COLUMN ... SET NOT NULL" clause doesn't allow to
specify name of the constraint (subcl. 11.15).
11. There is no way to specify more than one NNC per column via "SET
NOT NULL" (subcl. 11.15 GR 1)
12. At the same time in (subcl. 4.13) mentioned there can be "at least
one NNC" (may be via inheritance?).
13. "... ALTER COLUMN ... SET NOT NULL" _must_ add a table CHECK
constraint (subcl. 11.15 GR 1.d).
14. "DROP NOT NULL" clause must drop cascading all NNC, but leave
other constraints that may affect "nullability characteristic" (subcl.
11.16 GR 1.*).
15. PK can have NULL values if its "constraint characteristics" is "deferrable".
16. There is no mention of "NOT NULL" constraints in the (ISO/IEC
9075-11) at all.

===
Shortcuts:
subcl: Subclause
GR: General Rule
SR: Syntax Rule


Conclusion:
I. NNC implies CHECK constraints (p.13) but it is not a constraint
itself (p.4, p.9, p.13, p.16; opposite to p.2)
II. CHECK constraint does not imply NNC but it has influence on
"attnotnull" (deep check of CHECK constraints allows to move the
feature "T101" to the supporting features list).
III. "pg_attribute" table should have an "attnotnullid oid" as an
indicator of "NOT NULL" (p.4) and points to a CHECK constraint; It is
in addition to a "Nullability characteristic" "attnotnull" (p.3).
IV. "pg_constraint" should have a column "connotnullkey int2[]" as a
"list of the nullable columns" which references to
"pg_attribute.attnum" for fast checking whether a column is still
nullable after deleting/updating constraints or not. Array is
necessary for cases like "CHECK ((col1 IS NOT NULL) AND (col2 IS NOT
NULL))" and for nondeferrable PKs.
V. Inherited tables inherit CHECK constraints (from p.I), but that
constraints are not written to the "attnotnullid" (from p.II) even if
they have NULL values.
VI. "pg_constraint" _can_ have a column "connotnullpure BOOLEAN" to
skip CHECK constraints which define "NOT NULL" only (for one or
several columns) because a row has already checked for NULLs via
"attnotnull" just before ExecRelCheck is executed.
VII. "connotnullkey" is NULL for deferrable and "NOT VALID" constraints.
VIII. "connotnullkey" is recalculated after "VALIDATE CONSTRAINT" is done.
IX. "attnotnull" is recalculated if a constraint with nonempty
"connotnullkey" is inserted, deleted or "connotnullkey" is changed.
X. Pure CHECK constraint doesn't do full scan if the appropriate
table's column(s) has(ve) "attnotnull" as "TRUE".
XI. pg_dump shows "NOT NULL" iff "attnotnullid IS NOT NULL" and skip
CHECK statement with oid matched with attnotnullid.

What do you think about that design?

P.S.:
Since the SQL standard defines that "col NOT NULL" as an equivalent to
"CHECK (col IS NOT NULL)" (p.8) what to do with that behavior:

postgres=# create type t as (x int);
CREATE TYPE
postgres=# SELECT v, v IS NOT NULL AS should_be_in_table FROM
(VALUES('(1)'::t),('()'),(NULL)) AS x(v);
  v  | should_be_in_table
-----+--------------------
 (1) | t
 ()  | f
     | f
(3 rows)

"attnotnull" in such case is stricter, like "CHECK (col IS DISTINCT FROM NULL)".

Should such values (with NULL in each attribute of a composite type)
violate NOT NULL constraints?

===
[1]http://www.postgresql.org/message-id/flat/1343682669-sup-2532@alvh.no-ip.org
[2]http://www.postgresql.org/message-id/20160109030002.GA671800@alvherre.pgsql
[3]http://www.wiscorp.com/sql20nn.zip