Обсуждение: 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