Re: Why is MySQL more chosen over PostgreSQL? - Mailing list pgsql-hackers

On 7 Aug 2002, Hannu Krosing wrote:

> > Theory: Sure. But this is much harder to express in a turing machine
> > isn't it?
>
> You got it ;) The claim was that it is easiest to express it using
> inheritance, a little harder using pure relational model and much harder
> using a Turing machine.

Ok. I agree that it's much harder with a turning machine. I do *not*
agree that it's harder with the relational model. In fact, since you
*must* use the relational model for some things, I argue that it's
harder to switch back and forth between the relational and OO models,
and understand the effects of each on the other, than it is just to do
it in OO form in the first place.

In fact, I'd argue at this point, as far as table inheritance goes,
we don't even have a real model here. Let's look at a few of the problems.

1. I create a base table with an column with a UNIQUE constraint on
it, and two child tables. I can insert the same value into that column
into the two child tables, thus violating the unique constraint in the
base table. Now how can it be acceptable, in postgres or any other
relational database, to have a column declared to contain unique values
have non-unique values in it? (Note that this was the source of my
error in re-implementing some table-inheritance-modeled stuff here in
relational form; I preserved that unique constraint when I should not
have.)

2. When you have child1 and child2 tables both inheriting directly
from a base table, you can have entries in both child1 and child2
whose component from the base table is the same. What does this
mean? Are we supposed to be able to have objects that can simultaneously
be both subtypes?

Well, I could go on, but just from this you can see that:
   1. We appear to have no proper theory even defined for how   table inheritance should work.
   2. If we did, either postgres is not consistent with it, or   the theory itself is in conflict with the relational
portion  of the database.
 

Whatever way you look at it, it's apparent to me that using table
inheritance is dangerous, confusing, and should be avoided if you
want to maintain data integrity and a self-consistent view of your
data.

> > No, I mean set up your database so that a card can be a network_card
> > or a sound_card, but not both.
>
> Why can't you do this using inheritance ?
>
> create table card(...);
> create table network_card(...) inherits(card);
> create table sound_card(...) inherits(card);
>
> should do exactly that.

But it doesn't. You can have an entry in network_card and another one in
sound_card which share the same primary key in the sound_card table.

> in this case wasting a bit of space == having incorrect data.

No, it doesn't. Your queries will never return incorrect data; the
"unused" records will be ignored.

> The possiblity of getting out wrong data always exists if there is
> incorrect data in the system.

No, you can't put incorrect data into the system. The data about what
type of card it is is not in the sound_card or network_card table, but
in the card table itself, and thus it can only ever have one value for
any card entry. It's impossible for that column to have more than one
value, thus impossible for that column to have incorrect data.

Now you may argue that, because there's an entry for that card in
both network_card and sound_card, that means that the card has two
types. But that's just deliberate misinterpretation, because you're
getting the type information from the wrong place.  You might as
well argue that a table holding temperatures is "incorrect data"
because someone put them in in degress centigrate, and you're
interpreting them as degrees Fahrenheit when you pull them out.

> > Sure looks like a join to me.
>
> But you did not have to write it - it was written, debugged and
> optimised by postgres.

So? The argument I was replying to stated that his method was more
efficient because it didn't use joins. Who wrote the join does not
matter; it turns out that inside it all joins happen, and so it's
not more efficient.

> > But anyway, I realized that some of the joins I've shown are
> > unnecessary; I've incorrectly implemented, relationally, the inheritance
> > model you've shown. Here's the explanation:
>
> Which proves that using lower level idioms for describing inheritance is
> more error prone.

No, it proves that the semantics of table inheritance are confusing, or
postgres incorrectly impelements them, or both. This kind of mistake is
*exactly* the reason I avoid table inheritance; I couldn't tell just
what you were doing! And I still am not convinced that what you were
doing was what you wanted to do, especially given that I've seen other
complaints in this forum that table inheritance specifically was *not*
doing what people wanted it to do (thus the plea for cross-table unique
indexes).

> I suspect that the fact that this is implemented and general updatable
> views are not is due to bigger complexity of doing this for a general
> case than for specific "inheritance" case.

I'll agree with that.

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