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

On 5/3/16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> writes:
>> On 4/27/16, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> 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 <column definition> syntax rule 17a says
>
> If a <column constraint definition> is specified that contains
> the <column constraint> NOT NULL, then it is equivalent to the
> following <table constraint definition>:
>
> 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 <Columns, fields, and attributes>:

— 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 <set column not null clause> 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 <set column not null clause> General Rule 1.d:

The following <alter table statement> 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