Обсуждение: Re: Inheritance

> > It's nonlocal constraints that are the problem, and here foreign keys
> > and UNIQUE constraints are certainly the canonical examples.  Both of
> > these would be largely solved with table-spanning indexes I think.
>
> Note that the other obvious way to solve this would be to store all of
> the information inherited from the parent in the parent table, so that
> you don't have to do anything special to make all of the constraints and
> whatnot apply.

Seems with above you are not able to constrain what qualifies for a supertable row,
you would only be able to specify constraints that apply to all it's subtables.
To me, the current implementation looks superior and more efficient.

The SQL inheritance is a class/subclass thing.
All tables have instances (==rows) that are not (by itself) related.
(Even if they happen to share all attribute values with another row of a supertable.)
If you want that, then you need to resort to 3NF (or ROWREF's which iirc is another
SQL99 feature).

Andreas

On Fri, 16 Aug 2002, Zeugswetter Andreas SB SD wrote:

> > Note that the other obvious way to solve this would be to store all of
> > the information inherited from the parent in the parent table, so that
> > you don't have to do anything special to make all of the constraints and
> > whatnot apply.
>
> Seems with above you are not able to constrain what qualifies for a
> supertable row, you would only be able to specify constraints that
> apply to all it's subtables.

Yes, that's the whole point. If I have a constraint on a table, I think
it should *never* be possible for that constraint to be violated. If a
subtable should not have constraint the supertable has, it shouldn't
inherit from the supertable.

To do otherwise breaks the relational model.

> The SQL inheritance is a class/subclass thing. All tables have
> instances (==rows) that are not (by itself) related. (Even if
> they happen to share all attribute values with another row of a
> supertable.) If you want that, then you need to resort to 3NF (or
> ROWREF's which iirc is another SQL99 feature).

As I understand it, SQL99 has the restriction that a row with the same
primary key appearing in a supertable and/or any of its subtables must
be the result of a single INSERT statement. Thus, SQL99 doesn't allow
what you're saying, if I understand what you're saying. (I'm not sure
that I do.)

Am I to take it that you think the inheritance should be inheritance
of type information only? That is, if I have supertable A and
subtable A', inserting a row into A' does not make a row appear in
A? If so, I've got not real problem with that at present, but it's
not what postgres currently does, nor would it conform to SQL99.

What do others think of this idea?

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

> > Seems with above you are not able to constrain what qualifies for a
> > supertable row, you would only be able to specify constraints that
> > apply to all it's subtables.
>
> Yes, that's the whole point. If I have a constraint on a table, I think
> it should *never* be possible for that constraint to be violated. If a
> subtable should not have constraint the supertable has, it shouldn't
> inherit from the supertable.

If you want that, you simply need to only create constraints that apply to
all tables in the hierarchy. Note that you *can* do this. It should imho be
the default behavior.

>
> To do otherwise breaks the relational model.
>

That is probably a point of argument. Imho the inheritance feature
is something orthogonal to the relational model. It is something else, and
thus cannot break the relational model.

>
> > The SQL inheritance is a class/subclass thing. All tables have
> > instances (==rows) that are not (by itself) related. (Even if
> > they happen to share all attribute values with another row of a
> > supertable.) If you want that, then you need to resort to 3NF (or
> > ROWREF's which iirc is another SQL99 feature).
>
> As I understand it, SQL99 has the restriction that a row with the same
> primary key appearing in a supertable and/or any of its subtables must
> be the result of a single INSERT statement. Thus, SQL99 doesn't allow
> what you're saying, if I understand what you're saying. (I'm not sure
> that I do.)

I was not talking about primary key, not all tables have a primary key.
If the supertable has a PK, then of course an exact match of columns is not
possible in supertable/subtable rows.
> Am I to take it that you think the inheritance should be inheritance
> of type information only? That is, if I have supertable A and
> subtable A', inserting a row into A' does not make a row appear in
> A? If so, I've got not real problem with that at present, but it's
> not what postgres currently does, nor would it conform to SQL99.

No, not at all. All I am saying is that I want to be able to create a
constraint that only applies to the supertable rows, and not the
subtable rows. I would *not* want this as default behavior when creating a
constraint though.

Andreas

On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:

> > Yes, that's the whole point. If I have a constraint on a table, I think
> > it should *never* be possible for that constraint to be violated. If a
> > subtable should not have constraint the supertable has, it shouldn't
> > inherit from the supertable.
>
> If you want that, you simply need to only create constraints that apply to
> all tables in the hierarchy. Note that you *can* do this. It should imho be
> the default behavior.

So what you're saying is that constraints shouldn't be inherited?

> > To do otherwise breaks the relational model.
>
> That is probably a point of argument. Imho the inheritance feature
> is something orthogonal to the relational model. It is something else, and
> thus cannot break the relational model.

So then constraints must be inherited. The relational model, if I
am not incorrect here, says that, given a table definition such as
this:
   CREATE TABLE my_table (my_key int PRIMARY KEY,my_value text UNIQUE,my_other_value int CHECK (my_other_value > 0)
)

You will never, ever, when selecting from this table, have returned to you
   1. two rows with the same value of my_key but different values   for the other columns,
   2. two rows with the same value of my_value but different values   for the other columns, or
   3. a row in which the value of my_other_value is not greater than zero.

Breaking these sorts of guarantees under any circumstances really
doesn't do it for me; what's the point of having guarantees if they
aren't guarantees?

> > As I understand it, SQL99 has the restriction that a row with the same
> > primary key appearing in a supertable and/or any of its subtables must
> > be the result of a single INSERT statement. Thus, SQL99 doesn't allow
> > what you're saying, if I understand what you're saying. (I'm not sure
> > that I do.)
>
> I was not talking about primary key, not all tables have a primary key.

Well, for those that do....

Also, I should amend that; I suspect (though I could well be wrong,
knowing how screwed up SQL is at times) that this really applies
to all candidate keys in the table.

(And this is one of my complaints about SQL; it's possible for a table to
exist without candidate keys. So much for set theory!)

> No, not at all. All I am saying is that I want to be able to create a
> constraint that only applies to the supertable rows, and not the
> subtable rows.

I would strongly object to that. It should not be possible to SELECT
data from a table that violates the constraints that that table is
guaranteeing on the data.

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

> > > Yes, that's the whole point. If I have a constraint on a table, I think
> > > it should *never* be possible for that constraint to be violated. If a
> > > subtable should not have constraint the supertable has, it shouldn't
> > > inherit from the supertable.
> >
> > If you want that, you simply need to only create constraints that apply to
> > all tables in the hierarchy. Note that you *can* do this. It should imho be
> > the default behavior.
>
> So what you're saying is that constraints shouldn't be inherited?

No. I even said that inheriting should be the default.
> > > To do otherwise breaks the relational model.
> >
> > That is probably a point of argument. Imho the inheritance feature
> > is something orthogonal to the relational model. It is something else, and
> > thus cannot break the relational model.
>
> So then constraints must be inherited. The relational model, if I
> am not incorrect here, says that, given a table definition such as
> this:
>
>     CREATE TABLE my_table (
>     my_key int PRIMARY KEY,
>     my_value text UNIQUE,
>     my_other_value int CHECK (my_other_value > 0)
>     )

A local constraint should be made obvious from looking at the schema,
a possible syntax (probably both ugly :-):
CHECK my_table ONLY (my_other_value > 0)
or
CHECK LOCAL (my_other_value > 0)

>
> You will never, ever, when selecting from this table, have
> returned to you
>
>     1. two rows with the same value of my_key but different values
>     for the other columns,
>
>     2. two rows with the same value of my_value but different values
>     for the other columns, or
>
>     3. a row in which the value of my_other_value is not
> greater than zero.
>

Well, that is where I do not think this is flexible enough, and keep in mind
that all triggers and rules would then also need such restrictions.

> I would strongly object to that.

Regardless whether your objection is *strong* or not :-)
If you don't like the feature (to add a local constraint), don't use it.
(Remember you are talking about removing an implemented feature)

Andreas

On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:

> > So what you're saying is that constraints shouldn't be inherited?
>
> No. I even said that inheriting should be the default.

Ah. So you think it should be possible not to inherit constraints.

> A local constraint should be made obvious from looking at the schema,

Ok, this now I could live with. Though I'm not sure that its
theoretically very defensible, or worth the effort. Other languages
that offer constraints, such as Eiffel (and soon Java), do not allow
constraints that are not inherited, as far as I know. Do you have some
counterexamples.

> Well, that is where I do not think this is flexible enough, and keep in mind
> that all triggers and rules would then also need such restrictions.

Yes, all triggers, rules, and everything else would have to be inherited.

> Regardless whether your objection is *strong* or not :-)
> If you don't like the feature (to add a local constraint), don't use it.
> (Remember you are talking about removing an implemented feature)

