Horton

Thread

  1. Why is MySQL more chosen over PostgreSQL?

    Matthew Tedder <matthew@tedder.com> — 2002-07-28T23:08:53Z

    Just a long standing curiosity?

For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
more capabilites so why build upon a limited base such as MySQL?

Does anyone here have any idea as to why so many people select MySQL when 
both systems are open sourced?

Matthew

  2. Re: Why is MySQL more chosen over PostgreSQL?

    Your Name <cbbrowne@cbbrowne.com> — 2002-07-29T12:53:20Z

    > 
> Just a long standing curiosity?
> 
> For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
> more capabilites so why build upon a limited base such as MySQL?
> 
> Does anyone here have any idea as to why so many people select MySQL when 
> both systems are open sourced?

Three likely effects:

a) ISP management toolsets include management tools for MySQL, and not 
PostgreSQL.

(CPanel is an example of such a toolset.)

b) Apparently the permissions model for PostgreSQL used to discourage its use 
in shared hosting environments.  (Ask Neil Conway more about this.)

c) There was corporate sponsorship of MySQL, and they probably spent money 
marketing it in the ISP web hosting market.

d) MySQL is GPL-licensed, and some people consider that very important.  (And 
are too stupid to grasp that they like XFree86, which _isn't_ licensed under 
the GPL...  Of course, this is d), and I said "three" likely effects...)

e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
and you'll have to show something _really compelling_.
--
(concatenate 'string "cbbrowne" "@acm.org")
http://cbbrowne.com/info/advocacy.html
FLORIDA: Where your vote counts and counts and counts.

  3. Re: Why is MySQL more chosen over PostgreSQL?

    Roderick A. Anderson <raanders@acm.org> — 2002-07-29T13:39:59Z

    On Mon, 29 Jul 2002 cbbrowne@cbbrowne.com wrote:

[snip]

> e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
> apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
> and you'll have to show something _really compelling_.

I would like to add one other thought.  There are many web site designers
that get thrust into being a web site programmer.  Without an
understanding of database design and a novice programmers (?) view of the
process the benefits of letting the database (RDBMS) do the database work
isn't recognized.  They code it all in the CGI.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."

  4. Re: Why is MySQL more chosen over PostgreSQL?

    Vitaliy N. Kravchenko <kvn@phbme.ntu-kpi.kiev.ua> — 2002-07-29T14:32:10Z

    Matthew Tedder <matthew@tedder.com> wrote:
> For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
> more capabilites so why build upon a limited base such as MySQL?
> Does anyone here have any idea as to why so many people select MySQL when 
> both systems are open sourced?
Some people working on win32 platforms, and mysql easy install on win32.
Just for starting on use databases in soft.

PgSQL easy-install on *unix-systems (mostly..:)), but on win32 ..it's hard..:(

IMHO.

--
Best regards, KVN.
 PHP4You   (<http://php4you.kiev.ua/>)
 PEAR [ru] (<http://pear.php.net/manual/ru/>)
  mailto:kvn@php.net

  5. Re: Why is MySQL more chosen over PostgreSQL?

    Chris Humphries <chumphries@devis.com> — 2002-07-29T14:39:58Z

    well that and people tend to drift towards an easy answer,
like php... amazing how that combo is so popular... hrrmm...

Roderick A. Anderson writes:
 > On Mon, 29 Jul 2002 cbbrowne@cbbrowne.com wrote:
 > 
 > [snip]
 > 
 > > e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
 > > apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
 > > and you'll have to show something _really compelling_.
 > 
 > I would like to add one other thought.  There are many web site designers
 > that get thrust into being a web site programmer.  Without an
 > understanding of database design and a novice programmers (?) view of the
 > process the benefits of letting the database (RDBMS) do the database work
 > isn't recognized.  They code it all in the CGI.
 > 
 > 
 > Rod
 > -- 
 >   "Open Source Software - Sometimes you get more than you paid for..."
 > 
 > 
 > ---------------------------(end of broadcast)---------------------------
 > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
Chris Humphries
Development InfoStructure
540.366.9809

  6. Re: Why is MySQL more chosen over PostgreSQL?

    Roderick A. Anderson <raanders@acm.org> — 2002-07-29T15:07:08Z

    On Mon, 29 Jul 2002, Chris Humphries wrote:

> well that and people tend to drift towards an easy answer,
> like php... amazing how that combo is so popular... hrrmm...

Well people seem to get so ... about php that I didn't want to touch that 
topic.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."

  7. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-29T15:59:40Z

    On Mon, 29 Jul 2002, Roderick A. Anderson wrote:

> I would like to add one other thought. There are many web site
> designers that get thrust into being a web site programmer. Without
> an understanding of database design and a novice programmers (?) view
> of the process the benefits of letting the database (RDBMS) do the
> database work isn't recognized. They code it all in the CGI.

Well, I'll add two points to this, then:

1. Often there's a lot more benefit to moving the work from the database
to the application structure. Database schemas are hard to change, and
hard to keep under revision control. When I was doing a large website,
it was much, much easier to say "everything goes through these Java
classes" than "everything goes through the database." I could change the
database schema at will and know that my data was safe, because I could
have old interfaces running simultaneously with new.

(Though I'll admit, good view support would have mitigated this problem
quite a lot. But there is *no* database in the world that has really
good view support; they all fail on various updates where one can
theoretically do the Right Thing, but in practice it's very difficult.
And I don't think that's going to change any time soon.)

2. I expect that even most PostgreSQL--or even database--experts don't
have a real understanding of relational theory, anyway. That we still
have table inheritance shows that. As far as I can tell, there is
nothing whatsoever that table inheritance does that the relational model
does not handle; the whole "OO" thing is just another, redundant way of
doing what we already ought to be able to do within the relational model.

I'm still waiting to find out just what advantage table inheritance
offers. I've asked a couple of times here, and nobody has even started
to come up with anything.

All that said, though, don't take this as any kind of a dismissal of
postgres. It's in most ways better than MySQL and also some commericial
systems, and many of its failures are being addressed. Postgres for some
reason seems to attract some really, really smart people to work on it.
If I could see something better, I'd be there. But I don't.

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

  8. Re: Why is MySQL more chosen over PostgreSQL?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-29T16:30:56Z

    Curt Sampson wrote:
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.

We inherited inheritance from Berkeley.  I doubt we would have added it
ourselves.  It causes too much complexity in other parts of the system.

> All that said, though, don't take this as any kind of a dismissal of
> postgres. It's in most ways better than MySQL and also some commericial
> systems, and many of its failures are being addressed. Postgres for some
> reason seems to attract some really, really smart people to work on it.
> If I could see something better, I'd be there. But I don't.

Interbase/Firebird maybe?  They just came out with a 1.0 release in
March.

As for why PostgreSQL is less popular than MySQL, I think it is all
momentum from 1996 when MySQL worked and we sometimes crashed.  Looking
forward, I don't know many people who choose MySQL _if_ they consider
both PostgreSQL and MySQL, so the discussions people have over MySQL vs.
PostgreSQL are valuable because they get people to consider MySQL
alternatives, and once they do, they usually choose PostgreSQL.

As for momentum, we still have a smaller userbase than MySQL, but we are
increasing our userbase at a fast rate, perhaps faster than MySQL at
this point.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  9. Re: Why is MySQL more chosen over PostgreSQL?

    Mike Mascari <mascarm@mascari.com> — 2002-07-29T16:49:29Z

    Bruce Momjian wrote:
> 
> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> 
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

...

> As for why PostgreSQL is less popular than MySQL, I think it is all
> momentum from 1996 when MySQL worked and we sometimes crashed.  Looking
> forward, I don't know many people who choose MySQL _if_ they consider
> both PostgreSQL and MySQL, so the discussions people have over MySQL vs.
> PostgreSQL are valuable because they get people to consider MySQL
> alternatives, and once they do, they usually choose PostgreSQL.
> 
> As for momentum, we still have a smaller userbase than MySQL, but we are
> increasing our userbase at a fast rate, perhaps faster than MySQL at
> this point.

Its all due to sort-order. If Oracle was open source MySQL would still
be more popular. ;-)

Mike Mascari
mascarm@mascari.com

  10. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-29T17:01:19Z

    On Mon, 29 Jul 2002, Bruce Momjian wrote:

> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
>
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

Ah, all the more reason to remove it, then! :-)

But really, please don't take that as a criticism of the current development
direction; I know it was inherited, and it's not new code. In fact, I think
it probably wasn't until _The Third Manifsto_ came out in 1998 that it
really became clear that table inheritance was not terribly useful--if it's
even generally known now. And even so, I'm open to other opinions on that,
since it's not been an intensive area of study by any means.

> > All that said, though, don't take this as any kind of a dismissal of
> > postgres. It's in most ways better than MySQL and also some commericial
> > systems, and many of its failures are being addressed. Postgres for some
> > reason seems to attract some really, really smart people to work on it.
> > If I could see something better, I'd be there. But I don't.
>
> Interbase/Firebird maybe?  They just came out with a 1.0 release in March.

Once in a while I go back to it, but I still can't build the darn thing
from scratch. Which makes it a bit difficult to evaluate....

> As for why PostgreSQL is less popular than MySQL, I think it is all
> momentum from 1996 when MySQL worked and we sometimes crashed.

Right. I have a lot of hope. After all, MySQL was for a couple of
years a second-runner to mSQL, remember?

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

  11. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-07-29T18:57:37Z

    On Mon, 2002-07-29 at 19:01, Curt Sampson wrote:
> On Mon, 29 Jul 2002, Bruce Momjian wrote:
> 
> > Curt Sampson wrote:
> > > I'm still waiting to find out just what advantage table inheritance
> > > offers. I've asked a couple of times here, and nobody has even started
> > > to come up with anything.

It is mostly a syntactic thing that makes it easier to humans to write
cleaner code.

Otherwise, it is proved that anything can be written for a Turing
Machine ;)

> > We inherited inheritance from Berkeley.  I doubt we would have added it
> > ourselves.  It causes too much complexity in other parts of the system.
> 
> Ah, all the more reason to remove it, then! :-)
>

It would make more sense to make it compatible with SQL99 and drop the
current behaviour only after that if possible.

As it stands now it is a strange mix of SQL99's

  CREATE TABLE thistable(...,LIKE anothertable,...);
and
  CREATE table mytable(...) UNDER anothertable;

with only a few additional goodies, like SELECT* (i.e not ONLY) which
selects from all tables that inherit from this.

other things that should be done are not (like inheriting constraints,
foreign and primary keys, triggers, ...)

Also we currently can't return more than one recordset from a query,
which also makes selecting from an inheritance hierarchy less versatile.

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

  12. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-07-30T08:13:24Z

    On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:

> Bruce Momjian:
> > It causes too much complexity in other parts of the system.
> 
> That's one reason.

Seems like somewhat valid reason. But still not enough to do a lot of
work _and_ annoy a lot of existing users :)

> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything. and
> > there is nothing whatsoever that table inheritance does that the
> > relational model does not handle
> 
> That's the other one.

That's quite bogus imho. You could just as well argue that there is
nothing that relational model handles that can't be done in pure C.

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

  13. Re: Why is MySQL more chosen over PostgreSQL?

    Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> — 2002-07-30T08:19:39Z

    On Mon, 2002-07-29 at 18:30, Bruce Momjian wrote:
> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> 
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

How about dropping it, then?

Just start to emit 

WARNING: inheritance will be dropped with postgres 8.0 
WARNING: please refer to http://.../ for an explanation why.

right now on every CREATE TABLE that uses it.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg

  14. Re: Why is MySQL more chosen over PostgreSQL?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-07-30T08:45:43Z

    > > We inherited inheritance from Berkeley.  I doubt we would have added it
> > ourselves.  It causes too much complexity in other parts of the system.
>
> How about dropping it, then?
>
> Just start to emit
>
> WARNING: inheritance will be dropped with postgres 8.0
> WARNING: please refer to http://.../ for an explanation why.
>
> right now on every CREATE TABLE that uses it.

Why?  It doesn't hurt you personally!  Plus, it would annoy a _boatload_ of
existing inheritance users.

A more interesting question I think is how to allow our indexes to span
multiple relations, _without_ causing any performance degradation for non
inheritance users...

Chris

  15. Re: Why is MySQL more chosen over PostgreSQL?

    Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> — 2002-07-30T09:51:45Z

    [don't cc: me, please.]
[please leave proper attribution in]

On Tue, 2002-07-30 at 10:45, Christopher Kings-Lynne wrote:
> > > We inherited inheritance from Berkeley.  I doubt we would have added it
> > > ourselves.  It causes too much complexity in other parts of the system.

[Inheritance]

> > How about dropping it, then?
[...]

> Why?  It doesn't hurt you personally!  

That's correct.

> Plus, it would annoy a _boatload_ of
> existing inheritance users.

Bruce Momjian:
> It causes too much complexity in other parts of the system.

That's one reason.

Curt Sampson wrote:
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.
and
> there is nothing whatsoever that table inheritance does that the
> relational model does not handle

That's the other one.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg

  16. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-30T11:00:13Z

    On 30 Jul 2002, Hannu Krosing wrote:

> On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
>
> > Bruce Momjian:
> > > It causes too much complexity in other parts of the system.
> >
> > That's one reason.
>
> Seems like somewhat valid reason. But still not enough to do a lot of
> work _and_ annoy a lot of existing users :)

It's almost unquestionably more work to maintain than to drop. Dropping
support for it is a one-time operation. Maintaining it is an ongoing
expense.

> That's quite bogus imho. You could just as well argue that there is
> nothing that relational model handles that can't be done in pure C.

That's a straw man argument. What we (or I, anyway) are arguing is that
the relational model does everything that table inheritance does, and at
least as easily. Extending the model adds complexity without adding the
ability to do things you couldn't easily do before. (This, IMHO, makes
table inheritance quite inelegant.)

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

  17. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-30T11:02:44Z

    On 29 Jul 2002, Hannu Krosing wrote:

> > > Curt Sampson wrote:
> > > > I'm still waiting to find out just what advantage table inheritance
> > > > offers. I've asked a couple of times here, and nobody has even started
> > > > to come up with anything.
>
> It is mostly a syntactic thing that makes it easier to humans to write
> cleaner code.

And how is using table inheritance "cleaner" than doing it the
relational way? It adds extra complexity to the system, which is
automatically a reduction in cleanliness, so it would have to have
some correspondingly cleanliness-increasing advantages in order
to be cleaner, overall.

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

  18. Re: Why is MySQL more chosen over PostgreSQL?

    D'Arcy Cain <darcy@druid.net> — 2002-07-30T11:46:58Z

    * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 04:20]:
> On Mon, 2002-07-29 at 18:30, Bruce Momjian wrote:
> > Curt Sampson wrote:
> > > I'm still waiting to find out just what advantage table inheritance
> > > offers. I've asked a couple of times here, and nobody has even started
> > > to come up with anything.

I think one of the values of it is that it is something that no one else
has.  It distinguishes us.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

  19. Re: Why is MySQL more chosen over PostgreSQL?

    Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> — 2002-07-30T12:01:35Z

    [No cc: please. Especially if you're not commenting on anything I said]

On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > Curt Sampson wrote:
> > > > I'm still waiting to find out just what advantage table inheritance
> > > > offers. I've asked a couple of times here, and nobody has even started
> > > > to come up with anything.
> 
> I think one of the values of it is that it is something that no one else
> has.  It distinguishes us.

Coooool. Let's have the 'automatically phone KFC if developer works more
than 8 hours non-stop' feature, *that* is something nobody else has.
Yes. Cool.

In other words: this is an absolutely bogus argument.

As an implementor I'm always wary of using features nobody else has,
especially in databases. So, if I'd want postgres to have one thing
nobody else has, it would be the most complete standard SQL
implementation - so it would at least be the other products' fault if
I'd have to do any special porting work to/from postgres.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg

  20. Re: Why is MySQL more chosen over PostgreSQL?

    Rod Taylor <rbt@zort.ca> — 2002-07-30T12:13:42Z

    > As an implementor I'm always wary of using features nobody else has,
> especially in databases. So, if I'd want postgres to have one thing
> nobody else has, it would be the most complete standard SQL
> implementation - so it would at least be the other products' fault if
> I'd have to do any special porting work to/from postgres.

Why can't both be done?  If nobody extended the spec or came up with new
features there wouldn't exactly be any progress.

Yes, meeting the spec is a good goal, and one that is getting quite
close as far as the SQL part goes -- but it shouldn't be the only goal.


Inheritance currently saves me from issuing ~4 inserts, updates, deletes
as it handles it itself.  If indexes and a couple other things worked
across the entire tree it could be more useful.

I think what we need to do is expand on it, not blow it away.


There is a list of spec features we support. Stick to those (or the
subset) that is appropriate for portability.  If you plan on making an
embedded DB Based application the extra features may be useful.

  21. Re: Why is MySQL more chosen over PostgreSQL?

    D'Arcy Cain <darcy@druid.net> — 2002-07-30T12:28:33Z

    * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > I think one of the values of it is that it is something that no one else
> > has.  It distinguishes us.
> 
> Coooool. Let's have the 'automatically phone KFC if developer works more
> than 8 hours non-stop' feature, *that* is something nobody else has.
> Yes. Cool.

Excuse me all to hell but are you in the junior debating class or what?
No one said we need to include every possible feature just because it
is not in other products.  Your KFC suggestion has nothing whatsoever
to do with database management.  Inheritance does.  It is useful to
some and, as I said *one of the values* is the way it distinguishes us.

For the record, I do use the feature and I would miss it if it disappeared.
I think it can be improved upon, especially in the area of indexes and
prmary keys but overall it is a nice feature that has the added benefit
of differentiating us from other RDBMS systems.

> As an implementor I'm always wary of using features nobody else has,

How very conservative of you.  Personally I have spent my life trying
to do new things.  If I wanted Oracle or DB2 I know where to find it.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

  22. Re: Why is MySQL more chosen over PostgreSQL?

    Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> — 2002-07-30T12:44:05Z

    [Still no cc:s please]

On Tue, 2002-07-30 at 14:28, D'Arcy J.M. Cain wrote:
> * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> > On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > I think one of the values of it is that it is something that no one else
> > > has.  It distinguishes us.
> > 
> > Coooool. Let's have the 'automatically phone KFC if developer works more
> > than 8 hours non-stop' feature, *that* is something nobody else has.
> > Yes. Cool.
> 
> Excuse me all to hell but are you in the junior debating class or what?

Sure, I was taking it to the extreme here (And I really am sorry if you
felt offended by my remark). But I strongly feel that having a feature
because 'it is something that no one else has.  It distinguishes us.' is
no justification at all.

Of course, if a feature provides some real use, then it is worth having
(yes, even if it's not in the standard). But exactly this seems not so
clear in the case of inheritance in postgres.

(And that's where I'm starting to say things I've said before. So I'll
just shut up now.)

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg

  23. Re: Why is MySQL more chosen over PostgreSQL?

    Iavor Raytchev <iavor.raytchev@verysmall.org> — 2002-07-30T12:45:23Z

    
Adrian 'Dagurashibanipal' von Bidder wrote:

> (And that's where I'm starting to say things I've said before. So I'll
> just shut up now.)

May be you can contribute some code :)

  24. Inheritance (was: Re: Why is MySQL more chosen over PostgreSQL?)

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-07-30T13:48:37Z

    > > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> and
> > there is nothing whatsoever that table inheritance does that the
> > relational model does not handle
> 
> That's the other one.

Irrelevant - thousands of people are using the feature!

Chris

  25. Re: Why is MySQL more chosen over PostgreSQL?

    Neil Conway <nconway@klamath.dyndns.org> — 2002-07-30T14:10:20Z

    On Tue, Jul 30, 2002 at 02:01:35PM +0200, Adrian 'Dagurashibanipal' von Bidder wrote:
> As an implementor I'm always wary of using features nobody else has,
> especially in databases. So, if I'd want postgres to have one thing
> nobody else has, it would be the most complete standard SQL
> implementation - so it would at least be the other products' fault if
> I'd have to do any special porting work to/from postgres.

SQL99 includes inheritance (albeit a somewhat different implementation
than the design in Postgres right now) -- so the "most complete standard
SQL implementation" would need to include inheritance.

I'd say removing inheritence would be a waste of time -- it would
probably be easier to just fix its deficiencies.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

  26. Re: Why is MySQL more chosen over PostgreSQL?

    Thomas Lockhart <lockhart@fourpalms.org> — 2002-07-30T14:24:45Z

    > ... But I strongly feel that having a feature
> because 'it is something that no one else has.  It distinguishes us.' is
> no justification at all.

One reason why we have a database which *does* come very close to the
standards is precisely because it had (and has) things which no one else
had (or has). It demonstrated how to do things which are now part of
SQL99, but which were not implemented *anywhere else* back in the early
'90s.

Inheritance is not as well supported by us, but that is our fault for
focusing on other things recently. I think that some of the recent work
will end up benefiting inheritance features, so these might make some
progress soon too.

Search and destroy missions to eliminate all that is not "standard" will
diminish the product, because we will be constrained to work entirely
within the boundaries of a standard which is poorly thought out around
the edges. If our boundaries are always just a bit wider than that we'll
be OK ;)

All imho of course...

                     - Thomas

  27. Re: Why is MySQL more chosen over PostgreSQL?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T15:30:49Z

    Adrian 'Dagurashibanipal' von Bidder wrote:

Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> [Still no cc:s please]
> 
> On Tue, 2002-07-30 at 14:28, D'Arcy J.M. Cain wrote:
> > * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> > > On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > > I think one of the values of it is that it is something that no one else
> > > > has.  It distinguishes us.
> > > 
> > > Coooool. Let's have the 'automatically phone KFC if developer works more
> > > than 8 hours non-stop' feature, *that* is something nobody else has.
> > > Yes. Cool.
> > 
> > Excuse me all to hell but are you in the junior debating class or what?
> 
> Sure, I was taking it to the extreme here (And I really am sorry if you
> felt offended by my remark). But I strongly feel that having a feature
> because 'it is something that no one else has.  It distinguishes us.' is
> no justification at all.

I thought the KFC thing was very funny.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  28. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-07-30T19:54:14Z

    On Tue, 2002-07-30 at 16:00, Curt Sampson wrote:
> On 30 Jul 2002, Hannu Krosing wrote:
> 
> > On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
> >
> > > Bruce Momjian:
> > > > It causes too much complexity in other parts of the system.
> > >
> > > That's one reason.
> >
> > Seems like somewhat valid reason. But still not enough to do a lot of
> > work _and_ annoy a lot of existing users :)
> 
> It's almost unquestionably more work to maintain than to drop. Dropping
> support for it is a one-time operation. Maintaining it is an ongoing
> expense.

I would not rush to drop advanced features, as they may be hard to put
back later. If they stay in, even in broken form, then there wont be
nearly as much patches which make fixing them harder.

I'm afraid that we have already dropped too much. 

For example we dropped time travel, but recent versions of Oracle now
have some form of it, usable mostly for recovering accidentally deleted
(and committed rows), although it is much harder to implement it using
logs than using MVCC.

Also, I suspect that dropping support for multiple return sets for one
query was done too fast.

> > That's quite bogus imho. You could just as well argue that there is
> > nothing that relational model handles that can't be done in pure C.
> 
> That's a straw man argument.

Actually it was meant to be 'one straw man against another straw man 
argument' ;)

> What we (or I, anyway) are arguing is that
> the relational model does everything that table inheritance does, and at
> least as easily.

The problem is that 'the relational model' does nothing by itself. It is
always the developers/DBAs who have to do things. 

And at least for some brain shapes it is much more convenient to inherit
tables than to (re)factor stuff into several tables to simulate
inheritance using the relational model. 

I still think that inheritance should be enchanced and made compatible
with standards not removed.

> Extending the model adds complexity without adding the
> ability to do things you couldn't easily do before. (This, IMHO, makes
> table inheritance quite inelegant.)

Then explain why SQL99 has included inheritance ?

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

  29. Re: Why is MySQL more chosen over PostgreSQL?

    Jeff Davis <list-pgsql-hackers@empires.org> — 2002-07-30T21:41:02Z

    > 2. I expect that even most PostgreSQL--or even database--experts don't
> have a real understanding of relational theory, anyway. That we still
> have table inheritance shows that. As far as I can tell, there is
> nothing whatsoever that table inheritance does that the relational model
> does not handle; the whole "OO" thing is just another, redundant way of
> doing what we already ought to be able to do within the relational model.
>
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.

Can you point me (someone without a real understanding of relational theory) 
to some good resources that explain the concepts well?

Regards,
	Jeff

  30. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-30T23:35:17Z

    On 31 Jul 2002, Hannu Krosing wrote:

> I would not rush to drop advanced features, as they may be hard to put
> back later.

If they are hard to put back, it's generally because the other code
in the system that relates to it has changed, so you can't just bring
back what is in the old versions in the CVS repository.

But if the code was left in, that meant that someone had to make all of
those integration changes you'd have to make to bring the code back;
it's just they had to make it as they were adding new features and
whatnot. If in the end you decide that the feature you didn't drop isn't
important, you just did a lot of work for nothing. You may also slow
down or stop the implementation of other, more useful features, because
people find that the work to add them isn't worthwhile, due to having to
change too much code.

> If they stay in, even in broken form, then there wont be
> nearly as much patches which make fixing them harder.

Summary: someone always has to do the patches. It's just a question of
whether you *might* do them *if* you decide to bring the feature back,
or whether you *will* do them because the feature is there.

> > What we (or I, anyway) are arguing is that
> > the relational model does everything that table inheritance does, and at
> > least as easily.
>
> The problem is that 'the relational model' does nothing by itself. It is
> always the developers/DBAs who have to do things.

Ok. So "the developer can do what table inheritance does just as easily
in the relational model."

> And at least for some brain shapes it is much more convenient to inherit
> tables than to (re)factor stuff into several tables to simulate
> inheritance using the relational model.

I highly doubt that. Relating two tables to each other via a key, and
joining them together, allows you to do everything that inheritance
allows you to do, but also more. If you have difficulty with keys and
joins, well, you really probably want to stop and fix that problem
before you do more work on a relational database....

> > Extending the model adds complexity without adding the
> > ability to do things you couldn't easily do before. (This, IMHO, makes
> > table inheritance quite inelegant.)
>
> Then explain why SQL99 has included inheritance ?

Becuase SQL has a long, long history of doing things badly. The language
has been non-relational in many ways from the very beginning. But Codd
and Date argue that much better than I do, so I'd prefer you read their
books and respond to those arguments. I can provide references if you
need them.

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

  31. Re: Why is MySQL more chosen over PostgreSQL?

    Matthew T. O'Connor <matthew@zeut.net> — 2002-07-31T01:09:01Z

    On Mon, 2002-07-29 at 08:53, cbbrowne@cbbrowne.com wrote:
> > Just a long standing curiosity?
> e) Inertia.  MySQL got more popular way back when; the reasons may no longer 

f) Win32 Support.  I can download a setup.exe for mysql and have it up
and running quickly on Windows.  I think that native Win32 support will
go a long way toward making Postgres more "popular"

  32. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-31T02:31:11Z

    On Tue, 30 Jul 2002, Jeff Davis wrote:

> Can you point me (someone without a real understanding of relational theory)
> to some good resources that explain the concepts well?

C. J. Date's _An Introduction to Database Systems, Seventh Edition_ is
a fat tome that will give you an extremely good grasp of relational
theory if you take the time to study it. Even just browsing it is well
worthwhile. It has some discussion of "object-oriented" database systems
as well.

In particular (you'll see the relevance of this below) it has an
excellent analysis of the updatability of views.

Date and Darwen's _Foundation for Future Database Systems: the
Third Manifesto_ goes into much more detail about how they feel
object-oriented stuff should happen in relational databases. Appendix E
("Subtables and Supertables") discusses table inheritance. It ends with
this statement:

    To sum up: It looks as if the whole business of a subtable
    inheriting columns from a supertable is nothing but a syntatic
    shorthand--not that there is anything wrong with syntatic
    shorthands in general, of course, but this particular shorthand
    does not seem particularly useful, and in any case it is always
    more than adequately supported by the conventional view mechanism.

(This, BTW, addresses the note someone else made here about the
subtable/supertable thing letting him do one insert instead of two
or three; he just needs to create a view and appropriate rules,
and he'll get exactly the same effect. And maybe that will help
fix his index problems, too....)

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

  33. Re: Why is MySQL more chosen over PostgreSQL?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-07-31T02:43:30Z

    > I highly doubt that. Relating two tables to each other via a key, and
> joining them together, allows you to do everything that inheritance
> allows you to do, but also more. If you have difficulty with keys and
> joins, well, you really probably want to stop and fix that problem
> before you do more work on a relational database....

I'm still not convinced of this.  For example, my friend has a hardware
e-store and every different class of hardware has different properties.  ie
modems have baud and network cards have speed and video cards have ram.  He
simply just has a 'products' table from which he extends
'networkcard_products', etc. with the additional fields.  Easy.

Chris

  34. Re: Why is MySQL more chosen over PostgreSQL?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-07-31T02:44:26Z

    > On Mon, 2002-07-29 at 08:53, cbbrowne@cbbrowne.com wrote:
> > > Just a long standing curiosity?
> > e) Inertia.  MySQL got more popular way back when; the reasons
> may no longer
>
> f) Win32 Support.  I can download a setup.exe for mysql and have it up
> and running quickly on Windows.  I think that native Win32 support will
> go a long way toward making Postgres more "popular"

Speaking of that - wasn't someone going to branch the CVS with a whole lot
of Win32 support stuff?  Jan?

Chris

  35. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-31T03:03:56Z

    On Wed, 31 Jul 2002, Christopher Kings-Lynne wrote:

> > I highly doubt that. Relating two tables to each other via a key, and
> > joining them together, allows you to do everything that inheritance
> > allows you to do, but also more. If you have difficulty with keys and
> > joins, well, you really probably want to stop and fix that problem
> > before you do more work on a relational database....
>
> I'm still not convinced of this.  For example, my friend has a hardware
> e-store and every different class of hardware has different properties.  ie
> modems have baud and network cards have speed and video cards have ram.  He
> simply just has a 'products' table from which he extends
> 'networkcard_products', etc. with the additional fields.  Easy.

And what's the problem with networkcard_products being a separate table
that shares a key with the products table?

    CREATE TABLE products (product_id int, ...)
    CREATE TABLE networkcard_products_data (product_id int, ...)
    CREATE VIEW networkcard_products AS
	SELECT products.product_id, ...
	FROM products
	JOINT networkcard_products_data USING (product_id)

What functionality does table inheritance offer that this traditional
relational method of doing things doesn't?

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

  36. Re: Why is MySQL more chosen over PostgreSQL?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T03:11:14Z

    Curt Sampson wrote:
> On Wed, 31 Jul 2002, Christopher Kings-Lynne wrote:
> 
> > > I highly doubt that. Relating two tables to each other via a key, and
> > > joining them together, allows you to do everything that inheritance
> > > allows you to do, but also more. If you have difficulty with keys and
> > > joins, well, you really probably want to stop and fix that problem
> > > before you do more work on a relational database....
> >
> > I'm still not convinced of this.  For example, my friend has a hardware
> > e-store and every different class of hardware has different properties.  ie
> > modems have baud and network cards have speed and video cards have ram.  He
> > simply just has a 'products' table from which he extends
> > 'networkcard_products', etc. with the additional fields.  Easy.
> 
> And what's the problem with networkcard_products being a separate table
> that shares a key with the products table?
> 
>     CREATE TABLE products (product_id int, ...)
>     CREATE TABLE networkcard_products_data (product_id int, ...)
>     CREATE VIEW networkcard_products AS
> 	SELECT products.product_id, ...
> 	FROM products
> 	JOINT networkcard_products_data USING (product_id)
> 
> What functionality does table inheritance offer that this traditional
> relational method of doing things doesn't?

You can add children without modifying your code.  It is classic C++
inheritance;  parent table accesses work with the new child tables
automatically.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  37. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-31T03:14:36Z

    On Tue, 30 Jul 2002, Bruce Momjian wrote:

> You can add children without modifying your code.  It is classic C++
> inheritance;  parent table accesses work with the new child tables
> automatically.

I don't see how my method doesn't do this as well. What code do you have
to modify in the relational way of doing things that you don't in this
inheritance way?

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

  38. Re: Why is MySQL more chosen over PostgreSQL?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T03:15:33Z

    Curt Sampson wrote:
> On Tue, 30 Jul 2002, Bruce Momjian wrote:
> 
> > You can add children without modifying your code.  It is classic C++
> > inheritance;  parent table accesses work with the new child tables
> > automatically.
> 
> I don't see how my method doesn't do this as well. What code do you have
> to modify in the relational way of doing things that you don't in this
> inheritance way?

Seems like you have to modify your views to handle this, at least in the
example you just posted, right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  39. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-07-31T04:06:23Z

    On Wed, 2002-07-31 at 04:35, Curt Sampson wrote:
> On 31 Jul 2002, Hannu Krosing wrote:
> 
> > I would not rush to drop advanced features, as they may be hard to put
> > back later.
> 
> If they are hard to put back, it's generally because the other code
> in the system that relates to it has changed, so you can't just bring
> back what is in the old versions in the CVS repository.
> 
> But if the code was left in, that meant that someone had to make all of
> those integration changes you'd have to make to bring the code back;
> it's just they had to make it as they were adding new features and
> whatnot. If in the end you decide that the feature you didn't drop isn't
> important, you just did a lot of work for nothing. You may also slow
> down or stop the implementation of other, more useful features, because
> people find that the work to add them isn't worthwhile, due to having to
> change too much code.
> 
> > If they stay in, even in broken form, then there wont be
> > nearly as much patches which make fixing them harder.
> 
> Summary: someone always has to do the patches. It's just a question of
> whether you *might* do them *if* you decide to bring the feature back,
> or whether you *will* do them because the feature is there.

Often there are more than one way to do things. And the feature being
there may prompt the implementor to choose in favor of a way which does
not rule out the feature. It does not neccessarily make that harder for
new features, though it may.

> > > What we (or I, anyway) are arguing is that
> > > the relational model does everything that table inheritance does, and at
> > > least as easily.
> >
> > The problem is that 'the relational model' does nothing by itself. It is
> > always the developers/DBAs who have to do things.
> 
> Ok. So "the developer can do what table inheritance does just as easily
> in the relational model."
> 
> > And at least for some brain shapes it is much more convenient to inherit
> > tables than to (re)factor stuff into several tables to simulate
> > inheritance using the relational model.
> 
> I highly doubt that.

I said it is personal ;) Some other brain shapes are more fit to working
in relational model, even when writing front-ends in C++ or java.

> Relating two tables to each other via a key, and joining them together,

It gets more complicated fast when inheritance hierarchies get deeper,
and some info is often lost (or at least not explicitly visible from
schema). That's why advanced modeling tools allow you to model things as
inheritance hierarchies even when they have to map it to relational
model for databases which do not support inheritance.

An it is often easier to map OO languages to OOR database when you dont
have to change your mindset when going through the interface.

> allows you to do everything that inheritance allows you to do,
> but also more.

* you can do anything (and more ;) that DOMAINs do without domains.
* And you can do anything and more that can be done in C++ in C.
* And you can do anything sequences do and more without explicit syntax
  for sequences (except making them live outside of transactions,
  but this is mainly a performance hack and sequences are outside
  of relational theory anyway ;)
* And as I already mentioned, you can compute anything on
  a Turing Machine (I doubt you can compute more, but it is not
  entirely impossible as it has to work 'more' ;)

> If you have difficulty with keys and
> joins, well, you really probably want to stop and fix that problem
> before you do more work on a relational database....

It is of course beneficial to make joins faster, but it is often easier
to do for more specific cases, when the user has implicitly stated what
kind of a join he means.

One example of that is the existance of contrib/intagg which is meant to
make the relational method usable (performance-wise) for a class of
problems where _pure_ relational way falls down. 

> > > Extending the model adds complexity without adding the
> > > ability to do things you couldn't easily do before. (This, IMHO, makes
> > > table inheritance quite inelegant.)
> >
> > Then explain why SQL99 has included inheritance ?
> 
> Becuase SQL has a long, long history of doing things badly.

Or to rephrase it: SQL has a long, long history of doing things (though
badly)

> The language has been non-relational in many ways from the very beginning.

SQL has had pressure to be usable for a broad range of real-world
problems from the beginning, which theory has not.

> But Codd and Date argue that much better than I do, so I'd prefer you
> read their books and respond to those arguments. I can provide
> references if you need them.

In theory theory and practice are the same, in practice they are often
not nearly so.

From your reference:

|Date and Darwen's _Foundation for Future Database Systems: the
|Third Manifesto_ goes into much more detail about how they feel
|object-oriented stuff should happen in relational databases. Appendix E
|("Subtables and Supertables") discusses table inheritance. It ends with
|this statement:
|
|    To sum up: It looks as if the whole business of a subtable
|    inheriting columns from a supertable is nothing but a syntatic
|    shorthand--not that there is anything wrong with syntatic
|    shorthands in general, of course, but this particular shorthand
|    does not seem particularly useful, and in any case it is always
|    more than adequately supported by the conventional view mechanism.

Which is clearly not true in PostgreSQL's case, as adequate support
would IMHO mean that the rules for insert/update/delete were generated
automatically for views as they are for select.

Of course we could go the other way and remove support for VIEW's as
they can be done using a table and a ON SELECT DO INSTEAD rule. 
Actually this is how they are done.

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

  40. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-31T04:31:15Z

    On Tue, 30 Jul 2002, Bruce Momjian wrote:

> Curt Sampson wrote:
> > On Tue, 30 Jul 2002, Bruce Momjian wrote:
> >
> > > You can add children without modifying your code.  It is classic C++
> > > inheritance;  parent table accesses work with the new child tables
> > > automatically.
> >
> > I don't see how my method doesn't do this as well. What code do you have
> > to modify in the relational way of doing things that you don't in this
> > inheritance way?
>
> Seems like you have to modify your views to handle this, at least in the
> example you just posted, right?

You need to create a new view for the "child" table, yeah. But you had to
create a child table anyway. But all the previously existing code you had
continues to work unchanged.

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

  41. Re: Why is MySQL more chosen over PostgreSQL?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T06:17:50Z

    Hannu Krosing <hannu@tm.ee> writes:
> Of course we could go the other way and remove support for VIEW's as
> they can be done using a table and a ON SELECT DO INSTEAD rule. 

Two points for Hannu ;-)

Seriously, this entire thread seems a waste of bandwidth to me.
Inheritance as a feature isn't costing us anything very noticeable
to maintain, and so I see no credible argument for expending the
effort to rip it out --- even if I placed zero value on the annoyance
factor for users who are depending on it.  (Which I surely don't.)

It's true that upgrading inheritance to handle features like cross-table
uniqueness constraints or cross-table foreign keys is not trivial.  But
I don't know of any way to handle those problems in bog-standard SQL92
either.  The fact that we don't have a solution to those issues at
present doesn't strike me as a reason to rip out the functionality we
do have.

In short: give it a rest.  There's lots of things we could be more
productively arguing about.  Think about which type conversions should
be implicit, if you need a topic ...

			regards, tom lane

  42. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-07-31T06:23:43Z

    On 31 Jul 2002, Hannu Krosing wrote:

> An it is often easier to map OO languages to OOR database when you dont
> have to change your mindset when going through the interface.

But you have to anyway! Adding this inheritance does not remove the
relational model; it's still there right in front of you, and you still
have to use it. You have simply added another model to keep track of as
well.

And I've done a fair amount of OO lanugage <-> relational database
interfacing, and the problems I've encountered are not helped by
table inheritance. In fact, table inheritance has been irrelevant.
But maybe I missed some problems.

> > allows you to do everything that inheritance allows you to do,
> > but also more.
>
> * And you can do anything and more that can be done in C++ in C.

Ok, this is really starting to annoy me. Can we stop with this argument,
since you *know* it is attacking a straw man?

> > If you have difficulty with keys and
> > joins, well, you really probably want to stop and fix that problem
> > before you do more work on a relational database....
>
> It is of course beneficial to make joins faster, but it is often easier
> to do for more specific cases, when the user has implicitly stated what
> kind of a join he means.

No, my point is, you simply cannot do good work at all on a relational
DB without understanding keys and joins. It does not matter whether
table inheritance is present or not. Therefore everybody effectivly
using a database is going to have enough knowledge to do this stuff
without table inheritance.

> One example of that is the existance of contrib/intagg which is meant to
> make the relational method usable (performance-wise) for a class of
> problems where _pure_ relational way falls down.

You seem to be confusing the relational model with a particular
implementation of a relational database. The relational model handles
this just fine, because the relational model doesn't have performance.

This particular contrib module does not change anything at all
about the relational model as implemented in postgres. It just
provides a particular performance work-around. Note also that the
performance problem can also be fixed in other ways; under MS-SQL server
I'd simply use a clustered index on the one-to-many table.

In fact, given that contrib/intagg works only with relatively static
data, I'm not sure why you'd use it instead of just using the
CLUSTER command once in a while.

> SQL has had pressure to be usable for a broad range of real-world
> problems from the beginning, which theory has not.

SQL is actually much less usable for many real-world problems than
a proper relational language is. But as I said, read Date, and then
argue; I'm not going to spend days rewriting his books here.

> |Date and Darwen's _Foundation for Future Database Systems: the
> |Third Manifesto_ goes into much more detail about how they feel
> |object-oriented stuff should happen in relational databases. Appendix E
> |("Subtables and Supertables") discusses table inheritance. It ends with
> |this statement:
> |
> |    To sum up: It looks as if the whole business of a subtable
> |    inheriting columns from a supertable is nothing but a syntatic
> |    shorthand--not that there is anything wrong with syntatic
> |    shorthands in general, of course, but this particular shorthand
> |    does not seem particularly useful, and in any case it is always
> |    more than adequately supported by the conventional view mechanism.
>
> Which is clearly not true in PostgreSQL's case, as adequate support
> would IMHO mean that the rules for insert/update/delete were generated
> automatically for views as they are for select.

It certainly would be nice if we did that.

> Of course we could go the other way and remove support for VIEW's as
> they can be done using a table and a ON SELECT DO INSTEAD rule.
> Actually this is how they are done.

*Sigh*. You seem to be unable to distinguish between changes to
the conceptual model of a system and changes to implementation
details.

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

  43. Re: Why is MySQL more chosen over PostgreSQL?

    Sander Steffann <steffann@nederland.net> — 2002-07-31T08:35:12Z

    Hi

> And what's the problem with networkcard_products being a separate table
> that shares a key with the products table?
>
>     CREATE TABLE products (product_id int, ...)
>     CREATE TABLE networkcard_products_data (product_id int, ...)
>     CREATE VIEW networkcard_products AS
> SELECT products.product_id, ...
> FROM products
> JOINT networkcard_products_data USING (product_id)
>
> What functionality does table inheritance offer that this traditional
> relational method of doing things doesn't?

Well, if you also have soundcard_products, in your example you could have a
product which is both a networkcard AND a soundcard. No way to restrict that
a product can be only one 'subclass' at a time... If you can make that
restriction using the relational model, you can do the same as with
subclasses. But afaict that is very hard to do...

Sander.

  44. Re: Why is MySQL more chosen over PostgreSQL?

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-01T18:27:56Z

    On Tue, 2002-07-30 at 14:54, Hannu Krosing wrote:
> On Tue, 2002-07-30 at 16:00, Curt Sampson wrote:
> > On 30 Jul 2002, Hannu Krosing wrote:
> > 
> > > On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
> > >
> > > > Bruce Momjian:
> > > > > It causes too much complexity in other parts of the system.
> > > >
> > > > That's one reason.
> > >
> > > Seems like somewhat valid reason. But still not enough to do a lot of
> > > work _and_ annoy a lot of existing users :)
> > 
> > It's almost unquestionably more work to maintain than to drop. Dropping
> > support for it is a one-time operation. Maintaining it is an ongoing
> > expense.
> 
> I would not rush to drop advanced features, as they may be hard to put
> back later. If they stay in, even in broken form, then there wont be
> nearly as much patches which make fixing them harder.

I seem to find this argument a lot on the list here.  For some reason,
many of the developers are under the impression that even if code is
never touched, it has a very high level of effort to keep it in the code
base.  That is, of course, completely untrue.  Now then, I'm not saying
that something as central as the topic at hand has a zero maintenance
cost associated with it, especially if it's constantly being run into by
the developers, but I do see it used WAY to often here for it to be
applicable in every case.

From what I can tell, in many cases, when one developer on the list
doesn't want to maintain or sees little value in a feature, it suddenly
seems to have a high price associated with it.  We need to be sure we're
making the distinction between, "I don't care to maintain this", and,
"maintaining this code is prohibitively high given it's feature
return...because...".   In other words, I find this argument used often
here will little to nothing used in context which would quantify it. 
Worse yet, it generally goes unchallenged and unquestioned.

> 
> I'm afraid that we have already dropped too much. 
> 
> For example we dropped time travel, but recent versions of Oracle now
> have some form of it, usable mostly for recovering accidentally deleted
> (and committed rows), although it is much harder to implement it using
> logs than using MVCC.

I must admit, I never understood this myself but I'm sure I'm ignorant
of the details.

> > That's a straw man argument.
> 
> Actually it was meant to be 'one straw man against another straw man 
> argument' ;)

Was clear to me!  I thought you made the point rather well.

> 
> > What we (or I, anyway) are arguing is that
> > the relational model does everything that table inheritance does, and at
> > least as easily.
> 
> The problem is that 'the relational model' does nothing by itself. It is
> always the developers/DBAs who have to do things. 
> 
> And at least for some brain shapes it is much more convenient to inherit
> tables than to (re)factor stuff into several tables to simulate
> inheritance using the relational model. 

Agreed.  It's important to remember, there are some cases where the
conceptual implications can allow for more freedom in implementation. 
This is the point that was being made with the "pure C" comment.  Sure,
I can do pretty much anything in asm, but that approach doesn't suddenly
invalidate every other way/language/concept/idiom to trying to
accomplish as given task.

Simply put, much of the power you get from any tool is often the
flexibility of a given tool to address a problem domain in many
different ways rather than just one.  Just because it doesn't fit your
paradigm doesn't mean it doesn't fit nicely into someone else's.

> 
> I still think that inheritance should be enchanced and made compatible
> with standards not removed.

I completely agree with that!

> 
> > Extending the model adds complexity without adding the
> > ability to do things you couldn't easily do before. (This, IMHO, makes
> > table inheritance quite inelegant.)
> 
> Then explain why SQL99 has included inheritance ?
> 

Yes please.  I'm very interested in hearing a rebuttal to this one.

Greg

  45. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-02T03:39:18Z

    On 1 Aug 2002, Greg Copeland wrote:

> For some reason,
> many of the developers are under the impression that even if code is
> never touched, it has a very high level of effort to keep it in the code
> base.  That is, of course, completely untrue.

Where does this "of course" come from? I've been programming for quite a
while now, and in my experience every line of code costs you something
to maintain. As long as there's any interaction with other parts of
the system, you have to test it regularly, even if you don't need to
directly change it.

That said, if you've been doing regular work on postgres code base and you
say that it's cheap to maintain, I'll accept that.

> > Then explain why SQL99 has included inheritance ?
>
> Yes please.  I'm very interested in hearing a rebuttal to this one.

Because SQL99 is non-relational in many ways, so I guess they
figured making it non-relational in one more way can't hurt.

I mean come on, this is a language which started out not even
relationally complete!

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

  46. Re: Why is MySQL more chosen over PostgreSQL?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-02T04:30:38Z

    Greg Copeland <greg@CopelandConsulting.Net> writes:
> I seem to find this argument a lot on the list here.  For some reason,
> many of the developers are under the impression that even if code is
> never touched, it has a very high level of effort to keep it in the code
> base.  That is, of course, completely untrue.

FWIW, I did not notice any of the core developers making that case.

As far as I'm concerned, any patch to remove inheritance will be
rejected out of hand.  It's not costing us anything significant to
maintain as-is, and there are a goodly number of people using it.
Extending it (eg, making cross-table indexes to support inherited
uniqueness constraints) is a different kettle of fish --- but until
someone steps up to the plate with an implementation proposal, it's
rather futile to speculate what that might cost.  In the meantime,
the lack of any such plan is no argument for removing the functionality
we do have.

			regards, tom lane

  47. Re: Why is MySQL more chosen over PostgreSQL?

    Marc G. Fournier <scrappy@hub.org> — 2002-08-02T06:52:21Z

    On Fri, 2 Aug 2002, Tom Lane wrote:

> Greg Copeland <greg@CopelandConsulting.Net> writes:
> > I seem to find this argument a lot on the list here.  For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
>
> FWIW, I did not notice any of the core developers making that case.
>
> As far as I'm concerned, any patch to remove inheritance will be
> rejected out of hand.  It's not costing us anything significant to
> maintain as-is, and there are a goodly number of people using it.
> Extending it (eg, making cross-table indexes to support inherited
> uniqueness constraints) is a different kettle of fish --- but until
> someone steps up to the plate with an implementation proposal, it's
> rather futile to speculate what that might cost.  In the meantime,
> the lack of any such plan is no argument for removing the functionality
> we do have.

Definitely concur ... in fact, didn't someone recently do some work to
improve our inheritance code, as it wasn't 'object enough' for them?
Isn't inheritance kinda one of those things that is required in order to
be consider ourselves ORBDMS, which we do classify our selves as being?

  48. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-02T06:55:57Z

    On Fri, 2 Aug 2002, Marc G. Fournier wrote:

> Isn't inheritance kinda one of those things that is required in order to
> be consider ourselves ORBDMS, which we do classify our selves as being?

Well, it depends on what you call an ORDBMS. By the standards of
Date and Darwen in _The Third Manifesto_, table inheritance is not
required and is in fact discouraged as a feature trivially implemented
with views, foreign keys and constraints. (Though that does not
mean that posgresql currently has an implementation of these that
will make it trivial.)

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

  49. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-02T10:15:40Z

    On 2 Aug 2002, Hannu Krosing wrote:

> Is _The Third Manifesto_ available online ?

No. It's a book, and not a terribly small one, either.

    http://www.amazon.com/exec/obidos/ASIN/0201709287/

> Could you brief me why do they discourage a syntactical frontent to a
> feature that is trivially implemented ?

What's the point of adding it? It's just one more thing to learn.

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

  50. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-02T10:23:04Z

    On 2 Aug 2002, Hannu Krosing wrote:

> Could you point me to some pure relational languages ?
> Preferrably not pure academic at the same time ;)

The QUEL and PostQUEL languages used in Ingres and the old Postgres were
rather more "relational" than SQL.

> BTW, what other parts of SQL do you consider non-relational (and thus
> candidates for dropping) ?

I have nothing particular in mind right now. Also, note that merely
being non-relational does not make a language element a candidate
for dropping.  If lots of other databases implement a feature, it
would be silly to destroy compatability for the sake of 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

  51. Re: Why is MySQL more chosen over PostgreSQL?

    D'Arcy Cain <darcy@druid.net> — 2002-08-02T10:46:32Z

    * Hannu Krosing <hannu@tm.ee> [020802 06:32]:
> Your argument can as well be used against VIEWs - whats the point of
> having them, when they can trivially be implemented using ON XXX DO
> INSTEAD rules.

Well, at least on PostgreSQL it makes a difference.  We allow views to
have permissions granted to them independent of the underlying tables.
It a nice , distinguishing feature.  What other database allows you
to grant one person access to a subset of the colums of a table as
well as a subset of the rows?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

  52. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-02T11:07:56Z

    On Fri, 2002-08-02 at 08:55, Curt Sampson wrote:
> On Fri, 2 Aug 2002, Marc G. Fournier wrote:
> 
> > Isn't inheritance kinda one of those things that is required in order to
> > be consider ourselves ORBDMS, which we do classify our selves as being?
> 
> Well, it depends on what you call an ORDBMS. By the standards of
> Date and Darwen in _The Third Manifesto_,

Is _The Third Manifesto_ available online ?

> table inheritance is not
> required and is in fact discouraged as a feature trivially implemented
> with views, foreign keys and constraints. (Though that does not
> mean that posgresql currently has an implementation of these that
> will make it trivial.)

Could you brief me why do they discourage a syntactical frontent to a
feature that is trivially implemented ? 

If it is just views. foreign keys and constraints anyway, it should not
add compexity to implementation.

OTOH, stating explicitly what you mean, can give the system extra hints
for making good optimisation decisions.

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

  53. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-02T11:10:47Z

    On Fri, 2002-08-02 at 05:39, Curt Sampson wrote:
> Because SQL99 is non-relational in many ways, so I guess they
> figured making it non-relational in one more way can't hurt.
> 
> I mean come on, this is a language which started out not even
> relationally complete!

Could you point me to some pure relational languages ?

Preferrably not pure academic at the same time ;)

BTW, what other parts of SQL do you consider non-relational (and thus
candidates for dropping) ?

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

  54. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-02T11:34:21Z

    On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> On 2 Aug 2002, Hannu Krosing wrote:
> 
> > Is _The Third Manifesto_ available online ?
> 
> No. It's a book, and not a terribly small one, either.
> 
>     http://www.amazon.com/exec/obidos/ASIN/0201709287/
> 
> > Could you brief me why do they discourage a syntactical frontent to a
> > feature that is trivially implemented ?
> 
> What's the point of adding it? It's just one more thing to learn.

You don't have to learn it if you don't want to. But once you do, you
have a higher level way of expressing a whole class of models.

Your argument can as well be used against VIEWs - whats the point of
having them, when they can trivially be implemented using ON XXX DO
INSTEAD rules.

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

  55. Third Manifesto

    Your Name <cbbrowne@cbbrowne.com> — 2002-08-02T13:55:07Z

    > On Fri, 2002-08-02 at 08:55, Curt Sampson wrote:
> > On Fri, 2 Aug 2002, Marc G. Fournier wrote:
> > 
> > > Isn't inheritance kinda one of those things that is required in order to
> > > be consider ourselves ORBDMS, which we do classify our selves as being?
> > 
> > Well, it depends on what you call an ORDBMS. By the standards of
> > Date and Darwen in _The Third Manifesto_,
> 
> Is _The Third Manifesto_ available online ?

The full book is not.

An earlier version of the work is available as: http://www.acm.org/sigmod/recor
d/issues/9503/manifesto.ps

It's actually an easier read than the full book.
--
(concatenate 'string "cbbrowne" "@cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/finances.html
"very few people approach me in real life and insist on proving they
are drooling idiots."  -- Erik Naggum, comp.lang.lisp

  56. Re: Why is MySQL more chosen over PostgreSQL?

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-02T14:14:46Z

    On Thu, 2002-08-01 at 23:30, Tom Lane wrote:
> Greg Copeland <greg@CopelandConsulting.Net> writes:
> > I seem to find this argument a lot on the list here.  For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
> 
> FWIW, I did not notice any of the core developers making that case.
> 

I've seen it used a lot.  In many cases, it's asserted with nothing to
support it other than the fact that they are a core developer, however,
these assertions are often given against unspecified and undeveloped
code, so, it makes such an assertion invalid.  

Greg

  57. Re: Why is MySQL more chosen over PostgreSQL?

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-02T14:26:10Z

    On Thu, 2002-08-01 at 22:39, Curt Sampson wrote:
> On 1 Aug 2002, Greg Copeland wrote:
> 
> > For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
> 
> Where does this "of course" come from? I've been programming for quite a
> while now, and in my experience every line of code costs you something
> to maintain.

Please re-read my statement.  Your assertion and my statement are by no
means exclusionary.  "Of course" was correctly used and does correctly
apply, however, it doesn't appear it was correctly comprehended by you
as it applied in context.  I agree with your statement of, "...every
line of code costs you something to maintain..." which in no way, shape,
or form contradicts my statement of, "...it has a very high level of
effort...of course not...".  Fact is, if code which is never touched and
requires a very level of effort to maintain, chances are you screwed up
somewhere.

Hopefully we can agree that "...costs you something..." does not have to
mean, "...very high level of effort..."

 As long as there's any interaction with other parts of
> the system, you have to test it regularly, even if you don't need to
> directly change it.

No one said otherwise.  Perhaps you were replying to someone else?!  :)

> 
> That said, if you've been doing regular work on postgres code base and you
> say that it's cheap to maintain, I'll accept that.

Please re-read my statement.  In my mind, this was implicately
understood from the statement I made.

Shesh...sure hope I remembered to dot all my "i's"...

Greg

  58. Re: Why is MySQL more chosen over PostgreSQL?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-02T14:35:37Z

    Greg Copeland <greg@CopelandConsulting.Net> writes:
> On Thu, 2002-08-01 at 23:30, Tom Lane wrote:
>> FWIW, I did not notice any of the core developers making that case.

> I've seen it used a lot.

Perhaps my meaning wasn't clear: I meant that no one who's familiar
with the code base has made that argument against inheritance.  It
doesn't impact enough of the code to be a maintenance problem.  There
is quite a bit of inheritance code in tablecmds.c, and one or two
other files, but overall it's a very small issue.

			regards, tom lane

  59. Re: Why is MySQL more chosen over PostgreSQL?

    Jeff Davis <list-pgsql-hackers@empires.org> — 2002-08-02T17:53:30Z

    > Well, if you also have soundcard_products, in your example you could have a
> product which is both a networkcard AND a soundcard. No way to restrict
> that a product can be only one 'subclass' at a time... If you can make that
> restriction using the relational model, you can do the same as with
> subclasses. But afaict that is very hard to do...
>

Perhaps I'm mistaken, but it looks to me as if the relational model still 
holds quite cleanly. 

CREATE TABLE products (
id int4 primary key,
name text );

CREATE TABLE soundcard (
prod_id int4 REFERENCES products(id),
some_feature BOOLEAN);

CREATE VIEW soundcard_v AS SELECT * FROM products, soundcard WHERE products.id 
= soundcard.prod_id;

CREATE TABLE networkcard (
prod_id int4 REFERENCES products(id),
hundred_base_t BOOLEAN);

CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE 
products.id = networkcard.prod_id;

Now, to get the networkcard/soundcard combos, you just need to do:
SELECT * FROM soundcard_v, networkcard_v WHERE soundcard_v.id = 
networkcard_v.id;

For what it's worth, I didn't make any mistakes writing it up the first time. 
It most certainly "fits my brain" well and seems simple and clean.

I am not advocating that we remove inheritance, but I (so far) agree with Curt 
that it's pretty useless.

Regards,
	Jeff

  60. Re: Why is MySQL more chosen over PostgreSQL?

    Rod Taylor <rbt@zort.ca> — 2002-08-02T18:50:08Z

    On Fri, 2002-08-02 at 13:53, Jeff Davis wrote:
> > Well, if you also have soundcard_products, in your example you could have a
> > product which is both a networkcard AND a soundcard. No way to restrict
> > that a product can be only one 'subclass' at a time... If you can make that
> > restriction using the relational model, you can do the same as with
> > subclasses. But afaict that is very hard to do...
> >
> 
> Perhaps I'm mistaken, but it looks to me as if the relational model still 
> holds quite cleanly. 
> 
> CREATE TABLE products (
> id int4 primary key,
> name text );
> 
> CREATE TABLE soundcard (
> prod_id int4 REFERENCES products(id),
> some_feature BOOLEAN);
> 
> CREATE VIEW soundcard_v AS SELECT * FROM products, soundcard WHERE products.id 
> = soundcard.prod_id;
> 
> CREATE TABLE networkcard (
> prod_id int4 REFERENCES products(id),
> hundred_base_t BOOLEAN);
> 
> CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE 
> products.id = networkcard.prod_id;
> 
> Now, to get the networkcard/soundcard combos, you just need to do:
> SELECT * FROM soundcard_v, networkcard_v WHERE soundcard_v.id = 
> networkcard_v.id;
> 
> For what it's worth, I didn't make any mistakes writing it up the first time. 
> It most certainly "fits my brain" well and seems simple and clean.

Yup, you've basically done it -- but you still need the permissions
lines (soundcard people shouldn't be able to modify networkcard products
-- but rules on the views could accomplish that).

create table product(prod_id int4 primary key);
create table networkcard(hundred_base_t boolean) inherits(product);
create table soundcard(some_feature boolean) inherits(product);
create table something(some_feature integer) inherits(product);

My favorite (and regularly abused):

create table package_deal(package_price) inherits (product, networkcard,
soundcard, something);


Poor examples as noone would make a sellable package that way, but it
shows how it is simply shorter to do.   New 'product' consists of a
networkcard, soundcard, and something -- always.


Nobody is saying that:

ESC:%s/aba/wo/g

is a real easy way to know to replace all occurrences of 'aba' with
'wo', and there are lots of other ways of doing it -- but if you happen
to know it, then it certainly makes life easier but is not a very
portable command set :)


Views don't do much else but make life easier.  Putting the SQL into the
original queries is just as effective and slightly lower overhead.

Inheritance for me makes life a little bit easier in certain places. 
It's also easier for the programmers to follow than a wackload of views
and double inserts.

  61. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-03T11:32:10Z

    On 2 Aug 2002, Hannu Krosing wrote:

> On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> > On 2 Aug 2002, Hannu Krosing wrote:
> >
> > > Could you brief me why do they discourage a syntactical frontent to a
> > > feature that is trivially implemented ?
> >
> > What's the point of adding it? It's just one more thing to learn.
>
> You don't have to learn it if you don't want to. But once you do, you
> have a higher level way of expressing a whole class of models.

Perhaps this is the problem. I disagree that it's  a "higher" level.

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

  62. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-03T13:19:30Z

    On Sat, 2002-08-03 at 16:32, Curt Sampson wrote:
> On 2 Aug 2002, Hannu Krosing wrote:
> 
> > On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> > > On 2 Aug 2002, Hannu Krosing wrote:
> > >
> > > > Could you brief me why do they discourage a syntactical frontent to a
> > > > feature that is trivially implemented ?
> > >
> > > What's the point of adding it? It's just one more thing to learn.
> >
> > You don't have to learn it if you don't want to. But once you do, you
> > have a higher level way of expressing a whole class of models.
> 
> Perhaps this is the problem. I disagree that it's  a "higher" level.

I don't mean "morally higher" ;)

Just more concise and easier to grasp, same as VIEW vs. TABLE + ON xxx
DO INSTEAD rules.

With INSTEAD rules you can do more than a VIEW does, but when all you
want is a VIEW, then it is easier to define a VIEW, thus VIEW is a
higher level construct than TABLE + ON xxx DO INSTEAD

That is the same way that C is "higher" than ASM and ASM is higher than
writing code directly using hex editor.

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

  63. Re: Why is MySQL more chosen over PostgreSQL?

    Sander Steffann <sander@steffann.nl> — 2002-08-03T17:33:04Z

    Hi,

> > Well, if you also have soundcard_products, in your example you could
have a
> > product which is both a networkcard AND a soundcard. No way to restrict
> > that a product can be only one 'subclass' at a time... If you can make
that
> > restriction using the relational model, you can do the same as with
> > subclasses. But afaict that is very hard to do...
>
> CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE
> products.id = networkcard.prod_id;

I think I was not clear enough... You just demonstrated that it is possible
to have a card that is a soundcard and a networkcard at the same time. The
point I tried to make was that it is difficult to _prevent_ this. Ofcourse I
agree with you that your example fits the relational model perfectly!

I have this problem in a few real-life cases, so if you have a sollution to
this, I would realy appreciate it!
Sander.

  64. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-04T03:49:41Z

    On 3 Aug 2002, Hannu Krosing wrote:

> On Sat, 2002-08-03 at 16:32, Curt Sampson wrote:
> > On 2 Aug 2002, Hannu Krosing wrote:
> >
> > Perhaps this is the problem. I disagree that it's  a "higher" level.
>
> I don't mean "morally higher" ;)
> Just more concise and easier to grasp, same as VIEW vs. TABLE + ON xxx
> DO INSTEAD rules.

That's because we don't do a good job of implementing updatable views.
Views ought to be as fully updatable as possible given the definition,
without having to define rules for doing this. Simple views such as

    CREATE TABLE tab1 (
	id	int,
	foo	text
	)
    CREATE TABLE tab2 (
	id	int,
	bar	text
	)
    CREATE VIEW something AS
	SELECT tab1.id, tab1.foo, tab2.bar
	FROM tab1, tab2
	WHERE tab1.id = tab2.id

ought to be completely updatable without any special rules.

For further info see the detailed discussion of this in Date's
database textbook.

> That is the same way that C is "higher" than ASM and ASM is higher than
> writing code directly using hex editor.

No, this is the same way that Smalltalk is "higher" than Lisp.
(I.e., it isn't.)

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

  65. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-07T02:41:54Z

    On Sat, 3 Aug 2002, Sander Steffann wrote:

> I have this problem in a few real-life cases, so if you have a sollution to
> this, I would realy appreciate it!

Add a card_type column to your main table, and insert something
indicating the value of the card type there.

That won't stop you from having entries for the card in both
network_card and sound_card, but one of those entries will be
meaningless extra data.

Of course, this also means you have to go back to the relational
model to select all your network cards. Doing

    SELECT * FROM network_card

may also return (incorrectly inserted) non-network cards, if your
data are not clean, but

    SELECT card.card_id, card.whatever, network_card.*
    FROM card, network_card
    WHERE card.card_id = network_card.card_id
	AND card.type = 'N'

is guaranteed to return correct results. And of course you can just
make that a view called network_card, and the same statement as
you used with the inerhited table will work.

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

  66. Re: Why is MySQL more chosen over PostgreSQL?

    Don Baccus <dhogaza@pacifier.com> — 2002-08-07T03:09:48Z

    Curt Sampson wrote:
> On Sat, 3 Aug 2002, Sander Steffann wrote:
> 
> 
>>I have this problem in a few real-life cases, so if you have a sollution to
>>this, I would realy appreciate it!
> 
> 
> Add a card_type column to your main table, and insert something
> indicating the value of the card type there.
> 
> That won't stop you from having entries for the card in both
> network_card and sound_card, but one of those entries will be
> meaningless extra data.

So again relational theory can solve the problem but at a cost in 
efficiency.

So could a Turing machine.

> Of course, this also means you have to go back to the relational
> model to select all your network cards. Doing
> 
>     SELECT * FROM network_card
> 
> may also return (incorrectly inserted) non-network cards, if your
> data are not clean, but
> 
>     SELECT card.card_id, card.whatever, network_card.*
>     FROM card, network_card
>     WHERE card.card_id = network_card.card_id
> 	AND card.type = 'N'
> 
> is guaranteed to return correct results. And of course you can just
> make that a view called network_card, and the same statement as
> you used with the inerhited table will work.

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.

> 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"?

Of *course* you can do that in an object-oriented one.  Your skills 
aren't unique, nor is your skill level though you act as though you 
think you're in a class of your own.

> Sorry about that. :-)

Me, too.  The relational model is extremely powerful but it's not the 
be-all and end-all of all things.

You still haven't answered my earlier observation that the PG model, 
with all its flaws, can reduce the number of joins required.

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.

I assume you know that because you've demonstrated your brilliance to 
such an extent that I can only assume you've familiarized yourself with 
the actual details of PG's implementation?

I can't imagine you're the kind of mouth-flapper that would do so 
without such basic research, after all.

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?


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

  67. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-07T04:48:51Z

    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?

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

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.

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

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

Given a parent with an ID field as the primary key, and two children
that inherit that field, you can have the same ID in child1 and child2,
resulting in the ID appearing twice in the parent table. In other
words, the PRIMARY KEY constraint on the parent is a lie. If I were
to implement that relationally (though I'm not sure why I'd want to),
I'd just implement the parent as a view of the children, and add
another table to hold the parent-only data. Now the joins under all
circumstances would be exactly the same as in the version implemented
with inheritance, and you'd have the added advantage that there would be
no lies in the database schema. (And I'm sure I've even seen complaints
about this before, and requests for hacks such as cross-table indexes to
get around this.)

If you feel that I'm missing something here, please send me a schema and
queries that you believe that inheritance does more efficiently than any
relational method can in postgres, and I'll implement it relationally
and test it. If it is indeed impossible to implement as efficiently
relationally as it is with inheritance, I will agree with you that, for
the moment, inheritance has some practical uses in postgres. (I'll also
submit a change request to fix the relational stuff so that it can be
implemented as efficiently.)

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.

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

  68. Re: Why is MySQL more chosen over PostgreSQL?

    Don Baccus <dhogaza@pacifier.com> — 2002-08-07T13:06:18Z

    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.

That's rather the point, isn't it?

In the real world, it does.

> The question is how and whether you can express the constratints
> you need to express.

Have I said anything other than this?

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

Because in fact you have advocated removing the OO stuff.

You won't find me suggesting that this feature can't be modelled in 
relational theory.  AFter all I've got something like a quarter million 
lines of code over at OpenACS that proves you can.

However my co-developers and users would've glady accept the decreased 
effort in implementation and cleaner source code that the PG OO 
extensions offer if the implementation had been more complete.

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

Whether or not the view is written in such a way that it doesn't need to 
be rewritten, dropped and recreated when you change the tables that its 
composed of, you *still* need to write that view when you first extend 
your type using the table+view model.

That's what I was referring to above.  You have to write the view and 
get it right (i.e. write the join using the proper key for it and the 
base view you're extending).

Writing extra code, no matter how trivial, increases the odds that a 
mistake will be made.

You also need to write the proper foreign key and primary key 
constraints in the table being used to do the type extension.  Of course 
this is true of PG's current OO implementation  but if it were fixed it 
would be one less chore that the programmer needs to remember.

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

You mean you accidently supported the argument that this approach is, 
perhaps, more error prone?

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

I haven't *made* that argument.   Please stop raising strawmen.

The argument I've made is that even though that you can model PG's OO 
features not just relationally but in real-live warts-and-all SQL92, 
that doesn't mean they're not useful.

We don't need the binary "integer" type, either.  We could just use 
"number".  Yes, operations on "number" are a bit slower and they often 
take more space, but ...

Shall we take a vote :)


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

  69. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-07T14:38:47Z

    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

  70. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-08T01:47:50Z

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

  71. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-08T01:54:45Z

    On Wed, 7 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.
>
> That's rather the point, isn't it?
>
> In the real world, it does.

Well, I think I dealt with this elsewhere in my post by showing
that I can always implement what you did with inheritance just as
efficiently using relational methods, and sometimes more efficiently.

> Because in fact you have advocated removing the OO stuff.

Actually, I'd suggested thinking about removing the OO stuff. Starting
a discussion about the concept is far from "advocating" it. And in fact
I'd backed off the idea of removing it. However, now that it appears to
me that table inheritance actually breaks the relational portion of the
database, I'm considering advocating its removal. (This requires more
discussion, of course.)

> Writing extra code, no matter how trivial, increases the odds that a
> mistake will be made.

Yeah. But using a broken table inheritance model is far more likely to
cause bugs and errors. It certainly did when I tried to figure out what
you were doing using inheritance. Not only did I get it wrong, but I'm
not at all convinced that what you were doing was what you really wanted
to do.

> You mean you accidently supported the argument that this approach is,
> perhaps, more error prone?

No, supported the argument that table inheritance is either
ill-defined, broken, or both.

> The argument I've made is that even though that you can model PG's OO
> features not just relationally but in real-live warts-and-all SQL92,
> that doesn't mean they're not useful.

All right. I disagree with that, too. I think that they are not
only not useful, but harmful.

> We don't need the binary "integer" type, either.  We could just use
> "number".  Yes, operations on "number" are a bit slower and they often
> take more space, but ...
>
> Shall we take a vote :)

If you like. I vote we keep the integer type. Any other questions?

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

  72. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-08T04:01:15Z

    On Thu, 2002-08-08 at 06:47, Curt Sampson wrote:
> 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,

For me they are _not_ two different models but rather one
object-relational model. Same as C++ in _not_ a completely new language
but rather an extension of plain C.

As you seem to like fat books, check out :

"Object Relational Dbms: Tracking the Next Great Wave" by Michael
Stonebraker, Dorothy Moore (Contributor), Paul Brown
ISBN: 1558604529

I'm sure you find the requested arguments against Date there ;)

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

The table inheritance _implementation_ in PG is in fact broken in
several ways, most notably in  not enforcing uniqueness over all
inherited tables and not inheriting other constraints.

But as you often like to emphasize, model and implementation _are_
different things.

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

  73. Re: Why is MySQL more chosen over PostgreSQL?

    Don Baccus <dhogaza@pacifier.com> — 2002-08-08T06:19:13Z

    Curt Sampson wrote:

>>Because in fact you have advocated removing the OO stuff.

> Actually, I'd suggested thinking about removing the OO stuff.

Man, aren't we into splitting hairs?

You actually stated your case quite strongly and indeed if you hadn't, 
the thread would've died long ago.

Whatever.   You're just dick-waving.

Enjoy your life :)

> Starting
> a discussion about the concept is far from "advocating" it. And in fact
> I'd backed off the idea of removing it. However, now that it appears to
> me that table inheritance actually breaks the relational portion of the
> database, I'm considering advocating its removal. (This requires more
> discussion, of course.)

Except apparently you have no life, oh well, not my problem.

>>Writing extra code, no matter how trivial, increases the odds that a
>>mistake will be made.
> 
> 
> Yeah. But using a broken table inheritance model is far more likely to
> cause bugs and errors. It certainly did when I tried to figure out what
> you were doing using inheritance. Not only did I get it wrong, but I'm
> not at all convinced that what you were doing was what you really wanted
> to do.

I wasn't using inheritance.  I didn't post an example.  And all agree 
that PG's model is broken and eventually needs to be fixed.

Three strawmen in one paragraph.

Again, you're dick-waving and further discussion is not useful.

>>You mean you accidently supported the argument that this approach is,
>>perhaps, more error prone?

> No, supported the argument that table inheritance is either
> ill-defined, broken, or both.

Then what you're saying is you've been arguing all this time against it 
without understanding how it works?

Because either

1. If you understood how it worked then you screwed up your more complex 
view-based analogue, therefore supporting the argument that you've shown 
that the mapping is more error prone.

2. Or you screwed up your code because you've been dick-waving without 
bothering to learn the semantics of the PG OO extensions, which doesn't 
really enhance your credibility.

Which is it?  The idiot behind door number one or the pendantic boor 
behind door number two?

>>We don't need the binary "integer" type, either.  We could just use
>>"number".  Yes, operations on "number" are a bit slower and they often
>>take more space, but ...
>>
>>Shall we take a vote :)
> 
> 
> If you like. I vote we keep the integer type. Any other questions?

Sure ... why the inconsistency without explanation?

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

  74. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-08T07:08:16Z

    On Wed, 7 Aug 2002, Don Baccus wrote:

> Whatever.   You're just dick-waving....
> Except apparently you have no life, oh well, not my problem....
> Again, you're dick-waving and further discussion is not useful....
> Which is it?  The idiot behind door number one or the pendantic boor
> behind door number two?

Uh, yeah. If ad hominem attacks win arguments, I guess you win.
I'll let others decide whether the above arguments are a good reason
to keep table inheritance in postgres.

>
> >>We don't need the binary "integer" type, either.  We could just use
> >>"number".  Yes, operations on "number" are a bit slower and they often
> >>take more space, but ...
> >>
> >>Shall we take a vote :)
> >
> > If you like. I vote we keep the integer type. Any other questions?
>
> Sure ... why the inconsistency without explanation?

Personally I don't find it inconsistent that I want to remove something
that's broken and of dubious utility but keep something that works and
is demonstrably useful. It must be something to do with my dick, I
suppose. But I'll admit, your arguments are beyond me. I surrender.

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

  75. Re: Why is MySQL more chosen over PostgreSQL?

    Hannu Krosing <hannu@tm.ee> — 2002-08-08T11:32:17Z

    On Thu, 2002-08-08 at 17:57, Curt Sampson wrote:
> On 8 Aug 2002, Hannu Krosing wrote:
> 
> > For me they are _not_ two different models but rather one
> > object-relational model.
> 
> Well, given that we've already demonstrated two rather different ways
> of saying "the same thing," I think we have two models happening here.
> However, feel free to explain your "object-relational model" in more
> detail, including its advantages over the ordinary relational model.

The main difference (in the inheritance part) is that a relation does
not have one fixed set of fields, but can have any additional fields
added in inherited tables and still be part of to the base table as
well.

...

> > The table inheritance _implementation_ in PG is in fact broken in
> > several ways, most notably in  not enforcing uniqueness over all
> > inherited tables and not inheriting other constraints.
> 
> Right. I'm glad we agree on that.
> 
> > But as you often like to emphasize, model and implementation _are_
> > different things.
> 
> Ok. I won't object too much to the model, but let's get rid of this
> severely broken implementation, unless there are some prospects
> for fixing it. How's that?

Actually I am not against ripping out the current broken implementation,
but not before there has been a new, correct model available for at
least two releses, so that people have had time to switch over.

The inheritance model that SQL99 prescribes is more like java's - single
inheritance (so that you have no way of inheriting two primary keys ;) +
LIKE in table definition (in some ways similar to java interfaces)

I see that this could be implemented quite nicely by storing all the
inherited tables in the same page file, in which case primary key would
almost automatically span child relations and indexes on child relations
become partial indexes on the whole thing. There already is some support
for this present (namely tableoid system field stored in every tuple)

> BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model?  

It is "just" syntactic sugar, just as VIEW is "just" syntactic sugar for
ON SELECT DO INSTEAD rules.

VIEWs are broken too, in the sense that you can't insert into them
without doing some hard work. 

But guess you would rather see VIEWs "fixed" to be insertable and
updatable, rather than ripped out "because the same thing and more" can
be done using RULEs ;)

> Or is it supposed to offer something
> that the relational model doesn't do very simply?

It is supposed to help programmers express structures that they would
describe as inheritance in an ERD diagramm in SQL without having to do
mental gymnastics each time they go from model to schema.

Having a shorter description is on one hand syntactic sugar, on the
other hand shorter.

  76. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-08T12:57:41Z

    On 8 Aug 2002, Hannu Krosing wrote:

> For me they are _not_ two different models but rather one
> object-relational model.

Well, given that we've already demonstrated two rather different ways
of saying "the same thing," I think we have two models happening here.
However, feel free to explain your "object-relational model" in more
detail, including its advantages over the ordinary relational model.

> "Object Relational Dbms: Tracking the Next Great Wave" by Michael
> Stonebraker, Dorothy Moore (Contributor), Paul Brown
> ISBN: 1558604529
>
> I'm sure you find the requested arguments against Date there ;)

Unfortunately, this is a bit hard to order in Japan. So before I go
spend 8000 yen and wait a couple of weeks to get hold of a copy, I'd
be interested in just what is there that would dispute Date's points.
Looking through the index on Amazon.com, it appears that the book
devotes, at the very most, eight pages to table inheritance. What does
it say about it?

> The table inheritance _implementation_ in PG is in fact broken in
> several ways, most notably in  not enforcing uniqueness over all
> inherited tables and not inheriting other constraints.

Right. I'm glad we agree on that.

> But as you often like to emphasize, model and implementation _are_
> different things.

Ok. I won't object too much to the model, but let's get rid of this
severely broken implementation, unless there are some prospects
for fixing it. How's that?

BTW, can someone explain the model for inherited tables here? Is
it really just as described _The Third Manifesto_, trivial syntactic
sugar over the relational model? Or is it supposed to offer something
that the relational model doesn't do very simply? (Not to mention
correctly, in the case of postgres.)

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

  77. Re: Why is MySQL more chosen over PostgreSQL?

    Greg Copeland <greg@copelandconsulting.net> — 2002-08-08T14:31:33Z

    > > The table inheritance _implementation_ in PG is in fact broken in
> > several ways, most notably in  not enforcing uniqueness over all
> > inherited tables and not inheriting other constraints.
> 
> Right. I'm glad we agree on that.
> 
> > But as you often like to emphasize, model and implementation _are_
> > different things.
> 
> Ok. I won't object too much to the model, but let's get rid of this
> severely broken implementation, unless there are some prospects
> for fixing it. How's that?
> 

Wasn't that what was seemingly agreed on by pretty much everyone else on
this thread long ago?  The current implementation is problematic and
that it needs to be fixed.

As far as I can tell, the only difference of opinion here is, you seem
to hold zero value in table inheritance while others do see value.  At
this point in time, can't you guys agree to disagree and leave the
majority of this thread behind us?

> BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model? Or is it supposed to offer something
> that the relational model doesn't do very simply? (Not to mention
> correctly, in the case of postgres.)

I would, however, enjoy seeing the theory portion continued as long as
it were kept at the theoretical level.  After all, I think everyone
agreed that Postgres' implementation is broken.  It doesn't seem like we
need to keep beating that horse.

Any takers?  ;)

Greg

  78. Re: Why is MySQL more chosen over PostgreSQL?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-08T14:40:20Z

    > BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model? Or is it supposed to offer something
> that the relational model doesn't do very simply? (Not to mention
> correctly, in the case of postgres.)

No matter how much you grandstand, we're not getting rid of the
inheritance support.  It's not going to happen.  People are using it.

Chris

  79. Re: Why is MySQL more chosen over PostgreSQL?

    Jordan Henderson <jordan_henders@yahoo.com> — 2002-08-08T14:42:37Z

    Greg,

Well put, I can't agree more.  I think even the horse has gotten up and
left.

I think what would be useful is to discuss the theory part.  When we go down
that path, we should all be referring to a consistent set of references.
There by we can have a common ground from which to talk.  In that spirit, I
would offer up the following references:

- Date has 3, however his most current work is dated 2000, The Third
Manifesto SECOND EDITION.
-There is the work done by Dr Kim, perhaps 'Modern Database Systems, The
Object Model, Interoperability, and Beyond'.
- Silberschatz, Korth, Sudarshan, A book I am sure we have all read,
Database System Concepts - Third Edition.

In any case, we should use the current editions of these books, not
something the author has reconsidered, re-written, and published again.

Jordan Henderson

----- Original Message -----
From: "Greg Copeland" <greg@CopelandConsulting.Net>
To: "Curt Sampson" <cjs@cynic.net>
Cc: "Hannu Krosing" <hannu@tm.ee>; "Don Baccus" <dhogaza@pacifier.com>;
"PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
Sent: Thursday, August 08, 2002 10:31 AM
Subject: Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?

  80. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-09T01:38:38Z

    On 8 Aug 2002, Hannu Krosing wrote:

> The main difference (in the inheritance part) is that a relation does
> not have one fixed set of fields, but can have any additional fields
> added in inherited tables and still be part of to the base table as
> well.

This is trivial to do with a view.

> Actually I am not against ripping out the current broken implementation,
> but not before there has been a new, correct model available for at
> least two releses, so that people have had time to switch over.

So in other words, you want to let people use broken stuff, rather
than switch to another method, currently available, that has all
of the functionality but is not broken. I guess that's an opinion, all right.

> VIEWs are broken too, in the sense that you can't insert into them
> without doing some hard work.

Views are missing functionality. That is rather different from
making other tables lie about what they contain, essentially
destroying the requested data 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

  81. Re: Why is MySQL more chosen over PostgreSQL?

    Don Baccus <dhogaza@pacifier.com> — 2002-08-09T01:46:05Z

    Curt Sampson wrote:
> On 8 Aug 2002, Hannu Krosing wrote:
> 
> 
>>The main difference (in the inheritance part) is that a relation does
>>not have one fixed set of fields, but can have any additional fields
>>added in inherited tables and still be part of to the base table as
>>well.
> 
> 
> This is trivial to do with a view.

And views of this sort are trivial to do using PG's OO extensions.

I think I see a trend in this thread.  Why not give it up, dude?


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

  82. Re: Why is MySQL more chosen over PostgreSQL?

    Curt Sampson <cjs@cynic.net> — 2002-08-09T01:56:02Z

    On Thu, 8 Aug 2002, Don Baccus wrote:

> And views of this sort are trivial to do using PG's OO extensions.

So long as you don't mind them being broken, yeah. But hell, when someone
asks for a unique constraint, they probably don't really mean it, do they?
And what's wrong with multiple records with the same primary key? It's clear
to me now I've been working from the wrong direction; we should leave the OO
stuff and delete the relational stuff from the database instead.

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

  83. Re: Why is MySQL more chosen over PostgreSQL?

    Don Baccus <dhogaza@pacifier.com> — 2002-08-09T02:02:48Z

    Curt Sampson wrote:
> On Thu, 8 Aug 2002, Don Baccus wrote:
> 
> 
>>And views of this sort are trivial to do using PG's OO extensions.
> 
> 
> So long as you don't mind them being broken, yeah. But hell, when someone
> asks for a unique constraint, they probably don't really mean it, do they?

Good grief, we all agree that they're currently broken and need to be 
fixed someday.

Give it up.  You're being a boor.


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

  84. Table Inheritance Discussion

    Curt Sampson <cjs@cynic.net> — 2002-08-11T23:41:38Z

    On Thu, 8 Aug 2002, Jordan Henderson wrote:

> I think what would be useful is to discuss the theory part.

As do I.

> - Date has 3, however his most current work is dated 2000, The Third
> Manifesto SECOND EDITION.

This is actually Date and Darwen.

I think we should also add Date's _An Introduction to Database Systems,
7th Edition_, as it covers some relational stuff in more detail than
than _The Third Manifesto_. For example, it investigates the details of
automatic view updatability, which came up during this discussion, and
which most books just completely cop out on. (For example, _Database
System Concepts_ just points out a couple of problems with view
updatability and says, "Because of problems such as these, modifications
are generally not permitted on view relations, except in limited
cases.")

> - Silberschatz, Korth, Sudarshan, A book I am sure we have all read,
> Database System Concepts - Third Edition.
> ...
> In any case, we should use the current editions of these books, not
> something the author has reconsidered, re-written, and published again.

In that case we ought to use the fourth edition of this book.

Here are some questions I'd like to see people answer or propose
answers to:

    1. What models of table inheritance have been proposed, and how
    do they differ?

    2. What models of table inheritance are actually implemented in
    currently available database systems?

    3. What are the advantages of describing something using table
    inheritance rather than an equivalant relational description?

    4. If you think table inheritance is "object oriented," why do
    you think so.

    5. How ought we to fix the table inheritance in postgres?

The last question comes up because, during the conversation up to this
point, we seem to have implicitly accepted that table inheritance is
an "object-oriented" way of doing things. Thinking further on this,
however, I've decided that it's not in fact object-oriented at all.

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

  85. Re: Table Inheritance Discussion

    Don Baccus <dhogaza@pacifier.com> — 2002-08-11T23:59:48Z

    Curt Sampson wrote:

> The last question comes up because, during the conversation up to this
> point, we seem to have implicitly accepted that table inheritance is
> an "object-oriented" way of doing things. Thinking further on this,
> however, I've decided that it's not in fact object-oriented at all.

It's just type extensibility, really.

As to why, again there's an efficiency argument, as I said earlier some 
joins can be avoided given PG's implementation of this feature:

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(j integer) inherits (foo);
CREATE
dotlrn=# explain select * from bar;
NOTICE:  QUERY PLAN:

Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=8)

EXPLAIN
...

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(i integer references foo primary key, j integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'bar_pkey' for table 'bar'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE
dotlrn=# create view foobar as select foo.*, bar.j from foo, bar;
CREATE

dotlrn=# explain select * from foobar;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8)
   ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=4)
   ->  Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

There's also some error checking (using my inherited example):

dotlrn=# drop table foo;
ERROR:  Relation "bar" inherits from "foo"
dotlrn=#

Which doesn't exist in the view approach in PG at least (I'm unclear on 
standard SQL92 and of course this says nothing about the relational 
model in theory, just PG and perhaps SQL92 in practice).

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

  86. Re: Table Inheritance Discussion

    Curt Sampson <cjs@cynic.net> — 2002-08-12T00:18:16Z

    On Sun, 11 Aug 2002, Don Baccus wrote:

> It's just type extensibility, really.

Yeah.

> As to why, again there's an efficiency argument, as I said earlier some
> joins can be avoided given PG's implementation of this feature:
> [TI and relational examples deleted]

What you gave is not the relational equivalant of the TI case as
implemented in postgres. Modeled correctly, you should be creating
a table for the child, and a view for the parent. Then you will
find that the relational definition uses or avoids joins exactly
where the TI definition does.

> There's also some error checking (using my inherited example):

The relational definition doesn't force the dependency, but as you
can delete and recreate the view at will without data loss, the
amount of safety is the same.

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