
Curt Sampson wrote:
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.


Table inheritance offers data model extensibility.  New (derived) tables
can be added to the system, and will work with existing code that
opperates on the base tables, without having to hack up all the code.

Inherited indexes etc. would be nice, but it's the inability to have
referential integrity against a base table that picks up child table
rows that makes the current implementation useles.

I would rather see it fixed than junked, and better yet extended.  It
would be incredibly useful in real-world projects with complex data
models like OpenACS.

29 Jul 2002 18:27:40 -0600
Stephen Deasey <stephen@bollocks.net>
Re: Why is MySQL more chosen over PostgreSQL
On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as
Stephen Deasey <stephen@bollocks.net>  said:
> Curt Sampson wrote:
>> I'm still waiting to find out just what advantage table inheritance
>> offers. I've asked a couple of times here, and nobody has even
>> started to come up with anything.

> Table inheritance offers data model extensibility.  New (derived) tables
> can be added to the system, and will work with existing code that
> operates on the base tables, without having to hack up all the code.

But it kind of begs the question of why you're creating the new table in
the first place.

The new table certainly _won't_ work with existing code, at least from
the perspective that the existing code doesn't _refer_ to that table.

The same is not true for views; if you create a new view on a table, the
existing code that refers to the table in either "raw" form or in other
views already exist will certainly continue to work.

> Inherited indexes etc. would be nice, but it's the inability to have
> referential integrity against a base table that picks up child table
> rows that makes the current implementation useles.

Views will certainly inherit indices, and continue to maintain
referential integrity against the "base" table.

> I would rather see it fixed than junked, and better yet extended.  It
> would be incredibly useful in real-world projects with complex data
> models like OpenACS.

Have they found views to be an unacceptable alternative?

I just don't see that there's _all_ that much about table inheritance
that is really fundamentally wonderful.

The "incredibly useful" thing, it seems to me, would be to provide tools
that make it less necessary to create additional tables.  To be sure,
views do that.

I'm not quite sure what INHERITS buys us that we don't get from
  SELECT * into new_table from old_table

INHERITS may automagically draw in some contraints that have been
specifically tied to old_table that wouldn't be drawn by "SELECT * INTO
NEW_TABLE"; the latter _will_ inherit anything that is tied to the
types.

I'd _much_ rather have five views on one table than five tables.
--
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://cbbrowne.com/info/spreadsheets.html
"Everything should  be made as  simple as possible, but  not simpler."
-- Albert Einstein

Fri, 02 Aug 2002 13:39:44 -0400
cbbrowne@cbbrowne.com
Table inheritance versus views
On Fri, 2002-08-02 at 22:39, cbbrowne@cbbrowne.com wrote:
> On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as
> Stephen Deasey <stephen@bollocks.net>  said:
> > Curt Sampson wrote:
> >> I'm still waiting to find out just what advantage table inheritance
> >> offers. I've asked a couple of times here, and nobody has even
> >> started to come up with anything.
> 
> > Table inheritance offers data model extensibility.  New (derived) tables
> > can be added to the system, and will work with existing code that
> > operates on the base tables, without having to hack up all the code.
> 
> But it kind of begs the question of why you're creating the new table in
> the first place.
> 
> The new table certainly _won't_ work with existing code, at least from
> the perspective that the existing code doesn't _refer_ to that table.

The beuty of OO is that it does not need to :

hannu=# create table animal (name text, legcount int);
CREATE
hannu=# insert into animal values('pig',4);
INSERT 34183 1
hannu=# select * from animal;
 name | legcount 
------+----------
 pig  |        4
(1 row)

hannu=# create table bird (wingcount int) inherits (animal);
CREATE
hannu=# insert into bird values('hen',2,2);
INSERT 34189 1
hannu=# select * from animal;
 name | legcount 
------+----------
 pig  |        4
 hen  |        2
(2 rows)

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