1. It's not exactly an implemented feature, it's an accident of an
incomplete implementation of inheritance done in a certain way.

2. Should we change the way we decide to implement inheritance,
perhaps to make fixing the current problems much easier, it might
be a lot of work to add this.

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

On Mon, 2002-08-19 at 15:42, Curt Sampson wrote:
> > A local constraint should be made obvious from looking at the schema,
> 
> Ok, this now I could live with. Though I'm not sure that its
> theoretically very defensible, or worth the effort. Other languages
> that offer constraints, such as Eiffel (and soon Java), do not allow
> constraints that are not inherited, as far as I know. Do you have some
> counterexamples.

In Eiffel, at least, I can say "invariant feature_x" and redefine
feature_x in a descendant class, thus effectively redefining the
constraint.  If we decide to inherit constraints unconditionally, the
application writer can achieve similar flexibility by moving the logic
of the constraint into a function whose behaviour depends on which table
it is used on.  This would put the burden on the application rather than
requiring additional syntax in PostgreSQL.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "For every one that asketh receiveth; and he that      seeketh findeth; and
tohim that knocketh it shall be      opened."                         Luke 11:10 
 

On Mon, 2002-08-19 at 09:42, Curt Sampson wrote:
> On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:
>
> > > So what you're saying is that constraints shouldn't be inherited?
> >
> > No. I even said that inheriting should be the default.
>
> Ah. So you think it should be possible not to inherit constraints.

I've been silent for a bit because I wanted to kick the concept around
in my head.  After some thought, I say that I support children
inheriting constraints.  In a more abstract sense, we are really setting
conditions for all entities of a given type (class) which must be met to
classify as a defined type.  Again, in an abstract sense, if I say all
"candies" (type/class, candy) must have sugar (constraint), and I go on
to create a subclass of candy which I desire not to have sugar, then
there is a fundamental problem.  Either I incorrectly identified my
problem domain and didn't properly create my entities which address my
domain needs or what I'm trying to express really isn't a candy at all.
In other words, it sounds like candy should of been a subclass of a more
abstract base entity.  Likewise, the newly desired class which doesn't
have sugar should also inherit from the newly created base class and not
be derived from candy at all.


>
> > A local constraint should be made obvious from looking at the schema,
>
> Ok, this now I could live with. Though I'm not sure that its
> theoretically very defensible, or worth the effort. Other languages
> that offer constraints, such as Eiffel (and soon Java), do not allow
> constraints that are not inherited, as far as I know. Do you have some
> counterexamples.

I tend to agree.  Constraints should be inherited.  See above.

>
> > Well, that is where I do not think this is flexible enough, and keep in mind
> > that all triggers and rules would then also need such restrictions.
>
> Yes, all triggers, rules, and everything else would have to be inherited.

Agreed.

>
> > Regardless whether your objection is *strong* or not :-)
> > If you don't like the feature (to add a local constraint), don't use it.
> > (Remember you are talking about removing an implemented feature)
>
> 1. It's not exactly an implemented feature, it's an accident of an
> incomplete implementation of inheritance done in a certain way.
>
> 2. Should we change the way we decide to implement inheritance,
> perhaps to make fixing the current problems much easier, it might
> be a lot of work to add this.
>

I'm still trying to figure out if subclasses should be allowed to have
localized constraints.  I tend to think yes even though it's certainly
possible to create seemingly illogical/incompatible/conflicting
constraints with parent classes.  Then again, my gut feeling is, that's
more and an architectural/design issue rather than a fundamental issue
with the concept.


--Greg Copeland

The August draft of the SQL:200n standard (9075-2 Foundation) says in
Section 4.17.2: "Every table constraint specified for base table T is
implicitly a constraint on every subtable of T, by virtue of the fact
that every row in a subtable is considered to have a corresponding
superrow in every one of its supertables."

Peter Gulutzan
Co-Author, SQL-99 Complete, Really
Co-Author, SQL Performance Tuning

Peter Gulutzan wrote:
> The August draft of the SQL:200n standard (9075-2 Foundation) says in
> Section 4.17.2: "Every table constraint specified for base table T is
> implicitly a constraint on every subtable of T, by virtue of the fact
> that every row in a subtable is considered to have a corresponding
> superrow in every one of its supertables."

Yep, this is where we are stuck;  having an index span multiple tables
in some way.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

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

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.

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

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

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.

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

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

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

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

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

On 5 Sep 2002, Hannu Krosing wrote:

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

Oh, I see. Not that this is that big a deal, I think. Given that
it doesn't work correctly at the moment, making it work fast is a
definite second priority, I would think.

Once it's working right, one can always replace the internals with
something else that does the same job but is more efficient.

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

No, it's conceptually hard. Not all constraints are implemented with
just a unique index you know. And changing a constraint means you have
to check all the child tables, etc. etc. It's difficult just to track
down down all the things you have to try to preserve. Not to mention,
there's always the question of what happens to triggers and suchlike
when handed a tuple with extra columns from what it expects, and having
it modify the insert into a different table.

The beauty of storing all supertable columns in the supertable itself is
that the behaviour is automatically correct.

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

So what you're saying is that each tuple in the table would have a
format appropriate for its "subtype," and the table would be full of
tuples of varying types? At first blush, that seems like a reasonable
approach, if it can be done.

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

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

On Thu, 5 Sep 2002, Jeff Davis wrote:

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

This is not so wrong. If you think about it, you have the same
problem in most object-oriented programming languages: a person
object can't generally easily become a subclass of itself after
being created.

This is a case, I would say, where you simply don't want to use
inheritance. A person has-a job, not is-a job.

> What about the database users, and their expectations of the behavior?

Nobody really knows; table inheritance in databases is not well-defined.
(Though perhaps the latest SQL spec. changes 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.

Amen! :-)

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

I think that most people are at this point agreed that table
inheritance, at least as currently implemented in any known system,
doesn't offer anything that can't easily be done relationally.

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

On Thu, 2002-09-05 at 10:52, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > What I meant was that it is relatively more costly to update several
> > "physical" tables than updating one .
> 
> Oh, I see. Not that this is that big a deal, I think. Given that
> it doesn't work correctly at the moment, making it work fast is a
> definite second priority, I would think.

But choosing an implementation that _can_be_ made to work fast is not.

> Once it's working right, one can always replace the internals with
> something else that does the same job but is more efficient.

I still think that choosing the right implementation can also help in
making it work right.

> > > 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.
> 
> No, it's conceptually hard. Not all constraints are implemented with
> just a unique index you know. And changing a constraint means you have
> to check all the child tables, etc. etc. It's difficult just to track
> down down all the things you have to try to preserve.

It may be a lot of work, but not _conceptually_ hard. Conceptually you
have to do the same thing as for a single table, but just for all
inherited tables.

> Not to mention,
> there's always the question of what happens to triggers and suchlike
> when handed a tuple with extra columns from what it expects, and having
> it modify the insert into a different table.

IMHO that the trigger should not be aware of underlying implementation -
so it needs not worry about modifying the insert into a different table.

> The beauty of storing all supertable columns in the supertable itself is
> that the behaviour is automatically correct.

But "automatically correct" may not be what you want ;)

What about trigger that generates a cached printname using function
printname(row) that is different for each table - here you definitely do
not want to run the function defined for base table for anything
inherited.

> > 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.
> 
> So what you're saying is that each tuple in the table would have a
> format appropriate for its "subtype," and the table would be full of
> tuples of varying types? At first blush, that seems like a reasonable
> approach, if it can be done.

At least it makes some parts easier ;)

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

On 5 Sep 2002, Hannu Krosing wrote:

> > Oh, I see. Not that this is that big a deal, I think. Given that
> > it doesn't work correctly at the moment, making it work fast is a
> > definite second priority, I would think.
>
> But choosing an implementation that _can_be_ made to work fast is not.

I would say it definitely is. A correctly working implementation
can be replaced. An incorrectly working implementation destroys
data integrety.

Which is more important for PostgreSQL? Speed or maintaining data
integrity?

> > Not to mention,
> > there's always the question of what happens to triggers and suchlike
> > when handed a tuple with extra columns from what it expects, and having
> > it modify the insert into a different table.
>
> IMHO that the trigger should not be aware of underlying implementation -
> so it needs not worry about modifying the insert into a different table.

I agree.

> > The beauty of storing all supertable columns in the supertable itself is
> > that the behaviour is automatically correct.
>
> But "automatically correct" may not be what you want ;)
>
> What about trigger that generates a cached printname using function
> printname(row) that is different for each table - here you definitely do
> not want to run the function defined for base table for anything
> inherited.

Right. But that will be "automatically correct" when you store all
base data in the base table. It's when you start storing those data
in other tables that the trigger can get confused.

Or are you saying that when I insert a row into "just" a child
table, the trigger shouldn't be invoked on the "parent table"
portion of that insert? If so, I'd strongly disagree. If that
trigger is acting as an integrety constraint on the base table,
you might destroy the table's integrity.

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

>
> This is not so wrong. If you think about it, you have the same
> problem in most object-oriented programming languages: a person
> object can't generally easily become a subclass of itself after
> being created.
>
> This is a case, I would say, where you simply don't want to use
> inheritance. A person has-a job, not is-a job.
>

But a person is-a employee (allow me to momentarily step aside from the rules 
of english grammer, if you would), and a person is-a programmer. That's why I 
didn't call my table "job" :)  [1]

I don't like the way some OO programming languages handle objects, if they 
mean to say you can't change an object's type without performing a logical 
data copy to a new object. If you don't use some kind of extra layer of 
abstraction in C, you will end up with that problem: you'd need to copy all 
that RAM over to change from one struct to another. Most people would rather 
take that RAM copying hit than all the hits for allowing "room to expand" (at 
least in some applications). However, postgres needs to provide that "room to 
expand" for each tuple anyway, so to go through the same copying seems bad 
(especially since we're no longer just talking RAM). 

Take as an example python... it's easy to emulate other objects: just assign 
to the attribute, even if it's not there yet, it'll add the attribute. Same 
with python, it's providing room to expand for it's objects already, so why 
do all the copying? Now compare with Java, and see why you'd be annoyed. It 
has the facilities to change the objects all around, but you can't do it.

Even if you disregard all implementation details, and assume that the database 
is intelligent enough to not redundantly write data (and if you could name 
one such database, I would like to know), you're still doing something that 
doesn't logically make sense: you're deleting and inserting atomically, when 
the more obvious logical path is to expand on the data you already carry 
about an entity.

I like entities to be mutable, at least as far as makes sense to an 
application. Try telling an employee that as part of a promotion, they're 
going to be fired, lose their workstation, then be re-hired, and get a new 
workstation; I bet he'd have an interesting expression on his face (hey, at 
least postgres guarantees the "A" in ACID, or else bad things could happen to 
that poor guy :)

Thanks for responding, and I agreed with everything else you said. As you 
might have guessed, I don't much like "most object-oriented languages" if 
that's what they're going to try to tell me I have to do. Python works 
nicely, however :)

Regards,Jeff Davis

[1] Come to think of it, the JOIN operator seems to, at least on a first 
thought, represent the "has-a" relationship you describe. You could have the 
tuples "manager" and "programmer" in the table "job" and join with a "people" 
table. Don't ask about inheritance yet for this model, I'm still thinking 
about that one (does "has-a" even have an analogue to inheriteance?). Send me 
your thoughts about this, if you should have any.

On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > > Oh, I see. Not that this is that big a deal, I think. Given that
> > > it doesn't work correctly at the moment, making it work fast is a
> > > definite second priority, I would think.
> >
> > But choosing an implementation that _can_be_ made to work fast is not.
> 
> I would say it definitely is. A correctly working implementation
> can be replaced. An incorrectly working implementation destroys
> data integrety.
> 
> Which is more important for PostgreSQL? Speed or maintaining data
> integrity?

Both of course. The lack of one often makes the other unusable.

But as MySQL experience suggest, often people select speed over data
integrity. OTOH sometimes you happily accept a 10sec delay in updates to
have data integrity (like when doing a money transfer over internet;)

> > > Not to mention,
> > > there's always the question of what happens to triggers and suchlike
> > > when handed a tuple with extra columns from what it expects, and having
> > > it modify the insert into a different table.
> >
> > IMHO that the trigger should not be aware of underlying implementation -
> > so it needs not worry about modifying the insert into a different table.
> 
> I agree.
> 
> > > The beauty of storing all supertable columns in the supertable itself is
> > > that the behaviour is automatically correct.
> >
> > But "automatically correct" may not be what you want ;)
> >
> > What about trigger that generates a cached printname using function
> > printname(row) that is different for each table - here you definitely do
> > not want to run the function defined for base table for anything
> > inherited.
> 
> Right. But that will be "automatically correct" when you store all
> base data in the base table. It's when you start storing those data
> in other tables that the trigger can get confused.
> 
> Or are you saying that when I insert a row into "just" a child
> table, the trigger shouldn't be invoked on the "parent table"
> portion of that insert? If so, I'd strongly disagree.

Conceptually there are no "portions" of table - the trigger is invoked
on one _tuple_ exactly (pg has only row-level triggers), and each tuple
belongs to only one table regardless how it is implemented internally.

> If that
> trigger is acting as an integrety constraint on the base table,
> you might destroy the table's integrity.

What I try to say is that you should have the same freedom with triggers
that you have with select/insert/update/delete - you must be able to
choose if the trigger is on the parent table ONLY or on parent and all
children. 

And you should be able to override a trigger for child table even if it
is defined on parent as applying to all children - I guess that
overriding by trigger _name_ would be what most people expect.

Suppose you have a table CITIZEN with table-level constraint IS_GOOD
which is defined as kills_not_others(CITIZEN). and there is table
CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
(...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD so
you must either be able to override the trigger for that table (and its
children) or make sure that the functions used are dynamically mached to
the actual tuple type (header in Relational Model parlance) so that
kills_not_others(MILITARY) will be used, which presents the system
MILITARYs view of the being good ;)

What I'm after here is dynamic (and automatic) row level dispach of the
right function based on row type - so that for rows in CITIZEN or
CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
for rows in MILITAY the kills_not_others(MILITARY) is used.

--------- Hannu

