Re: possible bug with inheritance?

Wow, you are right that this is really weird.  I think the fundamental
issue is that PRIMARY KEY does not pass down to the child (and hence NOT
NULL doesn't either), while NOT NULL does pass to the child.  A larger
question is why PRIMARY KEY doesn't pass to the child.  If there is a
good reason for that, there is a good reason that the NOT NULL that is
part of PRIMARY KEY doesn't pass to the child.

I think the ALTER TABLE manual page might have a hint:

    The columns must have matching data types, and if they have
    NOT NULL constraints in the parent then they must also have
    NOT NULL constraints in the child.

What I think is happening is that the NOT NULL specification is a
qualification of what data values can be returned from the parent (i.e.
no nulls), and that has to be passed to the child so a query on the
parent with a not null column doesn't return null values from a child
that contains nulls in that column.

For primary key, there is no enforcement of the primary key, e.g.:

    test=> CREATE TABLE parent (name TEXT);
    CREATE TABLE
    test=> CREATE TABLE child (age INT) inherits (parent) ;
    CREATE TABLE
    test=> ALTER TABLE parent ADD primary KEY (name);
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
    "parent_pkey" for table "parent"
    ALTER TABLE
    test=> INSERT INTO parent (name) VALUES ('a');
    INSERT 0 1
    test=> INSERT INTO child (name) VALUES ('a');
    INSERT 0 1
    test=> SELECT * FROM parent;
     name
    ------
     a
     a
    (2 rows)

So, it seems like this is the ugly truth of our inheritance limitations
with primary key, and unless we can fix the primary key issues with
inheritance, our current behavior is the more predictable we can hope for.

---------------------------------------------------------------------------

A. Kretschmer wrote:
> Hi,
>
> Our documentation says:
> "All check constraints and not-null constraints on a parent table are
> automatically inherited by its children."
>
> Okay, this works as expected:
>
> test=# create table parent (name text primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
> CREATE TABLE
> test=*# create table child (age int) inherits (parent) ;
> CREATE TABLE
> test=*# \d child
>      Table "public.child"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  name   | text    | not null
>  age    | integer |
> Inherits: parent
>
>
> Nice, the 'not null' constraint is in the child-table.
>
>
> test=*# rollback;
> ROLLBACK
> test=# create table parent (name text);
> CREATE TABLE
> test=*# create table child (age int) inherits (parent) ;
> CREATE TABLE
> test=*# alter table parent add primary key (name);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
> ALTER TABLE
> test=*# \d child
>      Table "public.child"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  name   | text    |
>  age    | integer |
> Inherits: parent
>
>
> Doh, there isn't the 'not null' - modifier. The parent-table contains
> this modifier as part of the primary key - definition.
>
>
> Other test:
>
> test=# create table parent (name text);
> CREATE TABLE
> test=*# create table child (age int) inherits (parent) ;
> CREATE TABLE
> test=*# alter table parent alter column name set not null;
> ALTER TABLE
> test=*# \d child
>      Table "public.child"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  name   | text    | not null
>  age    | integer |
> Inherits: parent
>
>
> Okay, we have again the 'not null'.
>
>
> I think, that's not really clearly. In some case the 'not null' -
> constraint are in the child-table, in other case they are not.
>
> Version 8.4.2.
>
> Bug, feature? What have i overlooked?
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +