Re: index refuses to build

On Thu, 29 Dec 2011 17:16:22 -0600
Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

Woops, sorry: reposting on the ML.

> > Decomposing it reveals that it is the 'unaccent' part that
> > blocks:
> >
> > CREATE INDEX tst1m_name_lu_ix ON tst1m(unaccent(name));
> > ERROR:  functions in index expression must be marked IMMUTABLE
> >
> > From what I found on the web I also tried to cast it to text,
> > but the error's still here:(
>
> your problem is the unaccent function. it's defined stable because
> the rules function it depends on can change after the index is built
> -- that would effectively introduce index corruption. it's possible
> to bypass that restriction, but are you sure that's what you want to
> do?

Well, I don't know how to achieve what I want another way.

Overriding this restriction can effectively become a concern
as I use unaccent.rules (modified for fr and de) and I can't be
absolutely sure it won't evolve since the DB is to be used by
(mostly) CE people - so, if I don't have all modifications for
v1.0, there's a risk.

My goal is to have a case insensitive + unaccented index.

JY
--
As they say about Dungeons and Dragons, "Life's a die, and then you
bitch."