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

On Wed, 2002-08-07 at 06:48, Curt Sampson wrote:
> On Tue, 6 Aug 2002, Don Baccus wrote:
> 
> > So again relational theory can solve the problem but at a cost in
> > efficiency.
> 
> If you're talking about theory, efficiency doesn't come into it.
> The question is how and whether you can express the constratints
> you need to express.
> 
> Note that I am not advocating removing anything that does not fit into
> relational theory but does let us do things more efficiently. We live
> in an imperfect world, after all.
> 
> In fact, why don't we split the dicussion into two separate parts:
> relational theory vs. object-oriented theory, and practical use
> with postgres, and never mix the two. Ok?
> 
> > So could a Turing machine.
> 
> 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.

> > The view would work, but of course you have to define the view.  Any
> > time you have to do something manually, even something as simple as to
> > define a view, the chance for casual error is introduced.
> 
> Theory: views should automatically make themselves as updatable as
> possible, unless expressed otherwise. In fact, relationally, there
> is no difference between a view and a base table; that's only part
> of a storage model, which doesn't come into it in our perfect
> theoretical world.
> 
> Practice: defining a non-updatable view is pretty trivial in
> postgres.  Defining an updatable view is rather harder, and more
> subject to error.

But defining an updatable inherited table is easy .

>  However, in this particular case it's a necessary
> evil, since you can't use table inheritance to do what you want.
> > > Oops, did I just replace your "object-oriented" system with a
> > > relational one that does everything just as easily, and even does
> > > something the object-oriented one can't do?
> >
> > You mean "waste space with meaningless extra 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.

> You may also waste some space with meaningless data, if you have bugs
> in your application, but a) that meaningless data is pretty easy to
> clean up, and b) wasting a bit of space is a lot better than having
> incorrect data.

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

The possiblity of getting out wrong data always exists if there is
incorrect data in the system. You can't reasonably expect that nobody
will query just the network_card table without doing the fancy join with
additional card.type='N'. The join version is also bound to be always
slower than the non-join version.

> > Me, too.  The relational model is extremely powerful but it's not the
> > be-all and end-all of all things.
> 
> Theory: Never said it was. I said that table inheritance is an
> unnecessary addition to a relational database; it offers no capabilities
> you can't offer within the relational model, nor does it make things
> easier to do than within the relational model. (Since we are talking
> about theory, I hasten to add that it is possible to implement something
> where the OO way is easier to use than the relational way, but you're
> not forced to implement things this way.)
> 
> > You still haven't answered my earlier observation that the PG model,
> > with all its flaws, can reduce the number of joins required.
> 
> Sorry. Let me deal with that now: that's an incorrect observation.
> 
> > For instance in your example card and network card need to be joined if
> > you want to return network card.  That's what I see in the view.
> >
> > "FROM card, network_card"
> >
> > Using PG's inheritance no join is necessary.
> 
> But going the other way around:
> 
>     FROM card
> 
> Result  (cost=0.00..27.32 rows=6 width=36)
>   ->  Append  (cost=0.00..27.32 rows=6 width=36)
>         ->  Index Scan using ih_parent_pkey on ih_parent 
(cost=0.00..4.82 rows=1 width=36)
>         ->  Seq Scan on ih_child ih_parent  (cost=0.00..22.50 rows=5
width=36)
> 
> Sure looks like a join to me.
> 

But you did not have to write it - it was written, debugged and
optimised by postgres.

> > So ... assuming my assumption is true and that you've bothered to study
> > the implementation, why should I prefer the join over the
> > faster-executing single-table extraction if I use PG's type extension
> > facility?
> 
> Well, it depends on what your more frequent queries are.
> 
> 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. 

Btw, this is a general principle - the more lines of code you write to
solve the same problem, the more possibilities you have to make errors.
Given enough possibilities, everyone makes errors.

OTOH, sometimes you need to do low-level work to get the last bit of
performance out of the systems (sometimes down to assembly level).

...

> It could even happen that you will show me something that the relational
> model just doesn't handle, in which case you'll have won the argument.

As the inheritance model is built on top of relational one, it is
impossible to come up with something that relational model does not
handle. Just as it is impossible to show you a VIEW that can't be done
with ON SELECT DO INSTEAD rules.

What our current implementation does show, is that there is a subset of
generated views that are updatable. They are not explicitly statically
defined as views (because they change dynamically as new child tables
are inherited) but they are constructed each time you do a
SELECT/UPDATE/DELETE on parent table. 

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.

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