I'm sorry for the late answer. On 4/27/16, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Vitaly Burovoy wrote: > > Hi, > >> But before starting working on it I had a look at the SQL-2011 >> standard (ISO/IEC 9075-2) 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.
It is just mentioning that CHECK constraints have influence on nullability characteristic, but it differs from NNC. NNC creates CHECK constraint, but not vice versa. You can create several CHECK "col IS NOT NULL" constraints, but only one NNC (several ones by inheritance only?). And DROP NOT NULL should drop only those CHECK that is linked with NNC (and inherited), but no more (full explanation is in my initial letter). >> 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. Ok, I'm ready for a discussion. Two additional columns are necessary: one for pointing to an underlying CHECK constraint (or boolean column whether current CHECK is NNC or not) and second for fast computation of "attnotnull" (which means "nullable characteristic") and ability to skip check if "attnotnull" is set but not triggered (I think it'll improve performance for inherited tables). I think placing the first column (attnotnullid) to pg_attribute is better because you can't have more than one value in it. The second is obviously should be placed in pg_constraint. >> 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. Yes, it has. The PG's composite type is "Row types" (subcl.4.8) in the standard. The standard also differentiates IS [NOT] NULL and IS [NOT] DISTINCT FROM: >>> Subcl. 8.8 <null predicate>: >>> ... >>> 1) Let R be the <row value predicand> and let V be the value of R. >>> 2) Case: >>> a) If V is the null value, then “R IS NULL” is True and >>> the value of “R IS NOT NULL” is False. >>> b) Otherwise: >>> i) The value of “R IS NULL” is >>> Case: >>> 1) If the value of every field of V is the null value, then True. >>> 2) Otherwise, False. >>> ... >>> >>> Subcl. 8.15 <distinct predicate> >>> ... >>> 1) Let V1 be the value of <row value predicand 3> and let V2 be the value >>> of <row value predicand 4>. >>> ... >>> b) If V1 is the null value and V2 is not the null value, or if V1 is not >>> the null value and V2 is the null >>> value, then the result is True. >>> ... In subcl.8.8 "each column" is mentioned, in 8.15 if one of value is the null value and the other is not then nothing more is checked and True is returned. > I think your email was too hard to read because of excessive density, > which would explain the complete lack of response. Hmm. I decided it was "silently approved". =) > I haven't had the chance to work on this topic again, but I encourage you to, > if you have the resources. Thank you, I think I'll find a time for it no earlier than the summer. > (TBH I haven't had the chance to study your proposed design in detail, > either). I hope somebody find a time to study it before someone sends a proposal. -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers