Re: cataloguing NOT NULL constraints

On 6 August 2011 11:03, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On lör, 2011-08-06 at 08:04 +0100, Dean Rasheed wrote:
>> On 4 August 2011 18:57, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> > Have you considered just cataloging NOT NULL constraints as CHECK
>> > constraints and teaching the reverse parser to convert "x CHECK (x IS
>> > NOT NULL)" to "x NOT NULL".  It seems to me that we're adding a whole
>> > lot of hoopla here that is essentially identical to the existing CHECK
>> > constraint support (it must be, per SQL standard), for no additional
>> > functionality.
>> >
>>
>> With this approach, if the user explicitly wrote "CHECK (x IS NOT
>> NULL)" would it end up setting attnotnull?
>
> Yes, I would assume so.
>
>> Presumably, since the
>> pg_constraint entry would be indistinguishable, it would be difficult
>> to do otherwise. From a performance standpoint that might be a good
>> thing, but it's going to be bad for compatibility.
>
> What compatibility issue are you concerned about specifically?
>
>> What if they wrote "CHECK (NOT x IS NULL)", or "CHECK (x IS DISTINCT
>> FROM NULL)"? How many variants would it reverse parse?
>
> Well, it's already the case that the user can write check constraints in
> any number of forms that have the effect of restricting null values; and
> attnotnull isn't set in those cases.  So in the beginning I'd be quite
> happy if we just recognized CHECK (x IS NOT NULL).
>
> Longer term, I think we could tie this in with more general nullability
> detection.  For example, it is occasionally asked that we can expose
> nullability through views or CREATE TABLE AS.  The SQL standards has
> rules for what cases we should detect (which don't include the two you
> give).
>
>> What would this do to error messages when the constraint is violated?
>
> That's a reasonable concern, although not a fatal one, and it can be
> solved in any case.
>
>> I'm not convinced this simplifies things much; it just moves the
>> complexity elsewhere, and at the cost of losing compatibility with the
>> current behaviour.
>
> No, I don't think this would lose compatibility (except perhaps in cases
> of error message wording).
>

Hmm, maybe my compatibility concerns are not so serious. I'm still
trying to work out exactly what user-visible changes this approach
would lead to. Suppose you had:

CREATE TABLE foo
(
a int NOT NULL,
b int CHECK (b IS NOT NULL),
c int CHECK (NOT c IS NULL)
);

Right now \d gives:

Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer |
c | integer |
Check constraints:
"foo_b_check" CHECK (b IS NOT NULL)
"foo_c_check" CHECK (NOT c IS NULL)

With this approach, one change would be that you'd gain an extra "not
null" in the Modifiers column for "b".

But how many CHECK constraints would show? I guess it would show 3,
although it could be changed to just show 1. But it certainly couldn't
continue to show 2, since nothing in the catalogs could distinguish
the constraints on "a" from those on "b".

Regards,
Dean