On Thu, 2002-09-05 at 12:29, Jeff Davis wrote:
> >
> > This is not so wrong. If you think about it, you have the same
> > problem in most object-oriented programming languages: a person
> > object can't generally easily become a subclass of itself after
> > being created.
> >
> > This is a case, I would say, where you simply don't want to use
> > inheritance. A person has-a job, not is-a job.
> >
> 
> But a person is-a employee (allow me to momentarily step aside from the rules 
> of english grammer, if you would), and a person is-a programmer. That's why I 
> didn't call my table "job" :)  [1]
> 
> I don't like the way some OO programming languages handle objects, if they 
> mean to say you can't change an object's type without performing a logical 
> data copy to a new object. If you don't use some kind of extra layer of 
> abstraction in C, you will end up with that problem: you'd need to copy all 
> that RAM over to change from one struct to another. Most people would rather 
> take that RAM copying hit than all the hits for allowing "room to expand" (at 
> least in some applications). However, postgres needs to provide that "room to 
> expand" for each tuple anyway, so to go through the same copying seems bad 
> (especially since we're no longer just talking RAM). 

I would like to have UPDATEs both up and down the inheritance hierarchy,
so that when I have hierarchy

OBJECT(id serial primary key)+ HUMAN(name text,age int)  + EMPLOYEE(salary numeric)    + ENGINEER(workstation computer)
  + PHB(laptop computer)
 

and ENGINEER named Bob

I could do

UPDATE ENGINEER    TO PHB  SET salary = salary * 2 + age * 1000,      laptop.disksize = max(laptop.disksize ,
                workstation.disksize + 1000000)WHERE name='Bob'
 
;

to promote Bob from an engineer to phb, give him a salary rise and a
laptop with default configuration ensuring big enough disk to keep all
his old files, but still keep all FK related records.

> Take as an example python... it's easy to emulate other objects: just assign 
> to the attribute, even if it's not there yet, it'll add the attribute. Same 
> with python, it's providing room to expand for it's objects already, so why 
> do all the copying?

that's unless you use the new-style objects and __slots__

>>> class myobj(object):
...     __slots__ = ['a','b']
...    
>>> M = myobj()
>>> M.a =1
>>> M.c =1
Traceback (most recent call last): File "<stdin>", line 1, in ?
AttributeError: 'myobj' object has no attribute 'c'
>>> 

> Same with python, it's providing room to expand for it's objects already,
> so why do all the copying?


> [1] Come to think of it, the JOIN operator seems to, at least on a first 
> thought, represent the "has-a" relationship you describe. You could have the 
> tuples "manager" and "programmer" in the table "job" and join with a "people" 
> table. Don't ask about inheritance yet for this model, I'm still thinking 
> about that one (does "has-a" even have an analogue to inheriteance?).

Not in inheritance, but in OO world attributes are used to express has-a
relations. So
bob = people(name='Bob')bob.job = job('Manager')

makes an has-a relation between Bob and his job in python

BTW, good programming guidelines in python tell you not to test if bob
is-a something but rather test if the interface for something exists -
to see if you can iterate over bob you do not test if bob is a sequence
but just try it:

try:   for idea in bob:       examine(idea)
except TypeError:   print 'Failed to iterate over %s %s !' % (bob,job.name, bob.name)

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

I really like Hannu's idea of storing an entire (single-inheritance)
hierarchy in a single file.

I guess the question we need to ask ourselves is if we're prepared to
abandon support of multiple inheritance.  Personally I am, but...
        regards, tom lane

On Thu, Sep 05, 2002 at 10:23:02AM -0400, Tom Lane wrote:
> I really like Hannu's idea of storing an entire (single-inheritance)
> hierarchy in a single file.

Wouldn't this require solving the ALTER TABLE ADD COLUMN (to parent)
column ordering problem? 

> I guess the question we need to ask ourselves is if we're prepared to
> abandon support of multiple inheritance.  Personally I am, but...

No opinion - I've not used the inheritance much, since I'm not willing to
give up referential integrity.

Ross

On Thu, 2002-09-05 at 19:23, Tom Lane wrote:
> I really like Hannu's idea of storing an entire (single-inheritance)
> hierarchy in a single file.
> 
> I guess the question we need to ask ourselves is if we're prepared to
> abandon support of multiple inheritance.  Personally I am, but...

So am I, but I think we should move in stages -

1) first implement the SQL99 standard   CREATE TABLE mytable() UNDER parenttable ;  using the above idea and make it
workright vs constraints,  triggers, functions, etc.
 
  This should include the ability to include other table structures  using LIKE :
  CREATE TABLE engine(...);  CREATE TABLE vehicule(...);  CREATE TABLE car (     model text,     wheels  wheel[],
LIKEengine,  ) UNDER vehicule;
 
  which could then hopefully be used for migrating most code of form
  CREATE TABLE car (     model text primary key,     wheels  wheel[]  ) INHERITS (vehicule, engine);
  it would be nice (maybe even neccessary) to keep the current  functionality that columns introduced by LIKE are
automatically added/renamed/deleted when LIKE's base table changes.
 

2) when it is working announce non-SQL99-standard-and-broken INHERITS  to be deprecated and removed in future.

3) give people time for some releases to move over to UNDER + LIKE .  Or if someone comes up with bright
ideas/impementationsfor fixing  multiple inheritance, then un-deprecate and keep it.
 

4) else try to remove INHERITS.

5) if too many people object, goto 3)  ;)

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

On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote:
> On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> > On 5 Sep 2002, Hannu Krosing wrote:

> > If that
> > trigger is acting as an integrety constraint on the base table,
> > you might destroy the table's integrity.
>
> What I try to say is that you should have the same freedom with triggers
> that you have with select/insert/update/delete - you must be able to
> choose if the trigger is on the parent table ONLY or on parent and all
> children.

Sounds like a mechanism to make the distinction between virtual (child
can override parent) and non-virtual (child is constrained by the
parent) constraints are needed.

After all, there are two basic needs for constraints.  One is for
relational integrity and the other is business rule integrity.  That is,
one seeks to ensure that the database makes sense in respect to the data
model (a shoe is a product) while the other is to enforce business rules
(products are never free).  Seems like the DBA should be able to dictate
which domain his constraint falls into in some manner.

>
> And you should be able to override a trigger for child table even if it
> is defined on parent as applying to all children - I guess that
> overriding by trigger _name_ would be what most people expect.
>

That's the reason I used virtual and non-virtual above.  If we think
using C++ idioms, the child is stuck with it if it's deemed
non-virtual.  Generally speaking, if someone designed something with
that expectation in mind, there's probably a good reason for it.  In
this case, we could assume that such non-virtual constraints would be to
help ensure proper RI.  Something that otherwise, IMO, would be tossed
out with the bath water.

> What I'm after here is dynamic (and automatic) row level dispach of the
> right function based on row type - so that for rows in CITIZEN or
> CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
> for rows in MILITAY the kills_not_others(MILITARY) is used.

I think we're touching on some form of RTTI information here.  That is,
triggers and even functions may need to be able to dynamically determine
the row type that is actively being worked on.

If we're on the same page, I think that seemingly makes a lot of sense.

What about the concept of columns being public or private?  That is,
certain columns may not be inherited by a child?  Any thought to such a
concept?  Perhaps different types of table inheritance can be considered
in our model...has-a, is-a, etc...


Regards,
Greg Copeland

On Fri, 2002-09-06 at 03:19, Greg Copeland wrote:
> On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote:
> > On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> > > On 5 Sep 2002, Hannu Krosing wrote:
> 
> > > If that
> > > trigger is acting as an integrety constraint on the base table,
> > > you might destroy the table's integrity.
> > 
> > What I try to say is that you should have the same freedom with triggers
> > that you have with select/insert/update/delete - you must be able to
> > choose if the trigger is on the parent table ONLY or on parent and all
> > children. 
> 
> Sounds like a mechanism to make the distinction between virtual (child
> can override parent) and non-virtual (child is constrained by the
> parent) constraints are needed.
> 
> After all, there are two basic needs for constraints.  One is for
> relational integrity and the other is business rule integrity.  That is,
> one seeks to ensure that the database makes sense in respect to the data
> model (a shoe is a product) while the other is to enforce business rules
> (products are never free).  Seems like the DBA should be able to dictate
> which domain his constraint falls into in some manner.
>
> > And you should be able to override a trigger for child table even if
it
> > is defined on parent as applying to all children - I guess that
> > overriding by trigger _name_ would be what most people expect.
> > 
> 
> That's the reason I used virtual and non-virtual above.  If we think
> using C++ idioms, the child is stuck with it if it's deemed
> non-virtual.  Generally speaking, if someone designed something with
> that expectation in mind, there's probably a good reason for it.  In
> this case, we could assume that such non-virtual constraints would be to
> help ensure proper RI.  Something that otherwise, IMO, would be tossed
> out with the bath water.

I agree to this.

What I described (making overriding decision solely in child) is
probably a bad idea.

> > What I'm after here is dynamic (and automatic) row level dispach of the
> > right function based on row type - so that for rows in CITIZEN or
> > CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
> > for rows in MILITAY the kills_not_others(MILITARY) is used.
> 
> I think we're touching on some form of RTTI information here.  That is,
> triggers and even functions may need to be able to dynamically determine
> the row type that is actively being worked on.

