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

On 25 June 2011 01:59, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of vie jun 24 19:01:49 -0400 2011:
>> 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.
>

Really? I would expect the reverse, namely that the not-nullness is
part of the PK constraint and dropping the PK *would* then start
allowing NULLs.

I thought that this was one of the reasons for doing this work in the
first place. See http://wiki.postgresql.org/wiki/Todo#CREATE and the
bug reports linked from there.

> 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
>

Yeah, that's one of the bugs linked from the TODO item, that I hoped
this patch would fix.
[http://archives.postgresql.org/pgsql-hackers/2009-04/msg00062.php]

> 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.
>

If by that you mean that you'd end up with 2 pg_constraint entries for
the PK, then that seems wrong to me. I think the not-nullness should
be part of the PK.

Regards,
Dean