Re: 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 <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?

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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