Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
"David G. Johnston" writes: > This is a bit hard to reason about given that our implementation of > inheritance is non-standard. Yeah, that's a fairly key point. We've solved those problems with respect to inherited CHECK constraints, and it seems like what we ought to do with NOT NULL is make it work the same as CHECK, rather than invent some new concepts. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
On 5/3/16, Tom Lane wrote: > Vitaly Burovoy writes: >> On 4/27/16, Alvaro Herrera wrote: >>> 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). > > This seems to me to be a most curious reading of the standard. > SQL:2011 11.4 syntax rule 17a says > >If a is specified that contains >the NOT NULL, then it is equivalent to the >following : > > CND CHECK ( C IS NOT NULL ) CA > > As a rule, when the SQL spec says "equivalent", they do not mean "it's > sort of like this", they mean the effects are indistinguishable. In > particular, I see nothing whatsoever saying that you're not allowed to > write more than one per column. 1. SQL:2011 4.13 : — If C is a column of a base table, then an indication of whether it is defined as NOT NULL and, if so, the constraint name of the associated table constraint definition. NOTE 41 — This indication and the associated constraint name exist for definitional purposes only and are not exposed through the COLUMNS view in the Information Schema. There is only "constraint name", not "constraint names". 2. SQL:2011 11.15 General Rule 1: ... If the column descriptor of C does not contain an indication that C is defined as NOT NULL, then: And there is no rule 2. I.e. if the column is already set as NOT NULL you can't specify it as NOT NULL again. 3. SQL:2011 11.15 General Rule 1.d: The following is executed without further Access Rule checking: ALTER TABLE TN ADD CONSTRAINT IDCN CHECK ( CN IS NOT NULL ) > So I don't like the proposal to add an attnotnullid column to > pg_attribute. Why and where to place it? > What we'd talked about earlier was converting attnotnull > into, effectively, a hint flag saying that there's at least one NOT NULL > constraint attached to the column. That still seems like a good approach > to me. Ok. But not only NOT NULL constraint, but also non-deferrable PK, CHECK, domains, may be the strictest FK. > When we're actually ready to throw an error for a null value, > we could root through the table's constraint list for a not-null > constraint name to report. attnotnullid is not for reporting, it is for DROP NOT NULL and recreating "CREATE TABLE" statements via pg_dump. > It doesn't matter which one we select, because > constraint application order has never been promised to be deterministic; > and a few extra cycles at that point don't seem like a big problem to me. > > regards, tom lane -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
On Monday, February 8, 2016, Vitaly Burovoy wrote: > > 12. At the same time in (subcl. 4.13) mentioned there can be "at least > one NNC" (may be via inheritance?). > > This is a bit hard to reason about given that our implementation of inheritance is non-standard. Are we close to the standard semantics with regard to this particular dynamic? David J.
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
Quick flyby here... On Tuesday, May 3, 2016, Tom Lane wrote: > Vitaly Burovoy > writes: > > On 4/27/16, Alvaro Herrera > > wrote: > >> 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). Either it's one, or it's not... > This seems to me to be a most curious reading of the standard. > SQL:2011 11.4 syntax rule 17a says > > If a is specified that contains > the NOT NULL, then it is equivalent to the > following : > > CND CHECK ( C IS NOT NULL ) CA > > As a rule, when the SQL spec says "equivalent", they do not mean "it's > sort of like this", they mean the effects are indistinguishable. In > particular, I see nothing whatsoever saying that you're not allowed to > write more than one per column. Does it define how DROP NOT NULL is supposed to behave? I agree that the behavior of a column NNC is identical to a similar constraint defined on the table: but if drop not null doesn't impact table constraints then the concept of perfect equality is already lost. > So I don't like the proposal to add an attnotnullid column to > pg_attribute. What we'd talked about earlier was converting attnotnull > into, effectively, a hint flag saying that there's at least one NOT NULL > constraint attached to the column. > Have we considered making it a table constraint and giving it a name? We already handle that case without difficulty. Not looking for a detailed explanation. David J.
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
Vitaly Burovoy writes: > On 4/27/16, Alvaro Herrera wrote: >> 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). This seems to me to be a most curious reading of the standard. SQL:2011 11.4 syntax rule 17a says If a is specified that contains the NOT NULL, then it is equivalent to the following : CND CHECK ( C IS NOT NULL ) CA As a rule, when the SQL spec says "equivalent", they do not mean "it's sort of like this", they mean the effects are indistinguishable. In particular, I see nothing whatsoever saying that you're not allowed to write more than one per column. So I don't like the proposal to add an attnotnullid column to pg_attribute. What we'd talked about earlier was converting attnotnull into, effectively, a hint flag saying that there's at least one NOT NULL constraint attached to the column. That still seems like a good approach to me. When we're actually ready to throw an error for a null value, we could root through the table's constraint list for a not-null constraint name to report. It doesn't matter which one we select, because constraint application order has never been promised to be deterministic; and a few extra cycles at that point don't seem like a big problem to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
I'm sorry for the late answer. On 4/27/16, Alvaro Herrera wrote: > 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 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 : >>> ... >>> 1) Let R be the 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 >>> ... >>> 1) Let V1 be the value of and let V2 be the value >>> of . >>> ... >>> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
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 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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011
On 2/7/16, Vitaly Burovoy wrote: > 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". It seems the file I attached has more than necessary changes. Please, find a correct patch attached. > === > [1]http://www.postgresql.org/message-id/flat/1343682669-sup-2...@alvh.no-ip.org > [2]http://www.postgresql.org/message-id/20160109030002.GA671800@alvherre.pgsql -- Best regards, Vitaly Burovoy catalog-notnull-2-c477e84_cleaned.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers