[HACKERS] Table Inheritance Discussion
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Table Inheritance Discussion
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=100 width=8) - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) - Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4) EXPLAIN There's also some error checking (using my inherited example): dotlrn=# drop table foo; ERROR: Relation bar inherits from foo dotlrn=# Which doesn't exist in the view approach in PG at least (I'm unclear on standard SQL92 and of course this says nothing about the relational model in theory, just PG and perhaps SQL92 in practice). -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Table Inheritance Discussion
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
