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

Excerpts from Robert Haas's message of vie jun 24 19:01:49 -0400 2011:
> On Fri, Jun 24, 2011 at 6:39 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > So remind me ... did we discuss PRIMARY KEY constraints?  Are they
> > supposed to show up as inherited not null rows in the child?  Obviously,
> > they do not show up as PKs in the child, but they *are* not null so my
> > guess is that they need to be inherited as not null as well.  (Right
> > now, unpatched head of course emits the column as attnotnull).
> >
> > In this case, the inherited name (assuming that the child declaration
> > does not explicitely state a constraint name) should be the same as the
> > PK, correct?
>
> I would tend to think of the not-null-ness that is required by the
> primary constraint as a separate constraint, not an intrinsic part of
> the primary key. IOW, if you drop the primary key constraint, IMV,
> that should never cause the column to begin allowing nulls.

Yeah, that is actually what happens. (I had never noticed this, but it seems
obvious in hindsight.)

alvherre=# create table foo (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «foo_pkey» para la tabla «foo»
CREATE TABLE
alvherre=# alter table foo drop constraint foo_pkey;
ALTER TABLE
alvherre=# \d foo
Tabla «public.foo»
Columna | Tipo | Modificadores
---------+---------+---------------
a | integer | not null

What this says is that this patch needs to be creating pg_constraint
entries for all PRIMARY KEY columns too, which answers my question above
quite nicely.

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