On Sun, Jan 31, 2010 at 9:07 AM, Steve White <swh...@aip.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs