Re: Re: starting to review the Extend NOT NULL representation to pg_constraint patch - Mailing list pgsql-hackers

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_attributestores 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
attnotnullbits 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 nullb       | 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