Re: 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 <robertmhaas(at)gmail(dot)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 jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net