Thread

1. Re: INHERITS doesn't offer enough functionality

   Oliver Elphick <olly@lfix.co.uk> — 2000-10-18T19:46:34Z

   Alfred Perlstein wrote:
     >* Oliver Elphick <olly@lfix.co.uk> [001018 04:59] wrote:
     >> Do you mean that inheriting tables should share a single index with their
     >> ancestors, or that each descendant should get a separate index on the
     >> same pattern as its ancestors'?  
     >> 
     >> With the former, the inherited index could be used to enforce a primary
     >> key over a whole inheritance hierarchy, and would presumable make it
     >> easier to implement RI against an inheritance hierarchy.  Is this what
     >> you have in mind?
     >
     >Not really, it's more of a convience issue for me, a 'derived table'
     >should inherit the attributes of the 'base table' (including indecies),
     >having an index shared between two tables is an interesting idea but
     >not what I had in mind.
   
   Well then, what will happen if I do
   
    SELECT * FROM table* WHERE inherited_unique_indexed_field = some_value;
   
   would I expect to get back multiple rows?  Are all the separate indexes
   candidates for use in the selection?
   
   I think you are highlighting the fact that we still haven't satisfactorily
   defined the semantics of inheritance in PostgreSQL; is it merely a
   template system or is it something more meaningful?  What inheritance
   specifications are we going to work towards?
   
   
   -- 
   Oliver Elphick                                Oliver.Elphick@lfix.co.uk
   Isle of Wight                              http://www.lfix.co.uk/oliver
   PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
   GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                    ========================================
        "Delight thyself also in the LORD; and he shall give 
         thee the desires of thine heart."          Psalms 37:4
   
   
   
   

2. Re: INHERITS doesn't offer enough functionality

   Chris Bitmead <chris@bitmead.com> — 2000-10-19T07:33:24Z

   It's pretty clear to me that an inherited index should be only one
   index. There may be a case for optional non-inherited indexes (CREATE
   INDEX ON ONLY foobar), but if the index is inherited, it is just one
   index.
   
   At the end of the day though, the reason is only performance. The
   semantics should be the same no matter whether implemented as multiple
   indexes or not. Performance is much better with one index though.(*)
   
   (*) Assuming you use inheritance in the queries, which I have found is
   the most common thing. That's reflected in the 7.1 defaults where
   inheritance is the default.
   
   Oliver Elphick wrote:
   > 
   > Alfred Perlstein wrote:
   >   >* Oliver Elphick <olly@lfix.co.uk> [001018 04:59] wrote:
   >   >> Do you mean that inheriting tables should share a single index with their
   >   >> ancestors, or that each descendant should get a separate index on the
   >   >> same pattern as its ancestors'?
   >   >>
   >   >> With the former, the inherited index could be used to enforce a primary
   >   >> key over a whole inheritance hierarchy, and would presumable make it
   >   >> easier to implement RI against an inheritance hierarchy.  Is this what
   >   >> you have in mind?
   >   >
   >   >Not really, it's more of a convience issue for me, a 'derived table'
   >   >should inherit the attributes of the 'base table' (including indecies),
   >   >having an index shared between two tables is an interesting idea but
   >   >not what I had in mind.
   > 
   > Well then, what will happen if I do
   > 
   >  SELECT * FROM table* WHERE inherited_unique_indexed_field = some_value;
   > 
   > would I expect to get back multiple rows?  Are all the separate indexes
   > candidates for use in the selection?
   > 
   > I think you are highlighting the fact that we still haven't satisfactorily
   > defined the semantics of inheritance in PostgreSQL; is it merely a
   > template system or is it something more meaningful?  What inheritance
   > specifications are we going to work towards?
   > 
   > --
   > Oliver Elphick                                Oliver.Elphick@lfix.co.uk
   > Isle of Wight                              http://www.lfix.co.uk/oliver
   > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
   > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
   >                  ========================================
   >      "Delight thyself also in the LORD; and he shall give
   >       thee the desires of thine heart."          Psalms 37:4
   
   -- 
   Chris Bitmead
   mailto:chris@bitmead.com
   
   

