Re: Inheritance - Mailing list pgsql-hackers

On Thu, 2002-09-05 at 09:28, Curt Sampson wrote:
> 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.

What I meant was that it is relatively more costly to update several
"physical" tables than updating one .

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

But again this is not _conceptually_ hard, just hard to implement
efficiently.

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

probably. I was describing to a way to efficiently implement single
inheritance. 

The layer was somewhere between physical files and logical tables, i.e.
above splitting stuff into main/toast and also above splitting big files
to 1Gb chunks, but below logical tables, which are (or are not when
omitting ONLY ;) still separate logically.

Perhaps it could be named "logical file".

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

It does not matter in case you are assuming that the storage model can't
be changed. The trick with inherited tables is that in some sense they
are the same table and in another sense they are separate tables.

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

You should not need it, as the storage for existing tuples does not
change - even now you can do ADD COLUMN without touching existing
tuples.

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

It would not be noticably wider (only 1 bit/column) even if I did
propose storing all columns.

What I was actually trying to describe was that the tuple format would
be what it is currently, just stored in the same table with parent.

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

That's one possibility. The other would be to keep the one from the
first table as primary and demote onlly the other primary keys.

With single inheritance you don't even have to think about it.

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