Re: cataloguing NOT NULL constraints

Excerpts from Peter Eisentraut's message of jue ago 04 13:57:54 -0400 2011:
> On tis, 2011-08-02 at 23:40 -0400, Alvaro Herrera wrote:
> > Thanks. I've done the other changes you suggested, but I don't see that
> > it's desirable to have gram.y emit AT_AddConstraint directly. It seems
> > cleaner to be able to turn a NOT NULL constraint into AT_SetNotNull
> > in parse_utilcmd instead. (Maybe I'll have to bite the bullet and make
> > AT_AddConstraint work for not null constraints as well, as part of the
> > larger patch. Not sure.) Currently, the table constraint syntax only
> > lets you do a single constraint at a time, but you can do multiple
> > constraints with the column constraint syntax. I am not sure how hard
> > it is to rework the grammar so that only a single constraint is
> > allowed, but I'm not sure that it's worth the trouble either.

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

Hmm, no, I admit I haven't. The current approach was suggested very
early in the history of this patch. (To be honest I didn't know NOT
NULL constraints where special forms of CHECK constraints until you
mentioned the other day regarding the information schema, and then it
didn't occur to me that it might make sense to represent them as such in
the catalog).

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

Yeah, perhaps you're right. The main reason they were considered
separately is that we wanted to have them to be optimized via
pg_attribute.attnotnull, but my patch does away with the need for that
because it is maintained separately anyway.

Before embarking on rewriting this patch from scratch, I would like to
know what's your opinion (or the SQL standard's) on the fact that this
patch separated the PRIMARY KEY from NOT NULL constraints, so that they
don't act exactly alike (to wit, the not-nullness of a PK does not
inherit while the one from a NOT NULL constraint does).

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support