3. Re: INHERITS doesn't offer enough functionality

   Hiroshi Inoue <inoue@tpf.co.jp> — 2000-10-19T09:38:47Z

   
   Chris wrote:
   
   > It's pretty clear to me that an inherited index should be only one
   > index. There may be a case for optional non-inherited indexes (CREATE
   > INDEX ON ONLY foobar), but if the index is inherited, it is just one
   > index.
   >
   > At the end of the day though, the reason is only performance. The
   > semantics should be the same no matter whether implemented as multiple
   > indexes or not. Performance is much better with one index though.(*)
   >
   
   Is it true ?
   How to guarantee the uniqueness using multiple indexes ?
   
   Regards.
   Hiroshi Inoue
   
   
   

4. Re: INHERITS doesn't offer enough functionality

   Chris Bitmead <chris@bitmead.com> — 2000-10-19T11:58:14Z

   Hiroshi Inoue wrote:
   
   > > At the end of the day though, the reason is only performance. The
   > > semantics should be the same no matter whether implemented as multiple
   > > indexes or not. Performance is much better with one index though.(*)
   > >
   > 
   > Is it true ?
   > How to guarantee the uniqueness using multiple indexes ?
   
   Well you'd have to check every index in the hierarchy. As I said,
   inefficient.
   
   

5. Re: INHERITS doesn't offer enough functionality

   Marten Feldtmann <m.feldtmann@t-online.de> — 2000-10-19T17:48:50Z

   
   Hiroshi Inoue schrieb:
   > 
   > Chris wrote:
   > 
   > > It's pretty clear to me that an inherited index should be only one
   > > index. There may be a case for optional non-inherited indexes (CREATE
   > > INDEX ON ONLY foobar), but if the index is inherited, it is just one
   > > index.
   > >
   > > At the end of the day though, the reason is only performance. The
   > > semantics should be the same no matter whether implemented as multiple
   > > indexes or not. Performance is much better with one index though.(*)
   > >
   > 
   > Is it true ?
   > How to guarantee the uniqueness using multiple indexes ?
   > 
   
    Sorry to say, but you all should really think about, what inheritance
   should mean !!!!
   
    In the classic mapping strategy (OO-rdbms mapping) it's said, that 
   each class is mapped to ONE table ! This is the classic mapping
   strategy, which is mentioned in every literature.
   
    The point is: this is classic, but noone does it like this if
   your really have a larger hierarchy of classes. You'll not get 
   any good performance, when solving an association in your oo
   program, because the framework has to query against each 
   table: 6 tables - 6 queries !!! :-(((((
   
    With the PostgreSQL approach one can send ONE query against
   the tables and one would get one result ... which will be
   much faster (I hope so ... that has to be prooved ..).
   
   --
   
    I'm not sure, that inherited indices should be really ONE
   index. There are very well reasons NOT to build ONE larger
   index.
   
    Actually one should think about: why do I really want to 
   have inheritance in the oo-rdbms ? Actually I could put
   all columns (of all classes in this hierarchy into one table 
   and that's it). I would like to have inheritance in this
   database system, because the tables are getting smaller
   and queries against special classes (eh tables) are becoming
   faster.
   
    Actually the inserts will be much faster also because you
   have several smaller indices.
   
    I've run tests here with ONE large table (5 columns and 
   5 indices) holding data for about 17 classes and the result 
   is: the insert/update path is the problem and not the 
   select-path. insert-performance is decreasing in a 
   linear fashon ... very, very bad.
   
   
    Marten
   
   

6. Re: INHERITS doesn't offer enough functionality

   Chris Bitmead <chris@bitmead.com> — 2000-10-20T07:35:41Z

   >  The point is: this is classic, but noone does it 
   > like this if your really have a larger hierarchy of 
   > classes. You'll not get any good performance, when 
   > solving an association in your oo
   > program, because the framework has to query against 
   > each table: 6 tables - 6 queries !!! :-(((((
   > 
   >  With the PostgreSQL approach one can send ONE query 
   > against the tables and one would get one result ... 
   > which will be much faster (I hope so ... that has to 
   > be prooved ..).=
   
   You'll still have to do 6 queries in postgres because it does not return
   fields in sub-classes. Imagine the root of the hierarchy is abstract
   with no fields. You query this class and you get 100 tuples with no
   columns! This is the aspect I'm hoping to fix but I'm waiting for Tom to
   re-do the query data structures before I do changes that are thrown
   away.
   
   >  Actually one should think about: why do I really want to
   > have inheritance in the oo-rdbms ? Actually I could put
   > all columns (of all classes in this hierarchy into one table
   > and that's it).
   
   Ouch. That way lies madness.
   
   

7. Re: INHERITS doesn't offer enough functionality

   Marten Feldtmann <m.feldtmann@t-online.de> — 2000-10-20T18:02:47Z

   
   Chris schrieb:
   > 
   > >  The point is: this is classic, but noone does it
   > > like this if your really have a larger hierarchy of
   > > classes. You'll not get any good performance, when
   > > solving an association in your oo
   > > program, because the framework has to query against
   > > each table: 6 tables - 6 queries !!! :-(((((
   > >
   > >  With the PostgreSQL approach one can send ONE query
   > > against the tables and one would get one result ...
   > > which will be much faster (I hope so ... that has to
   > > be prooved ..).=
   > 
   > You'll still have to do 6 queries in postgres because it does not return
   > fields in sub-classes. 
   
    Practically this is not such a big problem as one might think.
   
    WHEN you have a persistance framework you tell your framework, 
   that every attribut is located (mapped or stored or however you 
   may see it) in the superclass and then your top class (table)
   helds all attributes your "lowest" subclass has.
   
    But that puts another question to be answered: are the defined
   contrained also inheritate ??? Actually I would say: no and
   therefore we have the same handling as with indices.
   
    Most of the attributes may have NULL, but who cares ? The 
   framework actually has to interpret the data coming from
   the database and will throw him away.
   
    Therefore I can get around the limitations of PostgreSQL
   in this case. If PostgreSQL can handle this in addition
   this would be very nice ... but before the basic stuff has
   to be fixed and it has to be very solid.
   
    But I have to admit: my point is a viewpoint from a programmer
   using an object oriented language and I only want to store
   my objects into a database. People using PHP, pearl or
   other "low-level" languages may have a different view or
   need, because they do not have a framework doing the work
   for them.
   
    I can only tell you, what will be an improvement for me as
   a persistance framework programmer and will not help me.
   
    What will not help me:
   
    * that the database generates OID
   
    * that the database generates "clsss" OID (one may want to
      have that, because to recognize which table the data
      comes from..)
   
    * special features to solve very special problems
   
    What will help me:
   
    * all the stuff to reduce the number (!) of queries send 
      to database to get my data
   
    * a way to insert VERY quickly a larger amount of data 
      into a table.
   
    * a good, speedy database
    
   Marten
   
   

8. Re: INHERITS doesn't offer enough functionality

   Chris Bitmead <chris@bitmead.com> — 2000-10-23T21:13:28Z

   Marten Feldtmann wrote:
   
   > > You'll still have to do 6 queries in postgres because it does not return
   > > fields in sub-classes.
   > 
   >  Practically this is not such a big problem as one might think.
   
   >  WHEN you have a persistance framework you tell your framework,
   > that every attribut is located (mapped or stored or however you
   > may see it) in the superclass and then your top class (table)
   > helds all attributes your "lowest" subclass has.
   
   I don't understand what you're saying. There is no query which will
   bring back a set of objects of different types without truncating the
   sub-class fields. Therefore it's a big problem for persistance
   frameworks that use inheritance.
   
   >  I can only tell you, what will be an improvement for me as
   > a persistance framework programmer and will not help me.
   > 
   >  What will not help me:
   > 
   >  * that the database generates OID
   > 
   >  * that the database generates "clsss" OID (one may want to
   >    have that, because to recognize which table the data
   >    comes from..)
   
   You don't seem to be thinking much in terms of an Object Data Management
   Group style persistence framework. That's a shame since it's becoming
   increasingly important. Sun seems to be endorsing it for Java in some
   way too.
   
   > 
   >  * special features to solve very special problems
   > 
   >  What will help me:
   > 
   >  * all the stuff to reduce the number (!) of queries send
   >    to database to get my data
   > 
   >  * a way to insert VERY quickly a larger amount of data
   >    into a table.
   > 
   >  * a good, speedy database
   > 
   > Marten