Re: Re: starting to review the Extend NOT NULL representation to pg_constraint patch

Excerpts from Robert Haas's message of mié jun 29 13:07:25 -0400 2011:
> On Wed, Jun 29, 2011 at 12:51 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > Excerpts from Robert Haas's message of lun jun 27 10:35:59 -0400 2011:

> > Interesting.  This whole thing requires quite a bit of rejiggering in
> > the initial transformation phase, I think, but yeah, I see the points
> > here and I will see to them.  Does this mean that "NOT NULL PRIMARY KEY"
> > now behaves differently?  I think it does , because if you drop the PK
> > then the field needs to continue being not null.
> 
> Yeah, I think an implicit not-null because you made it a primary key
> is now different from one that you write out.

Actually, it wasn't that hard, but I'm not really sure I like the
resulting code:

		/*
		 * We want to inherit NOT NULL constraints, but not primary keys.
		 * Since attnotnull flags in pg_attribute stores both, we want to keep only
		 * the attnotnull flag from those columns that have it from NOT NULL
		 * constraints.  To do this, we create a copy of the table's descriptor
		 * and scribble on it by resetting all the attnotnull bits to false, and
		 * the setting them true for columns that appear in a NOT NULL constraint.
		 *
		 * Note: we cannot use CreateTupleDescCopy here, because it'd lose
		 * the atthasdef bits, as well as constraints.
		 */
		tupleDesc = CreateTupleDescCopyConstr(RelationGetDescr(relation));
		constr = tupleDesc->constr;
		parent_nns = GetRelationNotNullConstraints(relation);

		for (parent_attno = 1; parent_attno <= tupleDesc->natts;
			 parent_attno++)
			tupleDesc->attrs[parent_attno - 1]->attnotnull = false;

		foreach (cell, parent_nns)
		{
			NotNullConstraint *constr = lfirst(cell);

			tupleDesc->attrs[constr->attnum - 1]->attnotnull = true;
		}


Here's the simple example (sorry for the spanish):

alvherre=# create table foo (a int primary key, b int not null);
NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito «foo_pkey» para la tabla «foo»
CREATE TABLE
alvherre=# create table bar () inherits (foo);
CREATE TABLE

alvherre=# \d foo
        Tabla «public.foo»
 Columna |  Tipo   | Modificadores 
---------+---------+---------------
 a       | integer | not null
 b       | integer | not null
Índices:
    "foo_pkey" PRIMARY KEY, btree (a)
Número de tablas hijas: 1 (Use \d+ para listarlas.)

alvherre=# \d bar
        Tabla «public.bar»
 Columna |  Tipo   | Modificadores 
---------+---------+---------------
 a       | integer | 
 b       | integer | not null
Hereda: foo


alvherre=# create table baz (a int not null primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito «baz_pkey» para la tabla «baz»
CREATE TABLE
alvherre=# create table qux () inherits (baz);
CREATE TABLE
alvherre=# \d baz
        Tabla «public.baz»
 Columna |  Tipo   | Modificadores 
---------+---------+---------------
 a       | integer | not null
Índices:
    "baz_pkey" PRIMARY KEY, btree (a)
Número de tablas hijas: 1 (Use \d+ para listarlas.)

alvherre=# \d qux
        Tabla «public.qux»
 Columna |  Tipo   | Modificadores 
---------+---------+---------------
 a       | integer | not null
Hereda: baz

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