cataloguing NOT NULL constraints - Mailing list pgsql-hackers

Hello,

Just over a year ago, I posted a patch (based on a previous patch by
Bernd Helmle) that attempted to add pg_constraint rows for NOT NULL
constraints.
http://archives.postgresql.org/message-id/20110707213401.GA27098@alvh.no-ip.org
That patch was rather long and complex, as it tried to handle all the
hairy issues directly with a completely new 'contype' value for NOT NULL
constraints; so the code had to deal with inheritance of constraints,
pg_dump issues, and a lot of nitty-gritty.  In the end it was killed by
a simple realization of Peter Eisentraut's: "Why not just transform
these into the equivalent CHECK constraints instead?"  That ended up
being discussing at length, and this patch, much smaller than the
previous one,  is an attempt to do things that way.

This patch is not final yet, because there are some issues still open;
but the interesting stuff already works.  Simply declaring a column as
NOT NULL creates a CHECK pg_constraint row; similarly, declaring a CHECK
(foo IS NOT NULL) constraint sets the pg_attribute.attnotnull flag.  If
you create a child table, the NOT NULL constraint will be inherited.

One thing that might be of interest is that we accumulate names of
not-nullable columns during parse analysis if they can't be dealt with
immediately; later, MergeAttributes is in charge of walking that list to
determine which columns need to have is_not_null set.  This is a bit
ugly but necessary: consider the following:
CREATE TABLE foo (CHECK (a IS NOT NULL), a INT);
At the point where the CHECK is processed, there is not yet any
ColumnDef node to set is_not_null to.  Also
CREATE TABLE foo (a INT);
CREATE TABLE bar (CHECK (a IS NOT NULL)) INHERITS (foo);
Same thing.
We also handle this correctly:
CREATE TABLE foo (a INT, b INT CHECK (a IS NOT NULL));
i.e. the NOT NULL check is declared on the "wrong" column (this last
command is not actually standard SQL, because column constraints are
supposed to apply only to the current column; but we take it anyway.)

Another thing is that pg_dump now reads attnotnull as always false for
9.3 servers, hoping that there will be a corresponding CHECK constraint.
I think this is okay, because a missing CHECK constraint means that
somebody has been messing with the catalogs and so if it bombs out it's
not our fault.  But if somebody opines differently let me know.

Another point to keep in mind is that I haven't touched syntax
definitions.  This means that ALTER TABLE / SET NOT NULL does not let
you specify a constaint name, so you get an auto-generated name.  I
think this is okay; if you want a different name, use ALTER TABLE / ADD
CONSTRAINT instead.

If you do
CREATE TABLE foo (a INT NOT NULL, CHECK (a IS NOT NULL))
you get two constraints.

Some of the open items here:
* declaring CHECK (foo IS NOT NULL) NO INHERIT doesn't work
  (i.e. the constraint is inherited)
* declaring CHECK (foo IS NOT NUL) NOT VALID doesn't work
  (i.e. the constraint is tested on existing rows).
* I've only handled raw_expr, not cooked_expr, in ColumnDef.  I think
  this means that stuff such as CREATE TABLE AS and CREATE TABLE LIKE
  don't work.  Haven't tested that yet.
* the information_schema needs updating (mainly to remove some UNION
  branches, I think)
* Haven't looked at domains.

--
Álvaro Herrera <alvherre@commandprompt.com>