Thread: Problem with REFERENCES on INHERITS

Hi.

I have encountered a problem with references when using INHERITS (on
Postgres 9.1/9.2).  Could someone please explain why this occurs.

Consider this example.

CREATE TABLE primate
(
   id SERIAL PRIMARY KEY,
   name TEXT,
   tale TEXT
);


CREATE TABLE chimp
(
    human_friend TEXT
) INHERITS(primate);

INSERT INTO chimp(name, tale, human_friend) VALUES
    ('Cheetah', 'Curly', 'Tarzan');

INSERT INTO primate(name, tale) VALUES
    ('King Julien', 'Move it');

SELECT * FROM primate;

==>
 id |    name     |  tale
----+-------------+---------
  2 | King Julien | Move it
  1 | Cheetah     | Curly
(2 rows)

CREATE TABLE banana_stash
(
    id SERIAL,
    primate_id INTEGER REFERENCES primate(id),
    qty INTEGER
);

INSERT INTO banana_stash(primate_id, qty) VALUES
    (1, 17);

==>
ERROR:  insert or update on table "banana_stash" violates foreign key
constraint "banana_stash_primate_id_fkey"
DETAIL:  Key (primate_id)=(1) is not present in table "primate".

INSERT INTO banana_stash(primate_id, qty) VALUES
    (2, 22);

==>
INSERT 0 1

SELECT * FROM banana_stash;

==>
 id | primate_id | qty
----+------------+-----
  2 |          2 |  22
(1 row)

My problem:  could someone please explain the semantics and why this
behaviour makes sense -- or is it a design error or bug?

To sum up the issue:
    - I insert into the derived table (chimp) and get id 1
    - I insert into the base table (primate) and get id 2
    - I have a foreign key constraint in banana_stash to the
      base table p.k. primate(id)
    - inserting to banana_stash with reference to id 2 is okay
    - inserting to banana_stash with reference 1 gives error
    - both ids 1 and 2 in table primate are supposed to be valid

So why does the one case give an error when the other does not?

Also, is there a way to solve this problem (i.e. remove the error)
without simply chopping out the REFERENCES clause from banana_stash?

-Will

William Gordon Rutherdale wrote
> I have encountered a problem with references when using INHERITS (on
> Postgres 9.1/9.2).  Could someone please explain why this occurs.
>
> My problem:  could someone please explain the semantics and why this
> behaviour makes sense -- or is it a design error or bug?
>
> To sum up the issue:
>     - I insert into the derived table (chimp) and get id 1
>     - I insert into the base table (primate) and get id 2
>     - I have a foreign key constraint in banana_stash to the
>       base table p.k. primate(id)
>     - inserting to banana_stash with reference to id 2 is okay
>     - inserting to banana_stash with reference 1 gives error
>     - both ids 1 and 2 in table primate are supposed to be valid
>
> So why does the one case give an error when the other does not?
>
> Also, is there a way to solve this problem (i.e. remove the error)
> without simply chopping out the REFERENCES clause from banana_stash?

I didn't read your post in depth but I suspect you have not read and
understood the limitations documented in section 5.8.1

http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html

David J.



--
View this message in context: http://postgresql.nabble.com/Problem-with-REFERENCES-on-INHERITS-tp5836326p5836347.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 02/02/15 12:11 AM, David G Johnston wrote:
> William Gordon Rutherdale wrote
>> My problem:  could someone please explain the semantics and why this
>> behaviour makes sense -- or is it a design error or bug?

> I didn't read your post in depth but I suspect you have not read and
> understood the limitations documented in section 5.8.1
>
> http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html

Okay, thanks.  The caveats section says this:

    A serious limitation of the inheritance feature is that indexes
    (including unique constraints) and foreign key constraints only
    apply to single tables, not to their inheritance children.

It also says that this 'deficiency' may be fixed in some future release.

Well, I guess that covers it.  I still find it curious:  the particular
form in which it manifests itself.  Instead of simply not performing the
reference check, it over-does it on the derived table.

So this statement:

INSERT INTO banana_stash(primate_id, qty) VALUES
    (1, 17);

Resulted in this error:

ERROR:  insert or update on table "banana_stash" violates foreign key
constraint "banana_stash_primate_id_fkey"
DETAIL:  Key (primate_id)=(1) is not present in table "primate".

How am I to interpret this?  A select * from primate shows that a row
with primate.id exists, yet the error message indicates that it doesn't.

-Will

William Gordon Rutherdale <will.rutherdale@utoronto.ca> writes:
> So this statement:
> INSERT INTO banana_stash(primate_id, qty) VALUES (1, 17);
> Resulted in this error:
> ERROR:  insert or update on table "banana_stash" violates foreign key
> constraint "banana_stash_primate_id_fkey"
> DETAIL:  Key (primate_id)=(1) is not present in table "primate".

> How am I to interpret this?  A select * from primate shows that a row
> with primate.id exists, yet the error message indicates that it doesn't.

If you did "select * from only primate" you would see that there is no
such row in the parent table, which is what the foreign key is being
enforced against.

            regards, tom lane

On 02/02/15 10:11 AM, Tom Lane wrote:
> If you did "select * from only primate" you would see that there is no
> such row in the parent table, which is what the foreign key is being
> enforced against.

Thanks.  That does a lot to clarify it.

-Will