Should be easy if the row comes directly from a table : just use
tableoid column.

> If we're on the same page, I think that seemingly makes a lot of sense.
> 
> What about the concept of columns being public or private?  That is,
> certain columns may not be inherited by a child?  Any thought to such a
> concept?  Perhaps different types of table inheritance can be considered
> in our model...has-a, is-a, etc...

I can't fit this in my mental model of table inheritance for two reasons

1) all parent table columns must be present in child

2) granting some right to parent should automatically allow selecting
from children

both are required for select/insert/update/delete to work on table and
its children (i.e. without ONLY)


But maybe i just need to think more about it ;)

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

On 5 Sep 2002, Hannu Krosing wrote:

> Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> which is defined as kills_not_others(CITIZEN). and there is table
> CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....

This I very much disagree with.

In most object-oriented languages (Eiffel being a notable exception, IIRC),
you can't specify constraints on objects. But in a relational database,
you can specify constraints on tables, and it should *never* *ever* be
possible to violate those constraints, or the constraints are pointless.

So if I have a constraint that says, "no rows appearing in this
table will ever violate constraint X," and then you go and create
a way of inserting rows into that table that violate that constraint,
I think you've just made the database into a non-relational database.
I really don't want to break postgres' relational side for some
inheritance features of dubious utility. Constraints should be explicitly
removed from tables if they are no longer needed, not implicitly removed
through the creation of another table.

I think we should settle this point before going any further.

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

On 5 Sep 2002, Greg Copeland wrote:

> Sounds like a mechanism to make the distinction between virtual (child
> can override parent) and non-virtual (child is constrained by the
> parent) constraints are needed.

Oh, I should mention that I have no problem with being able to declare a
constraint "overridable" by subtables, so long as it's not the default,
and it's clear from the table definition that it might be overridden.

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

On Thu, 5 Sep 2002, Jeff Davis wrote:

> But a person is-a employee (allow me to momentarily step aside from
> the rules of english grammer, if you would), and a person is-a
> programmer. That's why I didn't call my table "job" :) [1]

Certainly it's not the case that a person is-a job, by virtue of the
fact that a person can have no job. Nor is it the case that a person
is-a programmer; not all people are programmers.

Perhaps you're reversing the sense of "is-a"? One says "subtype is-a
supertype," not "supertype is-a subtype."

But even reversing these, it's not the case that job is-a person, by
virtue of the fact that you cannot use a job anywhere you can use a
person. (A person can file his tax return, a job can't.) That might be a
matter of bad mappings of object names to real-world concepts, though.

As for "programmer is-a person," yes, you could model things that way if
you really wanted to. But it's a bad way to do it because, as you point
out, a person can change his job, or not have a job. Now what do you do
with that programmer-subtype-of-person object you created? I think in
this case English misled you: we do say that "he is a programmer," but
what we really mean is that "one of the characteristics of that person
is that he programs." So create a separate characteristic type and have
the person object "have-a" as many or as few of those characteristics as
you need.

> I don't like the way some OO programming languages handle objects, if they
> mean to say you can't change an object's type without performing a logical
> data copy to a new object.

That's not a problem with the programming language; that's you
modelling things badly.

> Take as an example python... it's easy to emulate other objects: just assign
> to the attribute, even if it's not there yet, it'll add the attribute. Same
> with python, it's providing room to expand for it's objects already, so why
> do all the copying? Now compare with Java, and see why you'd be annoyed. It
> has the facilities to change the objects all around, but you can't do it.

Yes, you can't do it in Java because you Can't Do It in a language where
you can specify static typing. If I have field that holds a String, I'm
given a guarantee that, if I can put a reference in that field, it is
and always will be a String.

In non-statically-typed languages that give you the option of changing
types, you might give a referenc to a string, change the objects type on
me, and then I might blow up when I try to use it later. These bugs tend
to be quite difficult to track down because the source and manifestation
of the problem can be widely separated in code and in time. That's why
most languages don't allow this.

> ...when the more obvious logical path is to expand on the data you
> already carry about an entity.

Yes, that's the perfectly obvious path. And that's just what the
relational model lets us do, and do very well.

Why do you want to use an ill-fitting, error-prone model when you've
already got something that works better?

> [1] Come to think of it, the JOIN operator seems to, at least on a first
> thought, represent the "has-a" relationship you describe.

You bet! Hey, this relational stuff doesn't suck so badly after
all, does it? Especially for a 30-year old theory. :-)

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

On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> > which is defined as kills_not_others(CITIZEN). and there is table
> > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....
> 
> This I very much disagree with.
> 
> In most object-oriented languages (Eiffel being a notable exception, IIRC),
> you can't specify constraints on objects. But in a relational database,
> you can specify constraints on tables, and it should *never* *ever* be
> possible to violate those constraints, or the constraints are pointless.

That's not how real world (which data is supposed to model) operates ;)

As Greg already pointed out, there are two kinds of constraints -
database integrity constraints (foreign key, unique, not null, check),
which should never be overridden and business-rule constraints which
should be overridable in child tables.

one can argue that the latter are not constraints at all, but they sure
look like constraints to me ;)

To elaborate on Gregs example if you have table GOODS and under it a
table CAMPAIGN_GOODS then you may place a general overridable constraint
valid_prices on GOODS which checks that you dont sell cheaper than you
bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
you override the constraint for CAMPAIGN_GOODS.

> So if I have a constraint that says, "no rows appearing in this
> table will ever violate constraint X," and then you go and create
> a way of inserting rows into that table that violate that constraint,
> I think you've just made the database into a non-relational database.

SQL standard constraints should be non-overridable. I still think that
Constraint triggers should be overridable/dynamic. 

Or maybe it is better to just make the check function should be
dynamically dispatched, so the constraint will always hold, it just can
mean different things for different types.

> I really don't want to break postgres' relational side for some
> inheritance features of dubious utility. Constraints should be explicitly
> removed from tables if they are no longer needed, not implicitly removed
> through the creation of another table.
> 
> I think we should settle this point before going any further.

It seems that the dynamic dispatch of trigger function should be enough
for business-rule constraints. 

And it is also simpler and cleaner (both conceptually and to implement)
if constraints themselves are not overridable.

So in my CAMPAIGN_GOODS example you just have different
valid_prices(GOODS) and valid_prices(CAMPAIGN_GOODS), but one constraint
on GOODS which states that price must be valid . 

Doing it this way ensures that you are not able to have a record in
GOODS for which valid_price(ROW) does not hold.

If you don't want inherited tables to be able to override valid_price()
use it in CHECK constraint in GOODS, which should use the
valid_prices(cast(ROW as GOODS)) for any inherited type.

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

On 6 Sep 2002, Hannu Krosing wrote:

> > In most object-oriented languages (Eiffel being a notable exception, IIRC),
> > you can't specify constraints on objects. But in a relational database,
> > you can specify constraints on tables, and it should *never* *ever* be
> > possible to violate those constraints, or the constraints are pointless.
>
> That's not how real world (which data is supposed to model) operates ;)

Sure it is. Please don't blame the language for being wrong when you
incorrectly model things for your purposes. To chose a much simpler
and more obvious example: if you stored birthdate as a date only, and
someone complained that you're not born all day, but at a particular
time on that day, you don't blame the language for having the date type
not store the time of day. You fix your problem to use both a date and a
time to store that value.

If the language specifies that contstraints on tables are not to be
violated, then don't use those constraints when you don't want them.

> To elaborate on Gregs example if you have table GOODS and under it a
> table CAMPAIGN_GOODS then you may place a general overridable constraint
> valid_prices on GOODS which checks that you dont sell cheaper than you
> bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> you override the constraint for CAMPAIGN_GOODS.

This looks like a classic case of incorrect modelling to me. Does the
good itself change when it becomes a campaign_good? No. The price
changes, but that's obviously not an integral part of the good itself.
So separate your price information from your good information, and then
you can do things like have campaign prices, multiple prices per good
(since you probably want to keep the original price information as
well), and so on.

