Re: Problem with REFERENCES on INHERITS

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