From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>, 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:59:05 |
Message-ID: | CAKFQuwZzmOE6PxB9xnRPBB1-H7WieKd8qxTN35hU2dYScjhw1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Quick flyby here...
On Tuesday, May 3, 2016, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com <javascript:;>> writes:
> > On 4/27/16, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com <javascript:;>>
> 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 <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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2016-05-04 06:03:56 | Re: text search changes vs. binary upgrade |
Previous Message | Fabien COELHO | 2016-05-04 05:46:42 | Re: [BUGS] Breakage with VACUUM ANALYSE + partitions |