I'm really getting the feeling a lot of these applications that
want table inheritance want it just to be different, not because
it provides anything useful.

I am completely committed to object-oriented programming, and use
inheritance heavily, so it's not that I don't understand or like the
concepts. But just because a concept works well in one type of use does
not mean it will do any good, or even not do harm, when brought into a
completely different world.

> SQL standard constraints should be non-overridable. I still think that
> Constraint triggers should be overridable/dynamic.

I still don't like it. Eiffel had good reasons for making the
constraints non-overridable. Other OO languages don't have constraints,
or they would probably do the same.

That said, I could live with dynamic dispatch, if the default were
to make it non-dynamic, and you had to add a special flag to make it
dynamic. That way it would be obvious to the casual user or a DBA
familiar with other databases but not postgres that something unusual is
going on.

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

On Fri, 2002-09-06 at 09:53, Curt Sampson wrote:
> 
> If the language specifies that contstraints on tables are not to be
> violated, then don't use those constraints when you don't want them.

But what _should_ i use then if i want the same business rule on most
top-level types, but a changed one on some down the hierarchy ?
> > To elaborate on Gregs example if you have table GOODS and under it a
> > table CAMPAIGN_GOODS then you may place a general overridable constraint
> > valid_prices on GOODS which checks that you dont sell cheaper than you
> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> > you override the constraint for CAMPAIGN_GOODS.
> 
> This looks like a classic case of incorrect modelling to me. Does the
> good itself change when it becomes a campaign_good? No. The price
> changes, but that's obviously not an integral part of the good itself.

Perhaps we mean different things by good. I meant a GOOD to be a THING 
bought with the purpose of reselling. Price (actually prices: 
selling_price and buying_price) is what makes it a GOOD and thus it is
an integral part of it.

> So separate your price information from your good information, and then
> you can do things like have campaign prices, multiple prices per good
> (since you probably want to keep the original price information as
> well), and so on.

It does not solve the problem described above - the price at which the
good is soled is still constrained differently for orninary and campaign
goods.

in standard relational model you would make the distinction inside the
constraint (CHECK (selling_price > buying_price) OR is_campaign_good)
but this localises the check in wrong place - in OO model I'd expect it
to be possible to define the constraint near the child type, not change
the parent constraint each time I derive new child types.

> I'm really getting the feeling a lot of these applications that
> want table inheritance want it just to be different, not because
> it provides anything useful.

As with any other inheritance, it is just a way to organize stuff.

In case of being able to override constraints for child tables it can
also be a significant performance boost - if you have 10 000 000 goods
in a table you don't want to change a constraint on GOODS to allow
campaign goods to be sold cheaper than bought as it would have to check
all goods for validity according to new constraint - putting the
constraint on just CAMPAIGN_GOODS will enable the DB engine to check
just tuples in CAMPAIGN_GOODS.

> I am completely committed to object-oriented programming, and use
> inheritance heavily, so it's not that I don't understand or like the
> concepts. But just because a concept works well in one type of use does
> not mean it will do any good, or even not do harm, when brought into a
> completely different world.
 Surely great caution is needed when defining the desired behaviour.

> > SQL standard constraints should be non-overridable. I still think that
> > Constraint triggers should be overridable/dynamic.
> 
> I still don't like it. Eiffel had good reasons for making the
> constraints non-overridable. Other OO languages don't have constraints,
> or they would probably do the same.
> 
> That said, I could live with dynamic dispatch, if the default were
> to make it non-dynamic, and you had to add a special flag to make it
> dynamic. That way it would be obvious to the casual user or a DBA
> familiar with other databases but not postgres that something unusual is
> going on.

That seems about the right compromise between constraining and developer
freedom.

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

On Thu, 2002-09-05 at 15:51, Hannu Krosing wrote:
> On Fri, 2002-09-06 at 03:19, Greg Copeland wrote:
> >
> > What about the concept of columns being public or private?  That is,
> > certain columns may not be inherited by a child?  Any thought to such a
> > concept?  Perhaps different types of table inheritance can be considered
> > in our model...has-a, is-a, etc...
>
> I can't fit this in my mental model of table inheritance for two reasons
>
> 1) all parent table columns must be present in child

Okay, I must admit, I'm not really sure why.  If we look at it in a
physical versus logical manner, even if it's physically there, why must
it be logically exposed?  Can you help me understand why it would even
need to physically be there.  After all, if a child can't update it,
they don't need to see it.

>
> 2) granting some right to parent should automatically allow selecting
> from children

Unless the parent deemed it inappropriate access (private)?

If a column were deemed private, that would have a couple of
stipulations on it.  That is, it would have to ensure that "NOT NULL"
where not one of the constraints, or, if it did, ensure that a default
value were also provided.

>
> both are required for select/insert/update/delete to work on table and
> its children (i.e. without ONLY)
>
>
> But maybe i just need to think more about it ;)
>

Well, I guess I'm lagging behind you on this manner.  Perhaps "holding
my hand" and explaining it a bit will allow you to work through it some
more and help bring me in line with what you're thinking.

Greg

On Fri, 2002-09-06 at 07:53, Hannu Krosing wrote:
> On Fri, 2002-09-06 at 09:53, Curt Sampson wrote:
> > This looks like a classic case of incorrect modelling to me. Does the
> > good itself change when it becomes a campaign_good? No. The price
> > changes, but that's obviously not an integral part of the good itself.
>
> Perhaps we mean different things by good. I meant a GOOD to be a THING
> bought with the purpose of reselling. Price (actually prices:
> selling_price and buying_price) is what makes it a GOOD and thus it is
> an integral part of it.

No matter now you look at the example, invalidating it does not address
the issue raised as it still exists.  Either way, Hannu and I seem to
agree that some class of constraints need to be able to be overridden.

> In case of being able to override constraints for child tables it can
> also be a significant performance boost - if you have 10 000 000 goods
> in a table you don't want to change a constraint on GOODS to allow
> campaign goods to be sold cheaper than bought as it would have to check
> all goods for validity according to new constraint - putting the
> constraint on just CAMPAIGN_GOODS will enable the DB engine to check
> just tuples in CAMPAIGN_GOODS.

I had not considered this before.  Does that still hold true if we go
with a parent contains all columns implementation?  Of are you simply
saying that it doesn't matter as when the constraint were applied it
would only scan the rows the below to the child?  Perhaps this doesn't
matter for this portion of the conversation.  But hey, I was curious.
:)

>
> > > SQL standard constraints should be non-overridable. I still think that
> > > Constraint triggers should be overridable/dynamic.
> >
> > I still don't like it. Eiffel had good reasons for making the
> > constraints non-overridable. Other OO languages don't have constraints,
> > or they would probably do the same.

Well Curt, as you outlined above (clipped out) about it being a
different world...I think also applies here.  IMO, we are treading
lightly on new and perhaps thin ground so we need to be careful that we
apply common parallels and idioms only we are certain that they need
apply.  What I'm trying to say is, just because it's not allowed in
Eiffel does have to mean the same applies here.

> >
> > That said, I could live with dynamic dispatch, if the default were
> > to make it non-dynamic, and you had to add a special flag to make it
> > dynamic. That way it would be obvious to the casual user or a DBA
> > familiar with other databases but not postgres that something unusual is
> > going on.
>
> That seems about the right compromise between constraining and developer
> freedom.
>

I agree.  That does appear to be pointing us in a conservatively sane
and safe direction.


Greg

> On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> > On 5 Sep 2002, Hannu Krosing wrote:
> > 
> > > Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> > > which is defined as kills_not_others(CITIZEN). and there is table
> > > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> > > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....
> > 
> > This I very much disagree with.
> > 
> > In most object-oriented languages (Eiffel being a notable exception, IIRC),
> > you can't specify constraints on objects. But in a relational database,
> > you can specify constraints on tables, and it should *never* *ever* be
> > possible to violate those constraints, or the constraints are pointless.
> 
> That's not how real world (which data is supposed to model) operates ;)
> 
> As Greg already pointed out, there are two kinds of constraints -
> database integrity constraints (foreign key, unique, not null, check),
> which should never be overridden and business-rule constraints which
> should be overridable in child tables.
> 
> one can argue that the latter are not constraints at all, but they sure
> look like constraints to me ;)
> 
> To elaborate on Gregs example if you have table GOODS and under it a
> table CAMPAIGN_GOODS then you may place a general overridable constraint
> valid_prices on GOODS which checks that you dont sell cheaper than you
> bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> you override the constraint for CAMPAIGN_GOODS.

