From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Make PG's "NOT NULL"s and attnotnull ("is_nullable") conform to SQL-2011 |
Date: | 2016-05-04 05:19:37 |
Message-ID: | 17684.1462339177@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2016-05-04 05:46:42 | Re: [BUGS] Breakage with VACUUM ANALYSE + partitions |
Previous Message | Euler Taveira | 2016-05-04 05:03:55 | Re: what to revert |