Re: [HACKERS] about index inheritance
On Tue, May 7, 2013 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, May 6, 2013 at 9:30 AM, Vincenzo Melandri vmelan...@imolinfo.it wrote: Hi guys, My first post here :) I stumbled into the same problem as this guy http://www.postgresql.org/message-id/4be2835a.5020...@cybertec.at , so since I have some spare time recently, I've set-up the development environment for postgresql and I think I may be able to contibute for the feature of index inheritance, that is currently unsopported, but listed in TODOs. I've spent some time reading the docs and I took a look at the code. Is anybody out there working on this already? I don't want to overlap someone else effort, plus I'll gladly take any advice or join the community efforts if any, 'cause this feature seems pretty huge to me at a first glance.. This is a really hard problem. If you pick this as your first project hacking on PostgreSQL, you will almost certainly fail. Thank you very much, i guessed that already -.- Still, I needed that at my office for a long time, struggled with it many times and had to come out with some exotic solutions... Now I have spare time between projects, so I can work on it full-time. At least it's worth a try, isn't it? Anyway, I'm working to better understand the problem, trying to identify at least the main involved points. At the moment I'm figuring out how the inherit mechanism works for relations (in tablecmds.c).. Then I'll figure out about how indexes work.. I guess you discussed this plenty of time already in the past, but I didn't found it in the archive. Any hint for old discussions? I'll try to come out with a list of potential things to do, for you guys to validate and discuss. PS: i wrote last mail from an address with which I had not subscribed to the list, and still the message got through.. Odd.. -- Vincenzo. http://www.linkedin.com/pub/vincenzo-melandri/14/16/730
Re: [HACKERS] about index inheritance
On Wed, May 08, 2013 at 10:19:08AM +0200, Vincenzo Melandri wrote: On Tue, May 7, 2013 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: This is a really hard problem. If you pick this as your first project hacking on PostgreSQL, you will almost certainly fail. Thank you very much, i guessed that already -.- Still, I needed that at my office for a long time, struggled with it many times and had to come out with some exotic solutions... Now I have spare time between projects, so I can work on it full-time. At least it's worth a try, isn't it? Well, you can work on it but I think it will be less programming and more coming up with a feasable solution. Anyway, I'm working to better understand the problem, trying to identify at least the main involved points. At the moment I'm figuring out how the inherit mechanism works for relations (in tablecmds.c).. Then I'll figure out about how indexes work.. While there are probably old threads in the archives, I find the easiest way to look at the problem is in the locking. In particular, I think if you can get unique indexes to work then the rest will follow. Consider the case of an inheritence hierarchy and you want a unique index on a column. Since you want to be able to create and drop children easily, each childs need to have an index just for them. But if you insert a row into one child you need to, somehow, prevent other people also inserting the same value in a different child. Efficiently and deadlock-free. This is hard, though we're up for crazy, out-of-the-box ideas. Note, there is one very special case, namely: - The children are used for partitioning. - The unique index you want is on the partition key. Since each value can only possibly appear in one table your locking problems vanish. The question is: how often does this happen? Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] about index inheritance
On 5/8/13 2:17 PM, Martijn van Oosterhout wrote: On Wed, May 08, 2013 at 10:19:08AM +0200, Vincenzo Melandri wrote: On Tue, May 7, 2013 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: This is a really hard problem. If you pick this as your first project hacking on PostgreSQL, you will almost certainly fail. Thank you very much, i guessed that already -.- Still, I needed that at my office for a long time, struggled with it many times and had to come out with some exotic solutions... Now I have spare time between projects, so I can work on it full-time. At least it's worth a try, isn't it? Well, you can work on it but I think it will be less programming and more coming up with a feasable solution. Anyway, I'm working to better understand the problem, trying to identify at least the main involved points. At the moment I'm figuring out how the inherit mechanism works for relations (in tablecmds.c).. Then I'll figure out about how indexes work.. While there are probably old threads in the archives, I find the easiest way to look at the problem is in the locking. In particular, I think if you can get unique indexes to work then the rest will follow. Consider the case of an inheritence hierarchy and you want a unique index on a column. Since you want to be able to create and drop children easily, each childs need to have an index just for them. But if you insert a row into one child you need to, somehow, prevent other people also inserting the same value in a different child. Efficiently and deadlock-free. This is hard, though we're up for crazy, out-of-the-box ideas. Note, there is one very special case, namely: - The children are used for partitioning. - The unique index you want is on the partition key. Since each value can only possibly appear in one table your locking problems vanish. The question is: how often does this happen? I would also consider indexes that span multiple tables that are do NOT involve inheritance. That's the most generic case, so if you can make that work everything else should fall into place. The only caveat is that UPDATE and DELETE in an inheritance tree could produce unique challenges since they would start off by reading from more than one table. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about index inheritance
On Mon, May 6, 2013 at 9:30 AM, Vincenzo Melandri vmelan...@imolinfo.it wrote: Hi guys, My first post here :) I stumbled into the same problem as this guy http://www.postgresql.org/message-id/4be2835a.5020...@cybertec.at , so since I have some spare time recently, I've set-up the development environment for postgresql and I think I may be able to contibute for the feature of index inheritance, that is currently unsopported, but listed in TODOs. I've spent some time reading the docs and I took a look at the code. Is anybody out there working on this already? I don't want to overlap someone else effort, plus I'll gladly take any advice or join the community efforts if any, 'cause this feature seems pretty huge to me at a first glance.. This is a really hard problem. If you pick this as your first project hacking on PostgreSQL, you will almost certainly fail. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] about index inheritance
Hi guys, My first post here :) I stumbled into the same problem as this guy http://www.postgresql.org/message-id/4be2835a.5020...@cybertec.at , so since I have some spare time recently, I've set-up the development environment for postgresql and I think I may be able to contibute for the feature of index inheritance, that is currently unsopported, but listed in TODOs. I've spent some time reading the docs and I took a look at the code. Is anybody out there working on this already? I don't want to overlap someone else effort, plus I'll gladly take any advice or join the community efforts if any, 'cause this feature seems pretty huge to me at a first glance.. -- Vincenzo. http://www.linkedin.com/pub/vincenzo-melandri/14/16/730