What that tells me is that the constraint, valid_prices, shouldn't have been 
on GOODS in the first place.  If it is not a legitimate constraint for the 
children, then it is not a legitimate constraint for the parent.

In human inheritance, if you marry someone with "funny coloured skin," you 
don't get to choose that your children won't have "funny coloured skin."  
That's a pretty forcible "constraint."  :-).

For the GOODS situation, the constraint ought not to be on GOODS in the first 
place.  There ought to be a table ORDINARY_GOODS, or some such thing, to which 
the constraint applies, and from which CAMPAIGN_GOODS will _not_ be inheriting.

> > So if I have a constraint that says, "no rows appearing in this
> > table will ever violate constraint X," and then you go and create
> > a way of inserting rows into that table that violate that constraint,
> > I think you've just made the database into a non-relational database.
> 
> SQL standard constraints should be non-overridable. I still think that
> Constraint triggers should be overridable/dynamic. 
> 
> Or maybe it is better to just make the check function should be
> dynamically dispatched, so the constraint will always hold, it just can
> mean different things for different types.

Or maybe if someone is doing an Object Oriented design, and making extensive 
use of inheritance, they'll need to apply constraints in a manner that allow 
them to be properly inherited.
--
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://cbbrowne.com/info/
If a cow laughed, would milk come out its nose? 

On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote:
> > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> > > On 5 Sep 2002, Hannu Krosing wrote:
> >
> > To elaborate on Gregs example if you have table GOODS and under it a
> > table CAMPAIGN_GOODS then you may place a general overridable constraint
> > valid_prices on GOODS which checks that you dont sell cheaper than you
> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> > you override the constraint for CAMPAIGN_GOODS.
>
> What that tells me is that the constraint, valid_prices, shouldn't have been
> on GOODS in the first place.  If it is not a legitimate constraint for the
> children, then it is not a legitimate constraint for the parent.
>

I don't agree with you on that point.  This concept is common to many
OO-implementations.  Unless you can come up with a powerful argument as
to why our "to-be" picture should never do this, I'm less than
convinced.

> In human inheritance, if you marry someone with "funny coloured skin," you
> don't get to choose that your children won't have "funny coloured skin."
> That's a pretty forcible "constraint."  :-).
>

Fine, but that only works for YOUR specific example.  In that example,
the color constraint should be non-virtual, meaning, the child should
not be able to change it.  On the other hand, if I replace "human" with
"metal product", hopefully I won't be stuck with gun metal gray for
every derived product.  Hopefully, somewhere along the lines, I'll be
able to override the parent's color constraint.

> > Or maybe it is better to just make the check function should be
> > dynamically dispatched, so the constraint will always hold, it just can
> > mean different things for different types.
>
> Or maybe if someone is doing an Object Oriented design, and making extensive
> use of inheritance, they'll need to apply constraints in a manner that allow
> them to be properly inherited.

The problem with that assumption is that there is normally nothing wrong
with having seemingly mutually exclusive sets of *business rules* for a
parent and child.

Greg

Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall:
> --=-eu74lKXry3SVx8eZ/qBD
> Content-Type: text/plain
> Content-Transfer-Encoding: quoted-printable
> On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote:
>> > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
>> > > On 5 Sep 2002, Hannu Krosing wrote:
>> > To elaborate on Gregs example if you have table GOODS and under it a
>> > table CAMPAIGN_GOODS then you may place a general overridable constraint
>> > valid_prices on GOODS which checks that you dont sell cheaper than you
>> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
>> > you override the constraint for CAMPAIGN_GOODS.

>> What that tells me is that the constraint, valid_prices, shouldn't
>> have been on GOODS in the first place.  If it is not a legitimate
>> constraint for the children, then it is not a legitimate constraint
>> for the parent.

> I don't agree with you on that point.  This concept is common to
> many OO-implementations.  Unless you can come up with a powerful
> argument as to why our "to-be" picture should never do this, I'm
> less than convinced.

If the plan is for table CAMPAIGN_GOODS to virtually be a view on GOODS,
then I'd say it _is_ necessary.

>> In human inheritance, if you marry someone with "funny coloured skin," yo=
> u=20
>> don't get to choose that your children won't have "funny coloured skin."=
> =20=20
>> That's a pretty forcible "constraint."  :-).
>>=20

Is there something broken with your mailer?  It's reformatting quotes
rather horribly...

> Fine, but that only works for YOUR specific example.  In that
> example, the color constraint should be non-virtual, meaning, the
> child should not be able to change it.  On the other hand, if I
> replace "human" with "metal product", hopefully I won't be stuck
> with gun metal gray for every derived product.  Hopefully, somewhere
> along the lines, I'll be able to override the parent's color
> constraint.

That happens by _adding_ an additional characteristic, presumably that
of "what kind of paint the metal is covered with."  That doesn't
override the fundamental constraint that if it's a metal product,
there _will_ be metallic properties.

If you decide to add in some "non-metallic" products, then it would be
_silly_ to have them inherit all their characteristics from
"METAL_PRODUCTS;" they should head back up the class hierarchy and
inherit their basic characteristics from the _appropriate_ parent.

Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't
the appropriate parent class for CAMPAIGN_GOODS.  Both should be
inheriting the common characteristics from some common ancestor.  If
that is done, then there's nothing to "override."

>> > Or maybe it is better to just make the check function should be
>> > dynamically dispatched, so the constraint will always hold, it just can
>> > mean different things for different types.
>>=20
>> Or maybe if someone is doing an Object Oriented design, and making extens=
> ive=20
>> use of inheritance, they'll need to apply constraints in a manner that al=
> low=20
>> them to be properly inherited.

> The problem with that assumption is that there is normally nothing
> wrong with having seemingly mutually exclusive sets of *business
> rules* for a parent and child.

If the rules are totally different, it begs the question of why they
_should_ be considered to be related in a "parent/child" relationship.

