Обсуждение: INHERITS doesn't offer enough functionality
I noticed that INHERITS doesn't propogate indecies, It'd be nice if there was an toption to do so. thanks, -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein wrote: > > I noticed that INHERITS doesn't propogate indecies, It'd be nice > if there was an toption to do so. Yep it would. Are you volunteering?
* Chris <chrisb@nimrod.itg.telstra.com.au> [000828 17:15] wrote: > Alfred Perlstein wrote: > > > > I noticed that INHERITS doesn't propogate indecies, It'd be nice > > if there was an toption to do so. > > Yep it would. Are you volunteering? Ha, right now I'm doing a major rewrite of my own code to hack around vacuum, it'll have to wait but thanks for the vote of confidence. :-) -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> Alfred Perlstein wrote: > > > > I noticed that INHERITS doesn't propogate indecies, It'd be nice > > if there was an toption to do so. > > Yep it would. Are you volunteering? > Added to TODO: * Allow inherited tables to inherit index -- 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 <pgman@candle.pha.pa.us> [001016 08:55] wrote: > > Alfred Perlstein wrote: > > > > > > I noticed that INHERITS doesn't propogate indecies, It'd be nice > > > if there was an toption to do so. > > > > Yep it would. Are you volunteering? > > > > Added to TODO: > > * Allow inherited tables to inherit index Thank you, it's not a big problem that this doesn't happen, but it'd be nice to see it as an option when creating a table via inheritance. What about RULEs? I wouldn't really have a use for that but others might. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein wrote: > Thank you, it's not a big problem that this doesn't happen, but it'd > be nice to see it as an option when creating a table via inheritance. > > What about RULEs? I wouldn't really have a use for that but others > might. Actually it's a reasonably big deal. Apart from the obvious performance penalty for a deep inheritance hierarchy it affects the implementation of unique keys, referential integrity for inheritance and the transparancy of extending an inheritance hierarchy.
Bruce Momjian wrote: >> Alfred Perlstein wrote: >> > >> > I noticed that INHERITS doesn't propogate indecies, It'd be nice>> > if there was an toption to do so. >> >> Yep it would. Are you volunteering? >> > >Added to TODO: > > * Allowinherited tables to inherit index What is the spec for this? 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? -- 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 ofthine heart." Psalms 37:4
* Oliver Elphick <olly@lfix.co.uk> [001018 04:59] wrote: > Bruce Momjian wrote: > >> Alfred Perlstein wrote: > >> > > >> > I noticed that INHERITS doesn't propogate indecies, It'd be nice > >> > if there was an toption to do so. > >> > >> Yep it would. Are you volunteering? > >> > > > >Added to TODO: > > > > * Allow inherited tables to inherit index > > What is the spec for this? > > 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. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein wrote: >* Oliver Elphick <olly@lfix.co.uk> [001018 04:59] wrote: >> Do you mean that inheriting tables shouldshare a single index with their >> ancestors, or that each descendant should get a separate index on the >> same patternas its ancestors'? >> >> With the former, the inherited index could be used to enforce a primary >> key over awhole inheritance hierarchy, and would presumable make it >> easier to implement RI against an inheritance hierarchy. Isthis what >> you have in mind? > >Not really, it's more of a convience issue for me, a 'derived table' >should inheritthe attributes of the 'base table' (including indecies), >having an index shared between two tables is an interestingidea 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 ofthine heart." Psalms 37:4
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
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
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.
> 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.
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
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
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