On Sun, Jan 31, 2010 at 9:07 AM, Steve White <swhite(at)aip(dot)de> wrote:
> 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.
> 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).