Re: OOP real life example (was Re: Why is MySQL more

On Mon, 2002-08-12 at 11:52, Curt Sampson wrote:

On Sun, 11 Aug 2002, Don Baccus wrote:

Obviously it would require extending SQL, but since you in part argue
that SQL sucks in regard to the relational model this shouldn't matter,
right?

Well, if we're going to go so far as to get rid of SQL, we can go all
the way with the D&D thing, and VIEWs will no longer be syntatic sugar
because views and tables will be the same thing. (I'll leave you how
specify physical storage as an exercise for the reader. :-))

But anyway, I have no particularly huge objection to syntatic sugar
alone. I do have objections to it when it's not saving much typing. (It
is in this case, but that could be fixed with better automatic support
of view updates.)

But my real objection is when it makes things more confusing, rather
than less, which I think is definitely happening here.

What makes things more confusing is poor understanding of a feature, not
the feature itself.

I've never
seen a rigourous explanation of our model of table inheritance,
nor any model that was more obviously correct than another. And
the parallel drawn with inheritance in OO languages is a false
parallel that adds to the confusion.

Are you saying that inheritance in SQL is something fundamentally
different than inheritance in OO languages ?

(For example, the distinction
between types and instances of types is critical in OO theory. What are
the TI equivalants of this?)

If by TI you mean type instance then the equivalent of of an instance is
a relation (i.e. one row in an (inherited) table).

All this is borne out by the regular questions one sees about
inheritance in the mailing lists. I'll admit a good part of it is
due to the broken implementation of inheritance, but all of the
problems I've ever seen are easily solved with very simple relational
solutions.

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

Maybe the inheritance thing is causing people to turn off the relational
parts of their brain or something.

Of maybe people are diversifying, using inheritance for is-a
relationships and relational model for has-a relationships.

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

On Mon, 2002-08-12 at 13:14, Curt Sampson wrote:

On 12 Aug 2002, Hannu Krosing wrote:

Are you saying that inheritance in SQL is something fundamentally
different than inheritance in OO languages ?

Yes.

...

So is an instance a relation (a set of tuples) or a tuple?

An instance is a tuple. The relation is the Class. The relation header
is the class definition.

If the former, consider the following argument.

In an object oriented program I can have a class C, and a subclass C'
that inherits from C. Now, in any situation that calls for an instance
of C, I can instead use an instance of C'. This is polymorphism.

Now, if an instance is equivalant to tuple, and a relation inherits from
another relation, I'd guess that a relation is equivalant to a class.

Yes.

But given relation R' inheriting from relation R, does that mean that I
can use a tuple from R' anywhere I could use a tuple from R? No, obviously
not, as the two tuples have a different number of attributes, to start with.

The classes C and C' also have different number of 'attributes', but
what matters, is that C' has all the attributes that C has, so you can
use an instance of C' everywhere an instance of C is needed. The same is
true of table inheritance - tuple from R' has all the attributes that a
tuple from R has.

...

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

Can you please give me two or three examples of problems that are
not solved by simple relational solutions, and how table inheritance
solves them?

From implementors POW:

Updatable VIEWs

The subset of 'views' that inheritance creates are updatable by default
with no additional effort from the programmer. It is ready for
inheritance because it is inherently more difficult to solve the view
updatability problem for a general case than for the limited set of
views used by inheritance.

Of maybe people are diversifying, using inheritance for is-a
relationships and relational model for has-a relationships.

Well, it seems to me that the relational model better supports the is-a
relationship. With the relational model, I can specify a column in a
table that specifies what that particular entity is, and that can be set
to one and only one value.

When using inheritance both of these (defining and setting) are done
automatically.

With the table inheritance model, how are we
ensuring that, if tables R' and R'' both inherit from R, when a tuple
is in R' relating to another tuple in R (or is that the same tuple),
there's not also such a relation between a tuple in R'' and R?

In OOR _model_ we define a constraint.

In postgreSQL we first fix the constraints spanning inheritance trees
problem and then define a constraint ;)

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

On 12 Aug 2002, Hannu Krosing wrote:

Are you saying that inheritance in SQL is something fundamentally
different than inheritance in OO languages ?

Yes.

(For example, the distinction
between types and instances of types is critical in OO theory. What are
the TI equivalants of this?)

If by TI you mean type instance....

Sorry, I shouldn't have abbreviated this. By "TI" I meant "table
inheritance."

then the equivalent of of an instance is
a relation (i.e. one row in an (inherited) table).

As I understand it, one row in a table, inherited or not, is a
tuple, not a relation. The definitions I'm familar with are Date's:
a relation is a header, describing the types of attributes within
the tuple, and a set of tuples conforming to that header, and a
relvar is a variable that holds such a relation. (His definitions
seem to be the ones in common use--Korth/Silberschatz agree with
him, though they don't use the relvar concept AFIK.)

So is an instance a relation (a set of tuples) or a tuple?

If the former, consider the following argument.

In an object oriented program I can have a class C, and a subclass C'
that inherits from C. Now, in any situation that calls for an instance
of C, I can instead use an instance of C'. This is polymorphism.

Now, if an instance is equivalant to tuple, and a relation inherits from
another relation, I'd guess that a relation is equivalant to a class.
But given relation R' inheriting from relation R, does that mean that I
can use a tuple from R' anywhere I could use a tuple from R? No, obviously
not, as the two tuples have a different number of attributes, to start with.
So this analogy is now breaking down.

I suppose I could try to work out here if you really mean that
(using the strict Date sense of the terms here) the relvars are
classes, and the relations that they hold are instances. But that
seems to get a bit sticky too. I think it's better if I wait at
this point for you to provide some further clarification. Would
you mind doing so? Specifically, what is the equivalant of a class,
and what is the equivalant of an instance? What are the consequences
of this, if you know them?

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

Can you please give me two or three examples of problems that are
not solved by simple relational solutions, and how table inheritance
solves them?

Of maybe people are diversifying, using inheritance for is-a
relationships and relational model for has-a relationships.

Well, it seems to me that the relational model better supports the is-a
relationship. With the relational model, I can specify a column in a
table that specifies what that particular entity is, and that can be set
to one and only one value. With the table inheritance model, how are we
ensuring that, if tables R' and R'' both inherit from R, when a tuple
is in R' relating to another tuple in R (or is that the same tuple),
there's not also such a relation between a tuple in R'' and R?

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Mon, 2002-08-12 at 00:29, Hannu Krosing wrote:

On Mon, 2002-08-12 at 11:52, Curt Sampson wrote:

On Sun, 11 Aug 2002, Don Baccus wrote:

[snip]

But anyway, I have no particularly huge objection to syntatic sugar
alone. I do have objections to it when it's not saving much typing. (It
is in this case, but that could be fixed with better automatic support
of view updates.)

But my real objection is when it makes things more confusing, rather
than less, which I think is definitely happening here.

What makes things more confusing is poor understanding of a feature, not
the feature itself.

Agreed. Just because a feature may not be well understood by the masses
doesn't mean the feature is worthless.

I've never
seen a rigourous explanation of our model of table inheritance,
nor any model that was more obviously correct than another. And
the parallel drawn with inheritance in OO languages is a false
parallel that adds to the confusion.

Are you saying that inheritance in SQL is something fundamentally
different than inheritance in OO languages ?

Hmmm...there might be. Curt raises in interesting point below. Do keep
in mind that I believe he's specifically referring to table inheritance
and not the broad scope of "language wide" inheritance.

