I have now been working with table inheritance for a while and after starting to grapple with many of the use cases it has have become increasingly impressed with this feature. I also think that some of the apparent limitations fundamentally follow from the support for multiple inheritance, and multiple inheritance itself is so useful I would not want to see this go away. Inheritance really starts to come to its own once you start using table methods, and some features that are useful in some sorts of inheritance modelling are useless in others. Below I will offer a few suggestions regarding what can be done to make life a little easier for those of us using these features and they are not the typical suggestions. I still stand by my view that at least from what I have looked at, PostgreSQL allows you to do object-relational modelling better than you can do in other ORDBMS's I have looked at so far. What table inheritance gives you is an ability to model pieces of a database, and derived information, on small units which can then be later combined. When we think of that as the primary use case (instead of set/subset modelling) then the current DDL caveats largely don't apply. Logic can thus follow a group of columns rather than having to be repetitively attached to tables. The fact that this allows you to create essentially derived values from groups of re-used columns is itself remarkable and can be used to implement path traversal etc. which is not directly supported in PostgreSQL in the sense that it is in Oracle or DB2. With multiple inheritance you can actually build superior path traversal systems than you can easily on DB2 or Oracle because you can re-use enforced foreign keys (Oracle has an IS DANGLING operator for cross-table references!). As far as I can tell, this sort of use is PostgreSQL-only because it relies on multiple inheritance which is not supported on DB2, Informix, or Oracle. I am not aware of any other ORDBMS that allows for multiple inheritance and this has profound impacts on things like primary key inheritance, which I think becomes meaningless when combined with multiple inheritance. If I inherit two tables each with a different primary key, I obviously cannot inherit both without having multiple primary keys in the child table. I have to be the sort of person who sees bugs as features, but in this respect I cannot see the lack of inheriting a primary key as a bug anymore. It seems to me mathematically incompatible with PostgreSQL's take on table inheritance generally and this is one of those cases where multiple inheritance changes everything. Additionally it is important to note that primary key management is not a huge problem because it can be solved using techniques borrowed from table partitioning. If you are doing set/subset modelling (as in the cities/capitals example) the solution is to have a cities table which is constrained with a trigger or rule which does not allow inserts and then capitals and noncapitals tables. The primary key can then include an is_capital bool field which can be constrained differently on both tables. This has the advantage of knowing whether a city selected is a capital from the top-level query as well, and allows for the planner to treat the inheritance tree as a partitioned table set. Superset-constraint management would also have to use a second table which would be referenced by all child tables (and perhaps maintained by triggers). While superset management tables can be used to solve a subset of foreign key problems, they highlight a different (and perhaps more solvable) set of these problems. As far as I can tell, Oracle and DB2 do not discuss primary key inheritance and it isn't clear whether this is a problem on those platforms too. Foreign key management pretty clearly is a problem given the way these platforms handle cross-relational REFs. In other words, I think that on the whole table inheritance is still cutting edge on PostgreSQL and has been for some time. Foreign keys can be managed in a few ways including superset constraint tables maintained with triggers. These work well for enforcing foreign keys against subsets, but inheriting a foreign key constraint means redefining it repetitively on every child table. At the same time, not all foreign keys may want to be inherited. The following changes to behavior I would personally find very useful (and I believe would be useful in partitioned tables as well): * foreign keys (i.e. REFERENCES clauses) being able to be marked INHERIT or NOINHERIT on the parent table. INHERIT foreign keys would be automatically created on child tables. The default could be left to be NOINHERIT to avoid breaking backwards compatibility. * unique constraints being able to be marked INHERIT or NOINHERIT. A unique constraint that is marked INHERIT would be automatically created again on the child table. This could be documented to be domain-specific to each child table, and that if you need super-set unique constraints, you need to borrow techniques from table partitioning. * an ability to allow a check constraint to be marked NOINHERIT and thus excluded down-tree. This could be used to exclude inserts onto parent tables both in partitioning and object inheritance tree environments and it greatly simplifies set/subset modelling. * PRIMARY KEY inheritance would be documented as necessarily excluded by multiple inheritance. The concept simply doesn't make sense when a child table can have multiple parents. If it did, you'd have multiple primary keys. According to Oracle and DB2 documentation, the best they offer regarding such things is an OID field anyway..... Even without these changes, however, I am finding PostgreSQL's table inheritance to be extremely useful, and I will be covering it extensively in an upcoming blog post. Also instead of saying that it is mostly only useful in table partitioning, I would say that the techniques of table partitioning are useful in addressing the difficulties one runs into in set/subset modelling. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
