Re: REFERENCES misbehaves with inheritance

On Sun, Jan 31, 2010 at 9:07 AM, Steve White <swhite(at)aip(dot)de> wrote:
> Hi,
>
> I'm aware that this is a manifestation of the problem mentioned in the
> Caveats subsection of the Inheritance section.  I want to emphasize it,
> and maybe rattle your cage a bit.
>
> I find the Postgres notion of inheritance very compelling. Conceptually
> it does what I want, when I create tables of related, but different kinds
> of things.
>
> Unfortunately these little ommissions really foul up implementations
> using inheritance.
>
> For instance:  a field that REFERENCES a field in an inherited table is
> unaware that records have been added to the inherited table, by way of
> records being added to inheriting tables.
>
> This is awful.  One is forced to make choices between various evils.
>
> EXAMPLE:
> ============================================================================
>
> CREATE TABLE a (
>  a_id   SERIAL PRIMARY KEY
> );
>
> CREATE TABLE a1 (
> ) INHERITS( a );
>
> CREATE TABLE a2 (
> ) INHERITS( a );
>
> CREATE TABLE b (
>  b_id   SERIAL PRIMARY KEY,
>  a_id   INTEGER,
>  FOREIGN KEY (a_id) REFERENCES a(a_id)
> );
> -- ---------------------------------------
>
> INSERT INTO a1 VALUES( DEFAULT );
>
> -- The following results in a foreign key violation, saying
> -- no row with a_id=1 is present in table "a":
> INSERT INTO b VALUES( DEFAULT, CURRVAL('a_a_id_seq') );
>
> -- However this indicates that table "a" has a row with a_id=1:
> SELECT * FROM a;

I am guessing that the problem with this feature is not so much that
it's hard to implement as that the performance could be terrible: no
one has gotten around to adding the ability to create an index that
includes both the parent and all of its inheritance children.

I suppose in theory if each child had an index on the relevant
column(s) it might not be too bad, for certain use cases, but if you
have, say, a thousand child tables and have to make an index probe
into each one for each row inserted into the referring table, that
could be pretty ugly (~2k random seeks per row - ouch).

...Robert