(For example, the distinction
between types and instances of types is critical in OO theory. What are
the TI equivalants of this?)

If by TI you mean type instance then the equivalent of of an instance is
a relation (i.e. one row in an (inherited) table).

Look a little deeper here. In other OO implementations, I can define a
class (say class a) which has no instances (abstract base class).
Furthermore, I can take this case and use it for building blocks
(assuming multiple inheritance is allowed in this world) by combining
with other classes (z inherits from a, b, c; whereby classes a, b, c
still do not have an actual instance). I can create an instance of my
newly inherited class (z).

Seems to me that there is some distinction between types (classes) and
and type instances (instance of a specific class) as it pertains to it's
usability.

How exactly would you create an abstract base class for table type?

I'm still trying to put my brain around exactly what the implications
are here, but I *think* this is what curt was trying to stress. Curt,
feel free to correct me as needed.

All this is borne out by the regular questions one sees about
inheritance in the mailing lists. I'll admit a good part of it is
due to the broken implementation of inheritance, but all of the
problems I've ever seen are easily solved with very simple relational
solutions.

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

I agree with Hannu here. Curt's comment seems like lip service. Worth
noting too, even if it were not for the issues pointed out by Hannu
here, Curt's statement certainly does nothing to invalidate the concept
of table inheritance. After all, most camps are happy when there are
multiple means to an end. Just because it can be done via method-x,
doesn't invalid method-y. The inverse is probably true too. ;)

Maybe the inheritance thing is causing people to turn off the relational
parts of their brain or something.

Of maybe people are diversifying, using inheritance for is-a
relationships and relational model for has-a relationships.

That's an interesting point.

Greg

On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:
...

Look a little deeper here. In other OO implementations, I can define a
class (say class a) which has no instances (abstract base class).
Furthermore, I can take this case and use it for building blocks
(assuming multiple inheritance is allowed in this world) by combining
with other classes (z inherits from a, b, c; whereby classes a, b, c
still do not have an actual instance). I can create an instance of my
newly inherited class (z).

Seems to me that there is some distinction between types (classes) and
and type instances (instance of a specific class) as it pertains to it's
usability.

How exactly would you create an abstract base class for table type?

CREATE TABLE abstract_base (
cols ...,
CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
)

This assumes that the constraint is not inherited or can be removed in
child tables.

--
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
========================================
"And he spake a parable unto them to this end, that men
ought always to pray, and not to faint."
Luke 18:1

On Mon, 2002-08-12 at 10:39, Oliver Elphick wrote:

On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:

How exactly would you create an abstract base class for table type?

CREATE TABLE abstract_base (
cols ...,
CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
)

This assumes that the constraint is not inherited or can be removed in
child tables.

Why would I assume that constraints would not be inherited? Seems as a
general rule of thumb, you'd want the constraints to be inherited. Am I
missing something?

Also, if I remove the constraint on the child table, doesn't that really
mean I'm removing the constraint on the parent table? That would seem
to violate the whole reason of having constraints. If a constraint is
placed in an ABC and we find that we later need to remove it for EVERY
derived class, doesn't that imply it shouldn't of been in there to begin
with? After all, in this case, we're saying that each and every derived
class needs to overload or drop a specific constraint. That strikes me
as being rather obtuse.

That, in it self, I find rather interesting. Is there any papers or
books which offers explanation of how constraints should handled for
table inheritance?

Greg

On Mon, 2002-08-12 at 17:30, Greg Copeland wrote:

On Mon, 2002-08-12 at 10:39, Oliver Elphick wrote:

On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:

How exactly would you create an abstract base class for table type?

CREATE TABLE abstract_base (
cols ...,
CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
)

This assumes that the constraint is not inherited or can be removed in
child tables.

Why would I assume that constraints would not be inherited? Seems as a
general rule of thumb, you'd want the constraints to be inherited. Am I
missing something?

You are right, but I was stuck trying to think of a constraint that
would restrict the abstract base but not its descendants. Instead of
CHECK (1 = 0), I think we can use a function that checks whether the
current table is the abstract base and returns false if it is. That
would be validly heritable. (CHECK (tableoid != 12345678))

Also, if I remove the constraint on the child table, doesn't that really
mean I'm removing the constraint on the parent table? That would seem
to violate the whole reason of having constraints. If a constraint is
placed in an ABC and we find that we later need to remove it for EVERY
derived class, doesn't that imply it shouldn't of been in there to begin
with? After all, in this case, we're saying that each and every derived
class needs to overload or drop a specific constraint. That strikes me
as being rather obtuse.

Yes, it would be clumsy, and I think you are correct that constraints
should not be removable.

The inheritance model I am familiar with is that of Eiffel, where
constraints are additive down the hierarchy. That is, an invariant on
the base class applies in its descendants along with any invariants
added by the descendant or intermediate classes. That language has the
concept of a deferred class, which is the parallel of the abstract base
table we are discussing. A deferred class cannot be directly
instantiated. To do the same in the table hierarchy would require a
keyword to designate a table as an abstract table (CREATE ABSTRACT TABLE
xxx ...?). In the absence of that, a constraint based on the table
identity will have to do.

--
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
========================================
"And he spake a parable unto them to this end, that men
ought always to pray, and not to faint."
Luke 18:1

Ok, big bundled up reply here to various people.

From: Greg Copeland <greg@CopelandConsulting.Net>

What makes things more confusing is poor understanding of a feature, not
the feature itself.

Agreed. Just because a feature may not be well understood by the masses
doesn't mean the feature is worthless.

Yeah, but if it's not understood by fairly smart people familiar
with both relational theory and OO programming? If the feature is
confusing because it appears to be something it's not, that's a
feature problem, not a problem with the people trying to understand
it. Maybe all that's necessary to fix it is a terminology change,
but even so....

Hmmm...there might be. Curt raises in interesting point below. Do keep
in mind that I believe he's specifically referring to table inheritance
and not the broad scope of "language wide" inheritance.

Yes.

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

I agree with Hannu here. Curt's comment seems like lip service.

Well, as I said: examples please. Quite frankly, between the lack
of a clear model of table inheritance (Hannu seems to have one,
but this needs to be written up in unambiguous form and put into
the postgres manual) and the bugs in the postgres implementation
of table inheritance, I've found the relational model much easier
to use for solving problems.

From: Oliver Elphick <olly@lfix.co.uk>

On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:

How exactly would you create an abstract base class for table type?

CREATE TABLE abstract_base (
cols ...,
CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
)

This assumes that the constraint is not inherited or can be removed in
child tables.

Are we then assuming that tuples in the child tables do not appear
in the base table? That's more or less what I'd assumed when I
originally heard about table inheritance (after all, instantiating
a child object does not automatically instantiate a separate copy
of the parent object), but the SQL standard, postgres, and I believe other
systems make the exact opposite assumption.

