Re: multi-relation indices

On Thu, Oct 20, 2005 at 02:53:39PM +0530, Ajay wrote:
> This is first time I am trying to do development on POSTGRES.
> I am trying to build multi-relational indices. So that when user enters
> keyword he will get in which relation in which tuple this keyword occured.
> This is also known as inverted indices. I am thinking of adding Index at
> database level which will kepp track of all keywords. Another approach
> is to create a dummy table which will store information of all words in
> database. And add index on this tacle. Can any one guide me regarding
> how feasiable these method are? And if feasiable which one is better?

Firstly, search the archives for multi-relational indexes, the topic
has been discussed before.

However, the main stumbling block appears to be locking. Many places in
the code assume that if they have locked the table they have locked all
its associated indexes too. How do you deal with someone dropping a
table? Currently, all the associated indexes get dropped too, yet with
a multirelational index you can't do that.

These are not insoluable, but they are tricky. IMHO, a more productive
way to go would be to have an index type that is automatically created
for each table when the table is created. Then define a way to query
all those indexes at once. But YMMV.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.