Re: cataloguing NOT NULL constraints

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).