It may well be that they _aren't_ related as "parent/child."  They may
merely be "cousins," sharing some common ancestors.
-- 
(concatenate 'string "chris" "@cbbrowne.com")
http://cbbrowne.com/info/spreadsheets.html
"Note that if I can get you  to `su and say' something just by asking,
you have a very serious security problem on your system and you should
look into it."  -- Paul Vixie, vixie-cron 3.0.1 installation notes

There was a comment earlier that was not really addressed.
What can you do with table inheritance that you can not do
with a relational implementation?  Or what would work *better*
as inheritance?  (you define better)

This is a genuine question, not a snarky comment.  I really
want to know.  This is the reason I can think of to use
inheritance: Several tables have a common set of attributes and
there is some reason for these tables to be separate AND there
is some reason for the common columns to be queried en masse.
What kinds of "some reasons" are there, though?  And if my
condition for using table inheritance is lacking or misguided, what should
be the criteria for using table inheritance?

Creating indexes across tables is a project.  Is it the most important
project?  Will it benefit the most users?  Will it benefit any users?
Theory is great and important, but if no one uses the functionality,
who cares?  If these changes will enable people to use the functionality
that until now had been too much of a PITA then it might be worth
it.  However,  I suspect the majority of people who would use these
changes are participating in these discussions.

These features were never widely used in Illustra nor Informix although
their implementations were a little smoother imho.

To weigh in on the constraints issues, it seems problematic
that currently some constraints (check) are inherited and
others are not (foreign keys).  The chcheers,oice of which ones are
or aren't is clear to people familiar with the implementation
but what about the rest of the world who just want some
consistent rule.

I also agree with the people who say, if we inherit constrainsts,
then we must be able to override them in the subtables.
I like the suggested "LOCAL" keyword, myself.

cheers,

elein


:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:    elein@norcov.com   (510)543-6079    "Taking a Trip. Not taking a
Trip."--anonymous
 
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:

On Fri, 2002-09-06 at 11:05, cbbrowne@cbbrowne.com wrote:
> Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall:
> >> That's a pretty forcible "constraint."  :-).
> >>=20
>
> Is there something broken with your mailer?  It's reformatting quotes
> rather horribly...

Hmm...not that I know off.  Never had complaints before anyways.  Looks
like an issue with MIME contents...perhaps your mailer doesn't properly
parse some MIME and/or mine is hosing it some how.  Not really sure.

> Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't
> the appropriate parent class for CAMPAIGN_GOODS.  Both should be
> inheriting the common characteristics from some common ancestor.  If
> that is done, then there's nothing to "override."
>

You can complain about and redefine the model to suit your needs all day
long and get no where.  It doesn't change the need for it.  Fact is, it
would be nice to allow.  Fact is, OO-implementations tend to allow
this.  I'm quite happy to let you go to every OO computer language camp
and inform them that they've done it all wrong.  ;)

Citing that a specific example is all wrong hardly invalidates the
concept.  Since we are pretty much at the conceptual stage, I welcome a
conceptual argument on why this is bad and should never be done.
Please, be high level and generic.  After all, I too can give you a
hundred specific reasons why a cat is not dog (i.e. bad model)...but it
does nothing to facilitate the topic at hand.

> > The problem with that assumption is that there is normally nothing
> > wrong with having seemingly mutually exclusive sets of *business
> > rules* for a parent and child.
>
> If the rules are totally different, it begs the question of why they
> _should_ be considered to be related in a "parent/child" relationship.

Because this is how the real world works.  Often there are exceptions to
the rules.  When these rules differ, I've not seen a valid high level
conceptual reason that should prevent it.

Example:

animalquadruped (has 4-trunk limbs)    dog    injuredDog (has 0 or more trunk limbs)

Hopefully we can agree that a dog is still a dog even if it only has
three legs?  Hopefully you'll realize this was given to illustrate an
example and to prove a point.  Sometimes a model needs to allow for
exceptions to the rule.  You can argue that a three-legged dog is no
longer a quadruped but I prefer to believe that it is a quadruped which
just happens to be an exception to the rule.
>
> It may well be that they _aren't_ related as "parent/child."  They may
> merely be "cousins," sharing some common ancestors.

Yes, it's true.  Sometimes the wrong model is applied but that hardly
invalidates the concept or alleviates the need.

Regards,
Greg Copeland

On Fri, 2002-09-06 at 19:00, elein wrote:
> 
> 
> There was a comment earlier that was not really addressed.
> What can you do with table inheritance that you can not do
> with a relational implementation?  Or what would work *better*
> as inheritance?  (you define better)

There is nothing that you cannot do in some way; that way may not be
very convenient compared to the use of inheritance.  I consider
simplicity to be preferable to conceptual purity.

> This is a genuine question, not a snarky comment.  I really
> want to know.  This is the reason I can think of to use
> inheritance: Several tables have a common set of attributes and
> there is some reason for these tables to be separate AND there
> is some reason for the common columns to be queried en masse.
> What kinds of "some reasons" are there, though?  And if my
> condition for using table inheritance is lacking or misguided, what should
> be the criteria for using table inheritance?

I use it when a group of tables are closely related; they are all
members of some higher class.  For example:
                              person <.......................> address                                 |
 +--------------+--------------+                  |                             |            organisation
   individual <......> pay_tax                  |                             |         +--------+--------+
+---------+---------+        |        |        |          |         |         |    customer  supplier ...etc...   staff
homeworker ...etc...         |    +----+-------------+    |                  |
 
home_customer    export_customer

It is convenient to use a higher class when you are interested in all
its members and only in the attributes of the higher class.  So I can
say
  SELECT * FROM person,address   WHERE address.person = person.id AND         address.town = 'London';

to get all rows for people in London.  I will only get those attributes
that are in person itself; if I want to know about credit limits, that
is only relevant in the customer hierarchy and I have to SELECT from
customer instead..

Similarly, I can use the whole customer hierarchy when changing or
reporting on outstanding customer balances.

If foreign key relations were valid against an inheritance tree, I could
implement it for a table of addresses referencing the highest level
(every person has an address) and of pay and tax records at the
individual level.  These don't change as you go down the hierarchy, but
a purely relational implementation has to be redone at each level.  A
reciprocal relation requires an extra table to hold all the hierarchy's
keys and that in turn needs triggers to keep that table maintained.
(I.e., person should have a FK reference to address and address to
person; instead, address needs a reference to person_keys, which I have
to create because FK against a hierarchy isn't valid.)  The lack of
inherited RI makes the design more complex and more difficult to
understand.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "For whosoever shall call upon the name of the Lord      shall be saved."
    Romans 10:13 
 

At 08:33 AM 9/7/02, Oliver Elphick wrote:
>On Fri, 2002-09-06 at 19:00, elein wrote:
> >
> >
> > There was a comment earlier that was not really addressed.
> > What can you do with table inheritance that you can not do
> > with a relational implementation?  Or what would work *better*
> > as inheritance?  (you define better)
>
>There is nothing that you cannot do in some way; that way may not be
>very convenient compared to the use of inheritance.  I consider
>simplicity to be preferable to conceptual purity.

yes, simplicity is a very reasonable criteria for better.

> > This is a genuine question, not a snarky comment.  I really
> > want to know.  This is the reason I can think of to use
> > inheritance: Several tables have a common set of attributes and
> > there is some reason for these tables to be separate AND there
> > is some reason for the common columns to be queried en masse.
> > What kinds of "some reasons" are there, though?  And if my
> > condition for using table inheritance is lacking or misguided, what should
> > be the criteria for using table inheritance?

In non-OO terms, you have both reasons for tables to
be separate and reasons to query an entire hierarchy.
Your exact reasons are clear and reasonable.
This is helpful.

>I use it when a group of tables are closely related; they are all
>members of some higher class.  For example:
>
>                                person <.......................> address
>                                   |
>                    +--------------+--------------+
>                    |                             |
>              organisation                   individual <......> pay_tax
>                    |                             |
>           +--------+--------+          +---------+---------+
>           |        |        |          |         |         |
>      customer  supplier ...etc...   staff  homeworker ...etc...
>           |
>      +----+-------------+
>      |                  |
>home_customer    export_customer
>
>It is convenient to use a higher class when you are interested in all
>its members and only in the attributes of the higher class.  So I can
>say
>
>    SELECT * FROM person,address
>     WHERE address.person = person.id AND
>           address.town = 'London';
>
>to get all rows for people in London.  I will only get those attributes
>that are in person itself; if I want to know about credit limits, that
>is only relevant in the customer hierarchy and I have to SELECT from
>customer instead..
>
>Similarly, I can use the whole customer hierarchy when changing or
>reporting on outstanding customer balances.

I don't think table inheritance will "go away" and so being
consistent about the indexes and constraints is necessary
in order to keep its usage simpler.  This might lessen the PITA
factor for a few more people, but we should prioritize the
project.  I think few people have put the thought into it that
you have.


>If foreign key relations were valid against an inheritance tree, I could
>implement it for a table of addresses referencing the highest level
>(every person has an address) and of pay and tax records at the
>individual level.  These don't change as you go down the hierarchy, but
>a purely relational implementation has to be redone at each level.  A
>reciprocal relation requires an extra table to hold all the hierarchy's
>keys and that in turn needs triggers to keep that table maintained.
>(I.e., person should have a FK reference to address and address to
>person; instead, address needs a reference to person_keys, which I have
>to create because FK against a hierarchy isn't valid.)  The lack of
>inherited RI makes the design more complex and more difficult to
>understand.
>
>--
>Oliver Elphick                                Oliver.Elphick@lfix.co.uk
>Isle of Wight, UK
>http://www.lfix.co.uk/oliver
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "For whosoever shall call upon the name of the Lord
>       shall be saved."         Romans 10:13
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:    elein@norcov.com   (510)543-6079    "Taking a Trip. Not taking a
Trip."--anonymous
 
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: