Re: Inheritance - Mailing list pgsql-hackers

I have a question about inheritance:

You have 2 tables: Programmer and employee. Programmer inherits employee. You 
put in a generic employee record for someone, but then she becomes a 
programmer. What do you do? (I borrowed this example from a book by C.J. 
Date, who posed this question). Do you DELETE then INSERT? Something seems 
wrong with that somehow. Are the postgres developers agreed upon how that 
situation should be handled? What about the database users, and their 
expectations of the behavior? 

I am not advocating that we remove inheritence (I say this because this topic 
has generated some significant discussion about that). However, I will stick 
to the well-defined relational model until I see something useful from the 
inheritance system that is as well-defined. I agree it saves a few keystrokes 
(and can help organize things for you, as do objects in a programming 
language), but mind is more at peace when I am actually sure of what's 
happening. I can always throw more rules/views/triggers at the situation 
until I have a nice set of things to work with in the application.

Or, I suppose, if someone shows me something that I can't do in the relational 
model, but can with inheritance, I might be convinced otherwise.

Regards,Jeff Davis



On Thursday 05 September 2002 01:05 am, Hannu Krosing wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org