Re: Inheritance - Mailing list pgsql-hackers

On 5 Sep 2002, Hannu Krosing wrote:

> On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
>
> > 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 ?

Exactly the same way. Each column resides in only one physical table,
so you need only find the table it resides in, and do the insert there.
I'll be happy to provide an example if this is not clear.

> 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

I wouldn't say it makes it "hard" to do inserts, updates and deletes.
Postgres already has pretty near all of the code it needs to support
these updates, because these are the semantic equivalant of the separate
actions applied to the separate tables within one transaction.

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

I agree that making constraints work in this model is very difficult and
a lot of work.

> 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.

You appear to have delved into a different database layer than one
I'm looking at, here. I was examining storage on the table level,
which is unrelated to files. (E.g., postgres sometimes stores a
table in one file, sometimes in more than one. MS SQL Server stores
many tables in one file. It doesn't matter which approach is used when
discussing the two inheritance implementation options above.)

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

Or are you talking about storing all of the columns in a single
table? That's a possibility, but wouldn't it be costly to update
the entire table every time you add a new child table? And table
scans on child tables would certainly be more costly if you had
many of them, becuase the effective row width would be much wider.
But it might be worth thinking about.

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

I don't see where there's a conceptual problem here, either. With
multiple inheritance you can simply demote both keys to candidate
keys, and continue on as normal. (The only difference between a
primary key and a candidate key is that you can leave out the column
names when declaring foreign keys in another table.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC