Обсуждение: about index inheritance

On Mon, May 6, 2013 at 9:30 AM, Vincenzo Melandri <vmelandri@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.5020601@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

On Wed, May 08, 2013 at 10:19:08AM +0200, Vincenzo Melandri wrote:
> On Tue, May 7, 2013 at 11:55 PM, Robert Haas <robertmhaas@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   <kleptog@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

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 <robertmhaas@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
aninheritance tree could produce unique challenges since they would start off by reading from more than one table.
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net