If the child table tuples do appear in the parent, you've now got
a situation analogous to the current postgres situation where a
constraint on the parent table is an outright lie. (I'm thinking
of the UNIQUE constraint which guarantees that all values in a
column will be unique--and then they aren't.) I consider breaking
the relational model this badly a completely unacceptable cost no
matter what additional functionality you're wanting to add, and I
expect that most other people do, too.

From: Greg Copeland <greg@CopelandConsulting.Net>

That, in it self, I find rather interesting. Is there any papers or
books which offers explanation of how constraints should handled for
table inheritance?

Here again, I'd love to hear about some references, too. I see a
lot of people saying they like table inheritance; I don't see anyone
(except maybe Hannu) who seems to have a clear idea of how it should
work.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Curt Sampson wrote:

... the bugs in the postgres implementation
of table inheritance, I've found the relational model much easier
to use for solving problems.

No one has argued that the shortcomings (not bugs, really, just things
left out) makes the current implementation of very limited utility. As
I mention this is exactly why we choose not to use it at OpenACS.

On the other hand at least we took the time to understand how it
actually does work before criticizing it.

It's a pity, as I pointed out the reduction in joins alone would really
be great.

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

On Mon, 2002-08-12 at 20:34, Curt Sampson wrote:

Ok, big bundled up reply here to various people.

From: Greg Copeland <greg@CopelandConsulting.Net>

What makes things more confusing is poor understanding of a feature, not
the feature itself.

Agreed. Just because a feature may not be well understood by the masses
doesn't mean the feature is worthless.

Yeah, but if it's not understood by fairly smart people familiar
with both relational theory and OO programming? If the feature is
confusing because it appears to be something it's not, that's a
feature problem, not a problem with the people trying to understand
it. Maybe all that's necessary to fix it is a terminology change,
but even so....

You're constantly confusing Postgres' implementation with a "desired"
implementation. Below, I think, is the effort to figure out exactly
what a "desired implementation" really is.

If a feature is partially implemented, of course it's going to be
confusing to use.

Let's please stop beating this horse Curt. At this point, I think the
horse is floating upside down in a pond somewhere...yep...and the
buzzards are coming.

Please. Beating people with a stick isn't suddenly going to make
everyone share your view point.

All _simple_ inheritance problems are easily solved by simple relational
solutions. The general problem of much more typing and debugging, less
clues for optimiser etc. are not solved by _simple_ relational
solutions.

I agree with Hannu here. Curt's comment seems like lip service.

Well, as I said: examples please. Quite frankly, between the lack
of a clear model of table inheritance (Hannu seems to have one,
but this needs to be written up in unambiguous form and put into
the postgres manual) and the bugs in the postgres implementation
of table inheritance, I've found the relational model much easier
to use for solving problems.

If you're so keen on examples, please provide one that justifies such a
boastful statement. Hannu has done a pretty fair job of beating ya back
every time. Personally, in this case, I don't really need examples are
it's pretty obvious a braggart statement full of bias. So second
thought, perhaps we can let this one alone.

I do agree that it looks like Hannu is doing a fairly good job of
providing some constructive direction here. Hannu, please keep up the
good work. ;)

From: Oliver Elphick <olly@lfix.co.uk>

On Mon, 2002-08-12 at 15:00, Greg Copeland wrote:

How exactly would you create an abstract base class for table type?

CREATE TABLE abstract_base (
cols ...,
CONSTRAINT "No data allowed in table abstract_base!" CHECK (1 = 0)
)

This assumes that the constraint is not inherited or can be removed in
child tables.

Are we then assuming that tuples in the child tables do not appear
in the base table? That's more or less what I'd assumed when I
originally heard about table inheritance (after all, instantiating
a child object does not automatically instantiate a separate copy
of the parent object), but the SQL standard, postgres, and I believe other
systems make the exact opposite assumption.

That's actually my exact assumption...that is, that tuples in the parent
did not exist in the child. Is that not true? Can you point me to any
references?

If the child table tuples do appear in the parent, you've now got
a situation analogous to the current postgres situation where a
constraint on the parent table is an outright lie. (I'm thinking
of the UNIQUE constraint which guarantees that all values in a

[snip]

I knew that there are *implementation* issues with postgres that causes
problems with constraints, etc...I didn't realize that was the reason.

From: Greg Copeland <greg@CopelandConsulting.Net>

That, in it self, I find rather interesting. Is there any papers or
books which offers explanation of how constraints should handled for
table inheritance?

Here again, I'd love to hear about some references, too. I see a
lot of people saying they like table inheritance; I don't see anyone
(except maybe Hannu) who seems to have a clear idea of how it should
work.

Well, you seem to be making references to "...SQL standard, postgres,
and I believe other systems...". I was counting on you or someone else
to point us to existing references. I'm fairly sure we can manage to
wade through it to walk a sane and fruitful path...it would just be a
less bumpier road if we all spoke the same OO'ish dialect and shared a
common knowledge base that we can all agree on for starters. So, you
got anything to share here??? ;)

Greg

On Mon, 12 Aug 2002, Don Baccus wrote:

It's a pity, as I pointed out the reduction in joins alone would really
be great.

So implement the same thing relationally, and get your reduction
in joins. There are tricks, discussed on this very list in the
last few days, that would let you do what you need.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Tue, 2002-08-13 at 10:16, Curt Sampson wrote:

On 12 Aug 2002, Greg Copeland wrote:

...

Are we then assuming that tuples in the child tables do not appear

in the base table? That's more or less what I'd assumed when I
originally heard about table inheritance (after all, instantiating
a child object does not automatically instantiate a separate copy
of the parent object),

Tuples in the child table "appear" in parent table when you do a plain
SELECT, as thei IMHO should, because you _do_ want to get all kinds of
animals when doing select from animals.

They do not appear in parent table when you do

SELECT .. FROM ONLY parent_table

It used to be the opposite (one needed to do "SELECT .. FROM
parent_table* " to get tuples from inherited tables as well ) but it
was changed because SQL99 mandated that inherited tables should be
included by default. That's for SQL99's "CREATE TABLE (...) UNDER
another_table" kind of single inheritance.

but the SQL standard, postgres, and I believe other
systems make the exact opposite assumption.

That's actually my exact assumption...that is, that tuples in the parent
did not exist in the child.

Sorry, by "opposite assumption," I meant these two opposites:

There are two main orthogonal ways of mapping inheritance to relational
model.

1. Tuples in child tables appear in the parent table.

That's the way you implemented the samples in the beginning of this
thread, i.e. keep the common part in one table and extend by stitching
columns fron child tables to the "side" using foreign keys.

This makes it easy to enforce primary keys and uniqueness, but grows
ugly quite fast if you have deep inhweritance hierarchies - if you have
inheritance 5 levels deep, you need 4 joins to get a tuple from the
last-descendant table.

It also makes automatic updating ov views a pain to do.

2. Tuples in child tables do not appear in the parent table.

This is how postgres implements it - make a new table for each inherited
table and do UNION join when doing a SELECT .

This makes it hard to implement uniqueness and primary keys, but easy to
do updates and inserts.

Take your pick, keeping in mind that the sources I know of (Appendix E of _The
Third Manifesto_, _Database Systems Concepts_ (ISTR), the SQL standard and
postgres currently all assume #1.

I would like yet another implementation, more in line with SQL99's
single inheritance, where all inherited tables would be stored in the
same pagefile (so that you can put a unique index on them and it would
"just work" because TIDs all point into the same file). Fast access to
some single table ONLY could be done using partial indexes on tableoid.

This can't be mapped directly on SQL92 kind of relational model, but can
more or less be mimicked by setting the new fields to NULL for tuples
belonging to parent relation.

If we find the one we pick is unworkable, we can always go back
and try the other.

If the child table tuples do appear in the parent, you've now got
a situation analogous to the current postgres situation where a
constraint on the parent table is an outright lie. (I'm thinking
of the UNIQUE constraint which guarantees that all values in a

[snip]

I knew that there are *implementation* issues with postgres that causes
problems with constraints, etc...I didn't realize that was the reason.

Well, assuming we are mapping inheritance back into relational stuff
behind the scenes (which it appears to me we are doing now), we can just
map back to the relation method I demonstrated earlier of doing what
someone wanted to do with table inheritance (child tables contain only
foreign key and child-specific data; parent table contains primary key
and all parent data) and that will fix the implementation problem.

The main problems I pointed out above:

1. hard-to-implement UPDATE rules, theoretically possible is not good
enough for real use ;)

2. too much joining for deep inheritance hierarchies .

Or people have proposed other things, such as cross-table constraints,
to try to do this.

Well, you seem to be making references to "...SQL standard, postgres,
and I believe other systems...". I was counting on you or someone else
to point us to existing references.

Well, counting on me is not good, since the whole reason I started this
was because I found the issue confusing in part due to the lack of any
obvious standards here that I could find. :-) But here's what I do have:

Date, Darwen, _Foundation for Future Database Systems, The
Third Manefesto (Second Edition)_. Appendex E.

Silberschatz, Korth, Sudarshan, _Database Systems Concepts
(Fourth Edition)_. I think it's around chapter 9. (My copy is
at home right now.)

SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?

I got mine from

http://www.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/fcd2found.pdf

Quite hard to read, as standard in general tend to be ;)

I also have PDF's with a large [FINAL] stamp on them, which I cant
remember where I got (but I posted the link here a few months ago)

Postgres. Known broken implementation, but we can at least poke
stuff into it and see what it does.

In addition, OO programming gets mentioned ocassionally. I don't
think that table inheritance is anything related

IMHO table inheritance is a natural relational extension to type
inheritance - if you create a subtype that is-a parent type (bird is an
animal), you also want to be able to treat it as such in queries - i.e.
be able select all animals, and not have to manually make the connection
between OO (type inheritance) and Relational
(INSERT/UPDATE/SELECT/DELETE) worlds.

(and I've spent
a lot of time in the last couple of years developing methods to
make my OO programs and relational databases play nice with each
other),

So have the database guys, adding OO stuff to databases and all ;)

but it might help to have some idea of what people to do
connect the two, in case some people think that they are or should
be connected. You can start by checking out this page for a few
ways of creating objects from database information:

http://www.martinfowler.com/isa/inheritanceMappers.html

I'll try to check it out .

On 12 Aug 2002, Greg Copeland wrote:

You're constantly confusing Postgres' implementation with a "desired"
implementation.

No. I'm still trying to figure out what the desired implementation
actually is. This is documented nowhere.

If you're so keen on examples, please provide one that justifies such a
boastful statement.

You appear to be saying I should provide an example that proves there
exists no table inheritance configuration that cannot easily be done
with a relational implementation. That's not possible to do, sorry.

I will revise my opinion the instant someone shows me something that I
can't do relationally, or is easy to implement with inheritance, and
difficult with relational methods. Now you know what you need to do, and
if you have no example, we can drop the whole thing. But I am honestly
interested to see just what it is that makes table inheritance so great.

Are we then assuming that tuples in the child tables do not appear
in the base table? That's more or less what I'd assumed when I
originally heard about table inheritance (after all, instantiating
a child object does not automatically instantiate a separate copy
of the parent object), but the SQL standard, postgres, and I believe other
systems make the exact opposite assumption.

That's actually my exact assumption...that is, that tuples in the parent
did not exist in the child.

Sorry, by "opposite assumption," I meant these two opposites:

1. Tuples in child tables appear in the parent table.

2. Tuples in child tables do not appear in the parent table.

Take your pick, keeping in mind that the sources I know of (Appendix E of _The
Third Manifesto_, _Database Systems Concepts_ (ISTR), the SQL standard and
postgres currently all assume #1.

If we find the one we pick is unworkable, we can always go back
and try the other.

If the child table tuples do appear in the parent, you've now got
a situation analogous to the current postgres situation where a
constraint on the parent table is an outright lie. (I'm thinking
of the UNIQUE constraint which guarantees that all values in a

[snip]

I knew that there are *implementation* issues with postgres that causes
problems with constraints, etc...I didn't realize that was the reason.

Well, assuming we are mapping inheritance back into relational stuff
behind the scenes (which it appears to me we are doing now), we can just
map back to the relation method I demonstrated earlier of doing what
someone wanted to do with table inheritance (child tables contain only
foreign key and child-specific data; parent table contains primary key
and all parent data) and that will fix the implementation problem.

Or people have proposed other things, such as cross-table constraints,
to try to do this.

Well, you seem to be making references to "...SQL standard, postgres,
and I believe other systems...". I was counting on you or someone else
to point us to existing references.

Well, counting on me is not good, since the whole reason I started this
was because I found the issue confusing in part due to the lack of any
obvious standards here that I could find. :-) But here's what I do have:

Date, Darwen, _Foundation for Future Database Systems, The
Third Manefesto (Second Edition)_. Appendex E.

Silberschatz, Korth, Sudarshan, _Database Systems Concepts
(Fourth Edition)_. I think it's around chapter 9. (My copy is
at home right now.)

SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?

Postgres. Known broken implementation, but we can at least poke
stuff into it and see what it does.

In addition, OO programming gets mentioned ocassionally. I don't
think that table inheritance is anything related (and I've spent
a lot of time in the last couple of years developing methods to
make my OO programs and relational databases play nice with each
other), but it might help to have some idea of what people to do
connect the two, in case some people think that they are or should
be connected. You can start by checking out this page for a few
ways of creating objects from database information:

http://www.martinfowler.com/isa/inheritanceMappers.html

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Mon, 12 Aug 2002, Don Baccus wrote:

Give it up. You're acting like a turkey. If you aren't, skin yourself
a new non-turkey skin.

Since he appears not to be able to avoid abusive ad hominem attacks,
I'm now sending mail with "dhogaza@pacifier.com" in the From: header
to /dev/null. If there's a technical point in one of his messages that
relates to the discussion that I need to answer, someone should please
mention it on the list or forward it to me.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Tue, 2002-08-13 at 00:33, Curt Sampson wrote:

On Mon, 12 Aug 2002, Don Baccus wrote:

Give it up. You're acting like a turkey. If you aren't, skin yourself
a new non-turkey skin.

Since he appears not to be able to avoid abusive ad hominem attacks,
I'm now sending mail with "dhogaza@pacifier.com" in the From: header
to /dev/null. If there's a technical point in one of his messages that
relates to the discussion that I need to answer, someone should please
mention it on the list or forward it to me.

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in
it. We're all actively attempting to determine ways to make it less
broken, if not altogether better. The fact that it's broken and you
hardly go an email reminding everyone of this fact is certainly not
making friends. In fact, one should hardly be surprised you're not
seeing more retorts as such.

For the sake of the project, I'd hope you could give the "broken" topic
a rest, move on, and allow a little time for the list to settle again.
If such abuse continues, then IMHO, it would make sense to /dev/null
him.

Greg

On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:

I will revise my opinion the instant someone shows me something that I
can't do relationally, or is easy to implement with inheritance, and
difficult with relational methods. Now you know what you need to do, and
if you have no example, we can drop the whole thing. But I am honestly
interested to see just what it is that makes table inheritance so great.

I think here-in is the first problem. You seem to insist that the world
can only allow for one or the other and that the two approaches are
mutually exclusive. I tends to think that there is room for both. One
would also seem to allow that they can actually be complimentary
(referring to Hannu's recent is-a & has-a inheritance comments).

Can we let go of x is better than y and just concentrate on how y can be
made better without regard for x?

After it's all said and done, who knows, everyone might agree that table
inheritance is just a plain, bad idea.

I knew that there are *implementation* issues with postgres that causes
problems with constraints, etc...I didn't realize that was the reason.

Well, assuming we are mapping inheritance back into relational stuff
behind the scenes (which it appears to me we are doing now), we can just
map back to the relation method I demonstrated earlier of doing what
someone wanted to do with table inheritance (child tables contain only
foreign key and child-specific data; parent table contains primary key
and all parent data) and that will fix the implementation problem.

This is what I imagined the preferred solution would be, however, I'm
also assuming it would be the more complex to implement *properly*.

Or people have proposed other things, such as cross-table constraints,
to try to do this.

Ya, I was kicking this idea around in my head tonight. Didn't get far
on it. So I should look for postings in the archive about this specific
implementation?

Well, you seem to be making references to "...SQL standard, postgres,
and I believe other systems...". I was counting on you or someone else
to point us to existing references.

Well, counting on me is not good, since the whole reason I started this
was because I found the issue confusing in part due to the lack of any
obvious standards here that I could find. :-) But here's what I do have:

Date, Darwen, _Foundation for Future Database Systems, The
Third Manefesto (Second Edition)_. Appendex E.

Is this a book or a paper. I have a paper that I've been reading
(ack...very, very dry) by these guys of the same name.

Silberschatz, Korth, Sudarshan, _Database Systems Concepts
(Fourth Edition)_. I think it's around chapter 9. (My copy is
at home right now.)

SQL Standard. I don't have it handy. Anyone? Anyone? Bueller?

So the SQL standard does address table inheritance? Not that this means
I feel that they've done the right thing...but what did the
specification have to say on the subject? Any online references?

Postgres. Known broken implementation, but we can at least poke
stuff into it and see what it does.

In addition, OO programming gets mentioned ocassionally. I don't
think that table inheritance is anything related (and I've spent

Yes. I think I'm starting to buy into that too, however, I'm not sure
that it has to mean that no value is within. In other words, I'm still
on the fence on a) table inheritance really makes much "OO" sense and b)
even if it does or does not, is there value in any form of it's
implementation (whatever the end result looks like) .

a lot of time in the last couple of years developing methods to
make my OO programs and relational databases play nice with each
other), but it might help to have some idea of what people to do
connect the two, in case some people think that they are or should
be connected. You can start by checking out this page for a few
ways of creating objects from database information:

http://www.martinfowler.com/isa/inheritanceMappers.html

Thanks. Funny, I was reading that just the other day. ;)

Greg

On 13 Aug 2002, Greg Copeland wrote:

On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:

I will revise my opinion the instant someone shows me something that I
can't do relationally, or is easy to implement with inheritance, and
difficult with relational methods. Now you know what you need to do, and
if you have no example, we can drop the whole thing. But I am honestly
interested to see just what it is that makes table inheritance so great.

I think here-in is the first problem. You seem to insist that the world
can only allow for one or the other and that the two approaches are
mutually exclusive.

No, I don't.

1. If it changes the rules, as it were, that is breaks other
parts of the system, it should go. This is the current state
of the postgres implementation. I'm guessing it's not the state
of the desired implementation, once we figure out what that is.

2. If it's just syntactic sugar, that's livable, so long as
it's quite obvious what it's syntatic sugar for. (In the current
case, it's not.) It's even good if it saves a lot of effort.

3. If it actually allows you to do something you cannot otherwise
do, or allows you to do something very difficult with much
greater ease, it's a good thing and it should stay.

Well, assuming we are mapping inheritance back into relational stuff
behind the scenes (which it appears to me we are doing now), we can just
map back to the relation method I demonstrated earlier of doing what
someone wanted to do with table inheritance (child tables contain only
foreign key and child-specific data; parent table contains primary key
and all parent data) and that will fix the implementation problem.

This is what I imagined the preferred solution would be, however, I'm
also assuming it would be the more complex to implement *properly*.

I don't think so. Both systems are currently, AFICT, pretty simple
mappings onto the relational system. Once we get the exact details of
table inheritance behaviour hammered out, I will gladly provide the
mapping it's possible to create it.

Date, Darwen, _Foundation for Future Database Systems, The
Third Manefesto (Second Edition)_. Appendex E.

Is this a book or a paper. I have a paper that I've been reading
(ack...very, very dry) by these guys of the same name.

It's a book. Apparently the paper is, in comparison, much more lively.
:-) But I find the book good in that, at the very least, it shows the
level to which you have to go to come up with a theoretically solid
basis for something you want to implement.

So the SQL standard does address table inheritance?

Yes.

Not that this means I feel that they've done the right thing...but
what did the specification have to say on the subject? Any online
references?

I don't have a copy of the spec handy, and have not had time to go and
dig one up. All I got from it was out of the two book references I gave.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Greg Copeland wrote:

On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:

I will revise my opinion the instant someone shows me something that I
can't do relationally, or is easy to implement with inheritance, and
difficult with relational methods.

The traditional view approach requires unnecessary joins, and there's no
getting around it.

And yes I know he's not reading my mail and no, don't bother repeating
this to him, he'll just continue to ignore the point.

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

On Tuesday 13 August 2002 01:40 am, Greg Copeland wrote:

On Tue, 2002-08-13 at 00:33, Curt Sampson wrote:

On Mon, 12 Aug 2002, Don Baccus wrote:

Give it up. You're acting like a turkey. If you aren't, skin yourself
a new non-turkey skin.

Since he appears not to be able to avoid abusive ad hominem attacks,
I'm now sending mail with "dhogaza@pacifier.com" in the From: header
to /dev/null. If there's a technical point in one of his messages that
relates to the discussion that I need to answer, someone should please
mention it on the list or forward it to me.

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in

Knowing Don to some extent, I can say with some assurance that his 'attacks'
are never unprovoked.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

On Tue, 2002-08-13 at 18:48, Don Baccus wrote:

Greg Copeland wrote:

On Tue, 2002-08-13 at 00:16, Curt Sampson wrote:

...

And yes I know he's not reading my mail and no, don't bother repeating
this to him, he'll just continue to ignore the point.

I suspect that he will still read your (partial) comments in replies to
your mails and has to look the originals up in archives in case he gets
interested in what the other guys respond to ;)

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

On Tue, 13 Aug 2002, Lamar Owen wrote:

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in

Knowing Don to some extent, I can say with some assurance that his 'attacks'
are never unprovoked.

Sorry; I'm not aware of the circumstances under which one is supposed
to call someone a "dick-waver" and other such things on a technical
mailing list. Perhaps you can explain to me when one should be
doing this, so I too can do it at the appropriate times.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

It is hard to argue with this logic.

---------------------------------------------------------------------------

Curt Sampson wrote:

On Tue, 13 Aug 2002, Lamar Owen wrote:

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in

Knowing Don to some extent, I can say with some assurance that his 'attacks'
are never unprovoked.

