Thread: cataloguing NOT NULL constraints

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>

Alvaro Herrera <alvherre@commandprompt.com> wrote:
> 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.
Don't forget the peculiarities of columns with record types. 
Semantically, these three things are different:
colname rectype not null
colname rectype check (colname is not null)
colname rectype check (not (colname is null))
test=# create table t (id int primary key, v1 a not null, v2 a check
(v2 is not null), v3 a check (not (v3 is null)));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
test=# insert into t values (1, (1,1), (1,1), (1,1));
INSERT 0 1
test=# insert into t values (2, (1, null), (1, null), (1,1));
ERROR:  new row for relation "t" violates check constraint
"t_v2_check"
DETAIL:  Failing row contains (2, (1,), (1,), (1,1)).
test=# insert into t values (3, (1, null), (1,1), (1, null));
INSERT 0 1
test=# insert into t values (4, (null, null), (1,1), (1, null));
INSERT 0 1
test=# insert into t values (5, (null, null), (1,1), (null, null));
ERROR:  new row for relation "t" violates check constraint
"t_v3_check"
DETAIL:  Failing row contains (5, (,), (1,1), (,)).
-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Don't forget the peculiarities of columns with record types. 
I forgot to include the type creation in the example:
test=# create type a as (a1 int, a2 int);
CREATE TYPE
-Kevin

Excerpts from Kevin Grittner's message of jue ago 02 10:48:02 -0400 2012:
>
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>
> > Don't forget the peculiarities of columns with record types.
>
> I forgot to include the type creation in the example:
>
> test=# create type a as (a1 int, a2 int);
> CREATE TYPE

Thanks for the example.  After playing with this, I think that a NOT
NULL constraint attached to a column with a composite type is equivalent
to a CHECK (col IS DISTINCT FROM NULL); at least they seem to behave
identically.  Is that what you would expect?

This seems a bit complicated to handle with the way I'm doing things
today; at parse analysis time, when my current code is creating the
check constraint, we don't know anything about the type of the column
IIRC.  Maybe I will have to delay creating the constraint until
execution.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> I think that a NOT NULL constraint attached to a column with a
> composite type is equivalent to a CHECK (col IS DISTINCT FROM
> NULL); at least they seem to behave identically.  Is that what you
> would expect?
I had not thought about that, but now that you point it out I think
that interpretation makes more sense than any other.  In a quick
test they behaved identically for me.
> This seems a bit complicated to handle with the way I'm doing
> things today; at parse analysis time, when my current code is
> creating the check constraint, we don't know anything about the
> type of the column IIRC.  Maybe I will have to delay creating the
> constraint until execution.
Why?  CHECK (col IS DISTINCT FROM NULL) works correctly for *any*
type, doesn't it?
-Kevin