03 Aug 2002 18:10:44 +0500
Hannu Krosing <hannu@tm.ee>
Re: Table inheritance versus views

> On Fri, 2002-08-02 at 22:39, cbbrowne@cbbrowne.com wrote:
> 
>>On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as
>>Stephen Deasey <stephen@bollocks.net>  said:
>>
>>>Curt Sampson wrote:
>>>
>>>>I'm still waiting to find out just what advantage table inheritance
>>>>offers. I've asked a couple of times here, and nobody has even
>>>>started to come up with anything.
>>>
>>>Table inheritance offers data model extensibility.  New (derived) tables
>>>can be added to the system, and will work with existing code that
>>>operates on the base tables, without having to hack up all the code.
>>
>>But it kind of begs the question of why you're creating the new table in
>>the first place.
>>
>>The new table certainly _won't_ work with existing code, at least from
>>the perspective that the existing code doesn't _refer_ to that table.

Since OpenACS has been brought up in this thread, I thought I'd join the 
list for a day or two and offer my perspective as the project manager.

1. Yes, we use views in our quasi-object oriented data model.  They're 
automatically generated when content types are built by the content 
repository, for instance.

2. Yes, you can model anything you can model with PG's OO extensions 
using views.  If you haven't implemented some way to generate the view 
automatically then a bit more work is required compared to using PG's OO 
extensions.

3. The view approach requires joins on all the subtype tables.  If I 
declare type 'foo' then the view that returns all of foo's columns joins 
on all the subtype tables, while in the PG OO case all of foo's columns 
are stored in foo meaning I can get them all back with a simple query on 
the table.  The PG OO approach can be considerably more efficient than 
the view approach, and this is important to some folks, no matter how 
many appeals to authority are made to various bibles on relational 
theory written by Date and Darwen.

4. The killer that makes the current implementation unusable for us is 
the fact that there's no form of indexing that spans all the tables 
inherited from a base type.  This means there's no cheap enforcement of 
uniqueness constraints across a set of object types, among other things. 
  Being able to inherit indexes and constraints would greatly increase 
the utility of PG's OO extensions.

5. If PG's OO extensions included inheritance of indexes and 
constraints, there's no doubt we'd use them in the OpenACS project, 
because when researching PG we compared datamodels written in this style 
vs. modelling the object relationships manually with automatically 
generated views.  We found the datamodel written using PG's OO 
extensions not only potentially more efficient, but more readable as well.

As far as whether or not there's a significant maintenance cost 
associated with keeping the existing OO stuff in PG, Tom Lane's voice is 
authorative while, when it comes to PG internals, Curt Sampson doesn't 
know squat.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Sat, 03 Aug 2002 09:47:54 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Table inheritance versus views
On 29 Jul 2002, Stephen Deasey wrote:

> Table inheritance offers data model extensibility.  New (derived) tables
> can be added to the system, and will work with existing code that
> opperates on the base tables, without having to hack up all the code.

And why does this not work with the standard relational mechanism?
(Where a "derived table" would be just another table with a foreign
key pointing back to the base 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 all light.  --XTC


Sun, 4 Aug 2002 15:41:21 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL
On 3 Aug 2002, Hannu Krosing wrote:

> hannu=# create table animal (name text, legcount int);
> CREATE
> hannu=# insert into animal values('pig',4);
> INSERT 34183 1
> hannu=# select * from animal;
>  name | legcount
> ------+----------
>  pig  |        4
> (1 row)
>
> hannu=# create table bird (wingcount int) inherits (animal);
> CREATE
> hannu=# insert into bird values('hen',2,2);
> INSERT 34189 1
> hannu=# select * from animal;
>  name | legcount
> ------+----------
>  pig  |        4
>  hen  |        2
> (2 rows)

You can do this just as well with views. In posgres, it's harder
only because you're forced to create rules for updating views. But
it's possible to have the system automatically do the right thing.

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 all light.  --XTC


Sun, 4 Aug 2002 15:50:08 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Table inheritance versus views