Sorry; I'm not aware of the circumstances under which one is supposed
to call someone a "dick-waver" and other such things on a technical
mailing list. Perhaps you can explain to me when one should be
doing this, so I too can do it at the appropriate times.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  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, Pennsylvania 19073

Bruce Momjian wrote:

It is hard to argue with this logic.

If he were actually making a technical argument I might actually agree
with you myself.

Thus far all he's done is argue from authority, and in tight circles to
boot.

Which means the term is an accurate description of his behavior ...

Here's a lengthier and polite description - he's trying to impress us
with his brilliance which several of us are just too dense to recognize
on our own.

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

Yea, you have to question what value the discussion has, really. We
have users of inheritance that like it. If we can get a TODO item out
of the disucssion, great, but there doesn't seem to be any direction of
where the discussion is heading.

---------------------------------------------------------------------------

Don Baccus wrote:

Bruce Momjian wrote:

It is hard to argue with this logic.

If he were actually making a technical argument I might actually agree
with you myself.

Thus far all he's done is argue from authority, and in tight circles to
boot.

Which means the term is an accurate description of his behavior ...

Here's a lengthier and polite description - he's trying to impress us
with his brilliance which several of us are just too dense to recognize
on our own.

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

-- 
  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, Pennsylvania 19073

On Wed, 2002-08-14 at 05:07, Curt Sampson wrote:

On Tue, 13 Aug 2002, Lamar Owen wrote:

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in

Knowing Don to some extent, I can say with some assurance that his 'attacks'
are never unprovoked.

Sorry; I'm not aware of the circumstances under which one is supposed
to call someone a "dick-waver" and other such things on a technical
mailing list.

It was quite clear what he meant but perhaps there is a better technical
term for use in a technical list, some 5-7 letter all-capital acronym
perhaps ;)

But as anyone should give the benefit of doubt, I've been assuming that
you are just playing devil's advocate .

Perhaps you can explain to me when one should be
doing this, so I too can do it at the appropriate times.

I guess what he meant was that you were arguing for arguments sake (mine
is better than yours! Yes it is! Yes it is! ...) and not to get to some
solution, dismissing perfectly good arguments with a simple"not true"
statements and suggesting people to read heavy books with the claim that
the truth is somewhere in there ;)

So it seems that the "technical" content of his claim was quite similar
to some of yours ;)

This has been quite bizarre thread, with about half of traffic being
quite reasonable constructive discussion while the other half seems
definitely describable by the the word that would get me in your
killfile ;)

I'll be off to my vacation for two weeks now, and I'll try to come up
with consistent writeup of what our OO features should be (both
inheritance and others).

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

On Wed, 2002-08-14 at 09:49, Curt Sampson wrote:

On Wed, 14 Aug 2002, Bruce Momjian wrote:

OK, great summary. Isn't the bottom-line issue the limitation of not
being able to create an index that spans tables?

