Re: Inheritance

On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
> On Tue, 3 Sep 2002, Bruce Momjian wrote:
>
> > Yep, this is where we are stuck; having an index span multiple tables
> > in some way.
>
> Or implementing it by keeping all data in the table in which it
> was declared. (I.e., supertable holds all rows; subtable holds
> only the primary key and those columns of the row that are not
> in the supertable.)

How would you do it for _multiple_ inheritance ?

When implementing it on top of standard relational model you have more
or less two ways to slice the problem

1) the way you describe (parent holding common columns + child tables
for added child columns), which makes it easy to define constraints but
hard to do inserts/updates/deletes on inherited tables

2) the postgresql way (a new table for each child), which makes it hard
to define constraints but easy to do inserts/updates/deletes.

> From looking at the various discussions of this in books, and what
> it appears to me that the SQL standard says, it seems that their
> overall vision of table inheritance is to be consistent with the
> implementation that I described above.

Yes. The SQL99 standard specifies only _single_ inheritance for tables +
LIKE in column definition part, making the model somewhat similar to
Java's (single inheritance + interfaces).

This way it could probably be done even more effectively than you
describe by:

1) keeping _all_ (not only the inherited columns) the data for
inheritance hierarchy in the same physical file.

2) having partial indexes (involving tableoid=thiskindoftable) for
possible speeding up of SELECT .. ONLY queries.

3) no changes to (unique) indexes - they still reference simple TID's
without additional table part.

4) update/delete of all child tables are trivial as they are actually
done in the same table and not using joins

It seems that single inheritance avoids other conceptual problems, like
what to do with primary keys when inheriting from two tables that have
them.

--------------------
Hannu