Re: Table Inheritance Discussion

Curt Sampson wrote:

> The last question comes up because, during the conversation up to this
> point, we seem to have implicitly accepted that table inheritance is
> an "object-oriented" way of doing things. Thinking further on this,
> however, I've decided that it's not in fact object-oriented at all.

It's just type extensibility, really.

As to why, again there's an efficiency argument, as I said earlier some
joins can be avoided given PG's implementation of this feature:

dotlrn=# create table foo(i integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(j integer) inherits (foo);
CREATE
dotlrn=# explain select * from bar;
NOTICE: QUERY PLAN:

Seq Scan on bar (cost=0.00..20.00 rows=1000 width=8)

EXPLAIN
...

dotlrn=# create table foo(i integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(i integer references foo primary key, j integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'bar_pkey' for table 'bar'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
dotlrn=# create view foobar as select foo.*, bar.j from foo, bar;
CREATE

dotlrn=# explain select * from foobar;
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..30020.00 rows=1000000 width=8)
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4)
-> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

There's also some error checking (using my inherited example):

dotlrn=# drop table foo;
ERROR: Relation "bar" inherits from "foo"
dotlrn=#

Which doesn't exist in the view approach in PG at least (I'm unclear on
standard SQL92 and of course this says nothing about the relational
model in theory, just PG and perhaps SQL92 in practice).

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