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

Vitaly Burovoy wrote:

Hi,

> 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")

Point 2 is where things differ from what I remember; my (possibly
flawed) understanding was that there's no difference between those
things.  Many (maybe all) of the things from this point on are probably
fallout from that one change.

> 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.

I think these points warrant some more consideration.  I don't like the
idea that pg_attribute and pg_constraint are both getting considerably
bloated to support this.

> 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?

I wonder if the standard has a concept of null composite values.  If
not, then there is no difference between IS NOT NULL and IS DISTINCT
FROM NULL, which explains why they define NNC in terms of the former.


I think your email was too hard to read because of excessive density,
which would explain the complete lack of response.  I haven't had the
chance to work on this topic again, but I encourage you to, if you have
the resources.  (TBH I haven't had the chance to study your proposed
design in detail, either).

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services