Thread: AW: Postgresql OO Patch

> One more time for the <general> mailing list...
> 
> Hands up if you have objections to the patch I recently submitted for
> postgresql. It fixes the long standing bit-rot / bug  that DELETE and
> UPDATE don't work on inheritance hierarchies, and it adds the ONLY
> syntax as mentioned in SQL3 and as implemented by Informix. 
> The downside
> is it breaks compatibility with the old inheritance syntax. 
> But there is
> a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
> "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
> FROM ONLY foobar".
> 
> Benefits:
> *) SQL3 says it.

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

Andreas

On Wed, 24 May 2000, Zeugswetter Andreas SB wrote:
> > One more time for the <general> mailing list...
> > 
> > Hands up if you have objections to the patch I recently submitted for
> > postgresql. It fixes the long standing bit-rot / bug  that DELETE and
> > UPDATE don't work on inheritance hierarchies, and it adds the ONLY
> > syntax as mentioned in SQL3 and as implemented by Informix. 
> > The downside
> > is it breaks compatibility with the old inheritance syntax. 
> > But there is
> > a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
> > "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
> > FROM ONLY foobar".
> > 
> > Benefits:
> > *) SQL3 says it.
> 
> Imho this alone more than justifies the patch.
> We should also change our keyword "inherits" to "under".
> 

I don't agree.  UNDER only provides for single inheritance according to spec. 
Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
trees that contain subtables under a single maximal supertable.  Its like a
body that grows by having organs and cells inside it.  INHERIT is like two or
more separate bodies that together, yield an independent offspring.  UNDER and
INHERIT can coexist and be used together.

CREATE TABLE bike (
);
CREATE TABLE motorbike UNDER bike (
) INHERITS (pistonengine);
CREATE table harley (
) UNDER motorbike;


CREATE engine (
);
CREATE TABLE pistonengine UNDER engine (
)
CREATE TABLE jetengine UNDER engine (
);

Stuff like that.  

CREATE motorbike (
) INHERITS (bike, motor);
is ok too.  But the meaning is different than above.  It creates an independent
child table that is not contained under either parent so that the parents can
be dropped.  You use UNDER when the child/subtabe to share the exact same
physical PRIMARY KEY of the SUPERTABLE.  In inherit, the child inherits a
composite key from the parents, but that key is new physically, not the same
physically as any parents.

I just think that since UNDER is limited by the spec, (and there is a
difference anyway), that INHERITS stands on its own and can be used with UNDER
to pull attributes into the tree from another tree/table, linking separate
trees together in an nondependent way.

> Andreas
-- 
Robert B. Easter
reaster@comptechnews.com

"Robert B. Easter" wrote:

> > Imho this alone more than justifies the patch.
> > We should also change our keyword "inherits" to "under".
> >
> 
> I don't agree.  UNDER only provides for single inheritance according to spec.
> Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
> trees that contain subtables under a single maximal supertable. 

I don't see that it's a "basic idea". I see it as crippled subset of
SQL3-94.

> is ok too.  But the meaning is different than above.  It creates an independent
> child table that is not contained under either parent so that the parents can
> be dropped.  

I wouldn't like to define an object model in terms of what happens when
the meta-data is modified.

> You use UNDER when the child/subtabe to share the exact same
> physical PRIMARY KEY of the SUPERTABLE.  In inherit, the child inherits a
> composite key from the parents, but that key is new physically, not the same
> physically as any parents.

Issues like primary keys are the sort of stuff that probably kept the
committee arguing long enough they were too lazy to come to a decision.
Myself, I'm not too interested in primary keys since they are not a very
OO idea anyway.

Chris Bitmead wrote:
> 
> "Robert B. Easter" wrote:
> 
> > > Imho this alone more than justifies the patch.
> > > We should also change our keyword "inherits" to "under".
> > >
> >
> > I don't agree.  UNDER only provides for single inheritance according to spec.
> > Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
> > trees that contain subtables under a single maximal supertable.
> 
> I don't see that it's a "basic idea". I see it as crippled subset of
> SQL3-94.

Me too.

OTOH single inheritance has the advantage that it can be implemented
with _all_ 
subtables stored in single "physical" table, whereas multiple
inheritance can't,
which makes sharing thinkgs like primary keys and other constraints much
easier 
to implement as well.

> > You use UNDER when the child/subtabe to share the exact same
> > physical PRIMARY KEY of the SUPERTABLE.  In inherit, the child inherits a
> > composite key from the parents,

That composite key must actually still be two unique key (and thus
doube-uniqe ;)
which does not make much sense.

> > but that key is new physically, not the same physically as any parents.

Maybe what you are trying to accomplice by your definition of INHERITS
is 
better done by aggregation ?

create table engine (volume float);
create table wheel(circumference float);
create table car(   car_engine  engine,   car_wheels  wheel[4]
);

At least this fits better with may feeling that a car is not a "kind of"
engine.

And this should be possible with PostgreSQL now (except that type _wheel 
(for array of wheels) is not generated automatically and so only the 
following is

create table car(   car_engine  engine,   car_wheel1  wheel,   car_wheel2  wheel,   car_wheel3  wheel,   car_wheel4
wheel
);

which probably is a bug ;(

)

> Issues like primary keys are the sort of stuff that probably kept the
> committee arguing long enough they were too lazy to come to a decision.

It sure is an issue for multiple inheritance, at least when you disallow 
multiple primary keys on things that "are" both A and B.

> Myself, I'm not too interested in primary keys since they are not a very
> OO idea anyway.

Maybe not OO but useful in RDBM anyway. One could argue that table.pk ==
oid. 
And when implemented that way it would make finding an "object" in an
RDBM
very easy ;)

------
Hannu