That would be one way to fix one particular problem. I can think of
another way to fix it right off-hand. (Put the parent's part of the data
in the parent table, the child's part in the child table and join.) But
we haven't completely worked out what effect this has on other parts of
the system, or what effect we're even looking for.

It would be cleaner in some parts while making things messier in others.

This would make INSERTs and UPDATEs much more complicated, and also
there would be a lot of joins for deeper inheritance hierarchies.

An an example, at this point some people (including me) feel that
constraints (*all* constraints) placed on a supertable should always
work. This means that one should not be able to insert into a subtable
anything that would break a supertable constraint, and one should not be
able to add a constraint to a supertable that's violated by a subtable.

Agreed. Most of this would be easy to implement for curent
implementation (but perhaps no more efficient than when done by manually
added rules/triggers) if constraints could contain subqueries.

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

On Tue, 13 Aug 2002, Bruce Momjian wrote:

Yea, you have to question what value the discussion has, really. We
have users of inheritance that like it. If we can get a TODO item out
of the disucssion, great, but there doesn't seem to be any direction of
where the discussion is heading.

Summary:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

3. It's hard to fix the current implementation without such a
description.

4. Thus, we are in other messages here trying to work out the
model and come up with such a description.

5. The people working this out at the moment appear to be me,
Greg Copeland and Hannu Krosing.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

Surely 99% of the implementation problems could be solved with an index type
that can span tables?

Chris

Curt Sampson wrote:

On Tue, 13 Aug 2002, Bruce Momjian wrote:

Yea, you have to question what value the discussion has, really. We
have users of inheritance that like it. If we can get a TODO item out
of the disucssion, great, but there doesn't seem to be any direction of
where the discussion is heading.

Summary:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

3. It's hard to fix the current implementation without such a
description.

4. Thus, we are in other messages here trying to work out the
model and come up with such a description.

5. The people working this out at the moment appear to be me,
Greg Copeland and Hannu Krosing.

OK, great summary. Isn't the bottom-line issue the limitation of not
being able to create an index that spans tables? Is there any way to
implement that? We have sequences that can span tables. Can that help
us?

-- 
  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, Pennsylvania 19073

On Wed, 14 Aug 2002, Christopher Kings-Lynne wrote:

Surely 99% of the implementation problems could be solved with an
index type that can span tables?

Maybe.

But my problem is not so much that it's broken, as nobody can
explain exactly what "fixed" would be. I mean, completely fixed,
not just one obvious problem fixed.

Just my opinion of course, but I think it would be best to have a
detailed description of how everything in inheritance is supposed to
work, write a set of tests from that, and then fix the implementation to
conform to the tests.

And I think a detailed description comes most easily when you have
a logical model to work from.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Christopher Kings-Lynne wrote:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

Surely 99% of the implementation problems could be solved with an index type
that can span tables?

Right. Instead of talking in circles, let's figure out how to do it.
If the issue is only sequence numbers, can we force a column to _only_
get values from the sequence counter, thereby makeing the index span
unnecessary? Can't we look up stuff in parent/child index to check for
collisions before we add a row? Doesn't seem too hard to me.

-- 
  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, Pennsylvania 19073

Right. Instead of talking in circles, let's figure out how to do it.
If the issue is only sequence numbers, can we force a column to _only_
get values from the sequence counter, thereby makeing the index span
unnecessary? Can't we look up stuff in parent/child index to check for
collisions before we add a row? Doesn't seem too hard to me.

Is it theoretically possible to add support to btree for storing table along
with the indexed value? This would obviously add overhead, so it would only
be done for spanning indexes. The index would also take up more space on
disk I guess.

When a new inherited table is created, all parent indices would be dropped
and recreated as spanning indices and vice versa.

Chris

On Wed, 14 Aug 2002, Bruce Momjian wrote:

OK, great summary. Isn't the bottom-line issue the limitation of not
being able to create an index that spans tables?

That would be one way to fix one particular problem. I can think of
another way to fix it right off-hand. (Put the parent's part of the data
in the parent table, the child's part in the child table and join.) But
we haven't completely worked out what effect this has on other parts of
the system, or what effect we're even looking for.

An an example, at this point some people (including me) feel that
constraints (*all* constraints) placed on a supertable should always
work. This means that one should not be able to insert into a subtable
anything that would break a supertable constraint, and one should not be
able to add a constraint to a supertable that's violated by a subtable.
If after more work on this everybody agrees that this is really the way
to go, then that will have implications on the solution we pick.

There's also the matter of digging up the SQL standards for table
inheritance and deciding how closely we want to follow that. (Though
I think that that's best left to after a fairly formal logical
analysis of what table inheritance should be.)

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Hannu Krosing wrote:

I guess what he meant was that you were arguing for arguments sake (mine
is better than yours! Yes it is! Yes it is! ...)

That's the dictionary definition of the phrase.

and not to get to some
solution,

and that's the source of the frustration. I only re-subscribed to the
list because we at OpenACS had examined PG's OO extensions quite
thoroughly before rejecting the current implementation as being not
useful for our work, and I thought our reasoning might be of interest.

dismissing perfectly good arguments with a simple"not true"
statements and suggesting people to read heavy books with the claim that
the truth is somewhere in there ;)

and that's what's I mean when I say he's been arguing from authority.

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

Bruce Momjian wrote:

Christopher Kings-Lynne wrote:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

Surely 99% of the implementation problems could be solved with an index type
that can span tables?

Right. Instead of talking in circles, let's figure out how to do it.
If the issue is only sequence numbers, can we force a column to _only_
get values from the sequence counter,

Even if primary keys were forced to be generated from a sequence (a very
artificial restriction), unique constraints are also implemented by
index. And people also join on columns other than their primary key so
will want indexes on these columns to span tables, also.

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

Summary:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

3. It's hard to fix the current implementation without such a
description.

4. Thus, we are in other messages here trying to work out the
model and come up with such a description.

5. The people working this out at the moment appear to be me,
Greg Copeland and Hannu Krosing.

cjs

I've been following the thread on and off, but maybe we should come up with
a list of specifically what is broken... I have used the oo feature in the
past and the only thing I dont care for about it is the lack of
documentation/examples/etc of how it really works and the fact that
constraints/indicies/etc are not inherited by child tables.

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Is it theoretically possible to add support to btree for storing table along
with the indexed value?

That's what we need, all right.

This would obviously add overhead, so it would only
be done for spanning indexes. The index would also take up more space on
disk I guess.
When a new inherited table is created, all parent indices would be dropped
and recreated as spanning indices and vice versa.

Seems like the hard way. Instead use a t_infomask bit in indextuples to
indicate that the index entry points to a table other than the one its
index is nominally associated with; if and only if this bit is set, the
table OID follows the indextuple header. This way, you don't have to
reindex just to create a child table, and you also don't pay any extra
space cost for index entries that in fact point at the parent.

There are a veritable ton of other issues to be resolved --- like how do
we (efficiently) find all the indexes relevant to a given child table
--- but the physical storage doesn't seem too complicated.

regards, tom lane

On Tue, 2002-08-13 at 23:42, Bruce Momjian wrote:

Curt Sampson wrote:

On Tue, 13 Aug 2002, Bruce Momjian wrote:

Yea, you have to question what value the discussion has, really. We
have users of inheritance that like it. If we can get a TODO item out
of the disucssion, great, but there doesn't seem to be any direction of
where the discussion is heading.

Summary:

1. The current implementation is broken.

2. We have no proper description of how a "fixed" implementation
should work.

3. It's hard to fix the current implementation without such a
description.

4. Thus, we are in other messages here trying to work out the
model and come up with such a description.

5. The people working this out at the moment appear to be me,
Greg Copeland and Hannu Krosing.

OK, great summary. Isn't the bottom-line issue the limitation of not
being able to create an index that spans tables? Is there any way to
implement that? We have sequences that can span tables. Can that help
us?

Actually, I'm not sure that is the bottom line. One of the reasons I
ask so many questions is because I'm trying to understand what the "is"
case is. For me, that is important before I can understand, not only
what the "to-be" picture should be, but what needs to be done to get
there.

Because of that, I tend to agree with Curt. We need to fill in 1, 2,
and 3. As for item number 4, I was hoping that other references would
at least help us understand a "defacto" implementation.

Long story short, for me, it's easy to superficially agree that we need
indexes that span tables but I still have no idea if that really
constitutes "the bottom-line".

Regards,
Greg Copeland

On Wed, 2002-08-14 at 08:59, Tom Lane wrote:

There are a veritable ton of other issues to be resolved --- like how do
we (efficiently) find all the indexes relevant to a given child table
--- but the physical storage doesn't seem too complicated.

Tom, seems we have yet another false start. Thanks for offering your
comments on the topic at hand. Since you seem to have a good grasp on
the the "is" case is, would you be willing to offer up some additional
details on what you feel the ("veritable ton of") outstanding issues
are?

Seems everyone clearly wants a cure and is itching to get there, yet I
don't fully understand the disease. I suspect that there are others in
the same boat. I feel that this is important for us all of understand.
I think we need to understand what our "to-be" picture is as well as
what points need to be addressed before we can say we've arrived.

Willing to help spell this out?

Regards,
Greg Copeland

On Tue, 2002-08-13 at 23:43, Curt Sampson wrote:

Just my opinion of course, but I think it would be best to have a
detailed description of how everything in inheritance is supposed to
work, write a set of tests from that, and then fix the implementation to
conform to the tests.

And I think a detailed description comes most easily when you have
a logical model to work from.

I completely agree. This is why I want/wanted to pursue the theory and
existing implementations angle.

Seems like everyone trying to jump on "index spanning" is premature.

Doesn't Oracle have table inheritance? Hmmm...I might have to go do
some reading to find out one way or anther... ;)

Sign,
Greg Copeland

Hannu Krosing <hannu@tm.ee> writes:

Agreed. Most of this would be easy to implement for curent
implementation (but perhaps no more efficient than when done by manually
added rules/triggers) if constraints could contain subqueries.

I don't understand what a constraint containing a subquery means.
Does it constrain the table(s) referenced by the subquery too? If not,
what's the point --- adding, dropping or altering rows in the referenced
table might make the constraint condition false. If it does constrain
the referenced tables, how the heck are you going to implement that in a
reasonable fashion?

regards, tom lane

On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote:

On Tue, 2002-08-13 at 23:43, Curt Sampson wrote:

Just my opinion of course, but I think it would be best to have a
detailed description of how everything in inheritance is supposed to
work, write a set of tests from that, and then fix the implementation to
conform to the tests.

And I think a detailed description comes most easily when you have
a logical model to work from.

I completely agree. This is why I want/wanted to pursue the theory and
existing implementations angle.

In theory, it sounds like a good idea. In practice ... ;-)

Seems like everyone trying to jump on "index spanning" is premature.

Seems like some people haven't looked at the history of the OO
implementation in PostgreSQL.

Actually, I think you'll find that once a PostgreSQL DBA gets to
the point of designing a sufficently complex schema that inheritance
might be useful, they quickly bump up against the lack of index and
constraint spanning (most notably, referential integrity), and stop
right there. This means that there is little community experience with
the existing implementation, beyond the OO die hards. ;-)

I'm not sure, but Bruce's suggestion of getting index spanning working
first might move the existing implementation over the hump from
'interesting toy' to 'less than perfect implementation'. Then, the
community can get some real world experience.

Bruce has archived some of the emails - check your local pgsql source tree,
under <$PGSQLHOME>/doc/TODO.detail/inheritance

There was also some theoretical OO discussion, back when the change for
default SELECT behavior on an inhertiance tree was made. (You used to
have to say: SELECT foo from parent* to get foo from the parent and all
children) Take a look at the archives and see if there's anything in that
discussion that interests you: providing summary posts of old discussions
is often a good way to restart and move an unresolved topic along.

Ross

Ross J. Reedstrom wrote:

Actually, I think you'll find that once a PostgreSQL DBA gets to
the point of designing a sufficently complex schema that inheritance
might be useful, they quickly bump up against the lack of index and
constraint spanning (most notably, referential integrity), and stop
right there. This means that there is little community experience with
the existing implementation, beyond the OO die hards. ;-)

I'd have to agree wholeheartedly with this, because this was exactly my
experience the one time I wanted to use inherited tables.

FWIW, one thought I've had before related to inheritance (but pretty
much orthognal to this discussion) is this: if inheritance included
shared indexes and constraints, we would be not too far from having
Oracle style table partitioning.

Joe

On Wed, 2002-08-14 at 10:17, Ross J. Reedstrom wrote:

On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote:

I completely agree. This is why I want/wanted to pursue the theory and
existing implementations angle.

In theory, it sounds like a good idea. In practice ... ;-)

LOL. :)

Seems like everyone trying to jump on "index spanning" is premature.

Seems like some people haven't looked at the history of the OO
implementation in PostgreSQL.

[waving hand...]

Bruce has archived some of the emails - check your local pgsql source tree,
under <$PGSQLHOME>/doc/TODO.detail/inheritance

There was also some theoretical OO discussion, back when the change for
default SELECT behavior on an inhertiance tree was made. (You used to
have to say: SELECT foo from parent* to get foo from the parent and all
children) Take a look at the archives and see if there's anything in that
discussion that interests you: providing summary posts of old discussions
is often a good way to restart and move an unresolved topic along.

Thanks! I briefly read something about that in the archives. Excellent
pointers. I'll check that out. If I have time, I'll try to summarize
and post.

Greg Copeland

On Tuesday 13 August 2002 08:07 pm, Curt Sampson wrote:

On Tue, 13 Aug 2002, Lamar Owen wrote:

Curt, I think his reply stems from his frustration of chosen content in
many emails that originate from you. We all pretty well understand
postgres has a broken feature. We all understand you see zero value in

Knowing Don to some extent, I can say with some assurance that his
'attacks' are never unprovoked.

Sorry; I'm not aware of the circumstances under which one is supposed
to call someone a "dick-waver" and other such things on a technical
mailing list. Perhaps you can explain to me when one should be
doing this, so I too can do it at the appropriate times.

I never said I agreed with his wording; in fact I don't agree with his
wording. But that's not the point. The point is that the discussion was
going absolutely nowhere, quickly. Don's colorful metaphors (for lack of a
better term) aren't ones I would use, by any means -- but they had the
desired effect, didn't they?

The discussion has since progressed from 'the feature is broken because I say
it is' to 'how can we fix the broken feature' -- which is where Don, Hannu,
and Greg, unless I am mistaken, were all going towards. If you, Curt, were
just trying to play devil's advocate you went just a little too far, too
vehemently, and were flamed in the old alt.flame tradition. Had the words
'Hitler' or 'Nazi' shown up we would have known it had gone the next step --
and I'm just relating Usenet tradition here -- I'm not a party to that
tradition, but I certainly have seen enough flamewars to know what they
disintegrate into. I for one am glad you toned down the 'devil's advocate'
point of view so that a useful discussion arises (which has indeed happened).

And I just stated my experience with Don -- no agreement (or judgment) was
implied or stated. I've just developed code beside him before. I wish I had
more time to develop code on OpenACS, in fact -- but that's even further
off-topic. Don Baccus is well-mannered and even tempered until provoked.
When provoked; well, you see what happens.

Now, let's see the constructive discussion continue, without authoritarian
posturing (for lack of a more technical term for Don's colorful metaphor).
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Greg Copeland <greg@CopelandConsulting.Net> writes:

On Tue, 2002-08-13 at 23:43, Curt Sampson wrote:

And I think a detailed description comes most easily when you have
a logical model to work from.

I completely agree. This is why I want/wanted to pursue the theory and
existing implementations angle.

Seems like everyone trying to jump on "index spanning" is premature.

I agree. Table-spanning indexes would be a large, complex,
difficult-to-get-right feature. Before diving into that we should get
some idea of just how we'd actually use them, and whether that's the
only big chunk of work standing between us and a more useful inheritance
feature. I'm afraid we might do all that effort and then discover there
are other showstoppers.

regards, tom lane

On Wed, 2002-08-14 at 11:17, Ross J. Reedstrom wrote:

On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote:

On Tue, 2002-08-13 at 23:43, Curt Sampson wrote:

Just my opinion of course, but I think it would be best to have a
detailed description of how everything in inheritance is supposed to
work, write a set of tests from that, and then fix the implementation to
conform to the tests.

And I think a detailed description comes most easily when you have
a logical model to work from.

I completely agree. This is why I want/wanted to pursue the theory and
existing implementations angle.

In theory, it sounds like a good idea. In practice ... ;-)

Seems like everyone trying to jump on "index spanning" is premature.

Seems like some people haven't looked at the history of the OO
implementation in PostgreSQL.

Actually, I think you'll find that once a PostgreSQL DBA gets to
the point of designing a sufficently complex schema that inheritance
might be useful, they quickly bump up against the lack of index and
constraint spanning (most notably, referential integrity), and stop

Only took a few minutes to write a couple of triggers to manage most of
my needs. Not very generic, but gives me cross table uniqueness ;)

On Wed, 14 Aug 2002, Tom Lane wrote:

I agree. Table-spanning indexes would be a large, complex,
difficult-to-get-right feature. Before diving into that we should get
some idea of just how we'd actually use them, and whether that's the
only big chunk of work standing between us and a more useful inheritance
feature. I'm afraid we might do all that effort and then discover there
are other showstoppers.

That's my biggest fear as well. Here are a couple of possible
assertions we could make about supertables and subtables that have,
I think, some fairly far-reaching implications.

1. All constraints one places on a supertable must "work." That is,
they must apply on all subtables as well, and must always be true
on the supertable. For example, if I apply the constraint, "this
int field must be no smaller than 1 and no larger than 100," to the
supertable, this must apply to all subtables, and you must not be
able to remove the constraint from just a subtable."

2. It must not be possible apply a constraint to a supertable that
could be violated.

3. All constraints that one can apply to a non-inherited table in
postgresql must also be able to be applied to a supertable.

Depending on which of these you want to implement, and how you do
it, you may get yourself into a position where you can create a
table that that cannot have subtables, or cannot put certain constraints
on supertables....

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Curt Sampson <cjs@cynic.net> writes:

That's my biggest fear as well. Here are a couple of possible
assertions we could make about supertables and subtables that have,
I think, some fairly far-reaching implications.

CHECK-style constraints don't seem like a huge issue to me. We already
have recursive ALTER TABLE ADD CONSTRAINT, and IIRC we do actually
arrange for CHECK constraints on a parent to be inherited when a child
is created. We could argue about whether, for example, non-recursive
ADD CONSTRAINT should be disallowed or not --- but that's not any kind
of implementation showstopper, just a definitional issue about
flexibility vs. safety.

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.

What I'm not sure about is what other gotchas may be lurking...

regards, tom lane

On Wed, 14 Aug 2002, Tom Lane wrote:

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.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

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 all light. --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 all light. --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 all light. --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 to him 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, Pennsylvania 19073

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

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

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

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

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

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.
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, 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 work right 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[],
LIKE engine,
) 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/impementations for 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, 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 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 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 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 all light. --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 all light. --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 all light. --XTC

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

animal
quadruped (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
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: