Thread: Why is MySQL more chosen over PostgreSQL?
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
> > 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.
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..."
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 morepopular 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> understandingof 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 - Sometimesyou 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
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..."
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 alllight. --XTC
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, Pennsylvania19026
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
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 alllight. --XTC
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
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
> > 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
[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
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
* 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.
[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
> 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.
* 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.
[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
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 :)
Inheritance (was: Re: Why is MySQL more chosen over PostgreSQL?)
From
"Christopher Kings-Lynne"
Date:
> > 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
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
> ... 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
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, Pennsylvania19026
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 alllight. --XTC
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 alllight. --XTC
> 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. I think the fact that the PHP guys _pride_ themselves on having built-in MySQL support is another huge reason. They look at it is an example of what can be achieved with integration. The FreeBSD PHP port, as another example, has 'MySQL support' ticked by default. Not quite so much work is put into PHP's PostgreSQL support as MySQL's, so it's often buggy (tell me about it). Also, the utter lack of knowledge about relational theory and SQL is a factor in both newbies and self-taught developers. For instance, in the last few days I have answered questions like these on PHP Builder: "I use SELECT * FROM table WHERE a = 3. How do I get all rows? Can I put a = ALL or something?" "Why don't my javascript variables work in my SQL statements?" "I have two tables and a referencing ID, and I keep getting rows in my child table that don't match a row in the parent table, what is a query that I can run regularly to remove these problem rows?" ...and so on... Why would someone asking the above questions use anything other than the 'default' PHP database? Chris
> 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
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
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 alllight. --XTC
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"
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
particularshorthand does not seem particularly useful, and in any case it is always more than adequately supported
bythe 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
alllight. --XTC
> 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
> 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
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 VIEWnetworkcard_products ASSELECT products.product_id, ...FROM productsJOINT 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 alllight. --XTC
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, Pennsylvania19026
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 alllight. --XTC
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, Pennsylvania19026
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 alllight. --XTC
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
oftransactions, 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
entirelyimpossible 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
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
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
alllight. --XTC
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
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 alllight. --XTC
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
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?
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 alllight. --XTC
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
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
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 alllight. --XTC
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 alllight. --XTC
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
* 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.
> 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
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
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
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
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.
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
> 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
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.
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 alllight. --XTC
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
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 ASSELECTtab1.id, tab1.foo, tab2.barFROM tab1, tab2WHERE 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 alllight. --XTC
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.
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_idANDcard.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 alllight. --XTC
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
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_pkeyon ih_parent (cost=0.00..4.82 rows=1 width=36) -> Seq Scan on ih_child ih_parent (cost=0.00..22.50rows=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 alllight. --XTC
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
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
On 7 Aug 2002, Hannu Krosing wrote: > > Theory: Sure. But this is much harder to express in a turing machine > > isn't it? > > You got it ;) The claim was that it is easiest to express it using > inheritance, a little harder using pure relational model and much harder > using a Turing machine. Ok. I agree that it's much harder with a turning machine. I do *not* agree that it's harder with the relational model. In fact, since you *must* use the relational model for some things, I argue that it's harder to switch back and forth between the relational and OO models, and understand the effects of each on the other, than it is just to do it in OO form in the first place. In fact, I'd argue at this point, as far as table inheritance goes, we don't even have a real model here. Let's look at a few of the problems. 1. I create a base table with an column with a UNIQUE constraint on it, and two child tables. I can insert the same value into that column into the two child tables, thus violating the unique constraint in the base table. Now how can it be acceptable, in postgres or any other relational database, to have a column declared to contain unique values have non-unique values in it? (Note that this was the source of my error in re-implementing some table-inheritance-modeled stuff here in relational form; I preserved that unique constraint when I should not have.) 2. When you have child1 and child2 tables both inheriting directly from a base table, you can have entries in both child1 and child2 whose component from the base table is the same. What does this mean? Are we supposed to be able to have objects that can simultaneously be both subtypes? Well, I could go on, but just from this you can see that: 1. We appear to have no proper theory even defined for how table inheritance should work. 2. If we did, either postgres is not consistent with it, or the theory itself is in conflict with the relational portion of the database. Whatever way you look at it, it's apparent to me that using table inheritance is dangerous, confusing, and should be avoided if you want to maintain data integrity and a self-consistent view of your data. > > No, I mean set up your database so that a card can be a network_card > > or a sound_card, but not both. > > Why can't you do this using inheritance ? > > create table card(...); > create table network_card(...) inherits(card); > create table sound_card(...) inherits(card); > > should do exactly that. But it doesn't. You can have an entry in network_card and another one in sound_card which share the same primary key in the sound_card table. > in this case wasting a bit of space == having incorrect data. No, it doesn't. Your queries will never return incorrect data; the "unused" records will be ignored. > The possiblity of getting out wrong data always exists if there is > incorrect data in the system. No, you can't put incorrect data into the system. The data about what type of card it is is not in the sound_card or network_card table, but in the card table itself, and thus it can only ever have one value for any card entry. It's impossible for that column to have more than one value, thus impossible for that column to have incorrect data. Now you may argue that, because there's an entry for that card in both network_card and sound_card, that means that the card has two types. But that's just deliberate misinterpretation, because you're getting the type information from the wrong place. You might as well argue that a table holding temperatures is "incorrect data" because someone put them in in degress centigrate, and you're interpreting them as degrees Fahrenheit when you pull them out. > > Sure looks like a join to me. > > But you did not have to write it - it was written, debugged and > optimised by postgres. So? The argument I was replying to stated that his method was more efficient because it didn't use joins. Who wrote the join does not matter; it turns out that inside it all joins happen, and so it's not more efficient. > > But anyway, I realized that some of the joins I've shown are > > unnecessary; I've incorrectly implemented, relationally, the inheritance > > model you've shown. Here's the explanation: > > Which proves that using lower level idioms for describing inheritance is > more error prone. No, it proves that the semantics of table inheritance are confusing, or postgres incorrectly impelements them, or both. This kind of mistake is *exactly* the reason I avoid table inheritance; I couldn't tell just what you were doing! And I still am not convinced that what you were doing was what you wanted to do, especially given that I've seen other complaints in this forum that table inheritance specifically was *not* doing what people wanted it to do (thus the plea for cross-table unique indexes). > I suspect that the fact that this is implemented and general updatable > views are not is due to bigger complexity of doing this for a general > case than for specific "inheritance" case. I'll agree with that. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
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 alllight. --XTC
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
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
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 alllight. --XTC
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 alllight. --XTC
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.
> > 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
> 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
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?
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 alllight. --XTC
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
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 alllight. --XTC
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
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 alllight. --XTC
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) -> SeqScan 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
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 alllight. --XTC