Re: index refuses to build

On Thu, Dec 29, 2011 at 5:10 PM, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
> Hi list,
>
> I'm wrong somewhere, but where?:
>
> CREATE INDEX tst1m_name_lu_ix ON tst1m(lower(unaccent(name)));
> ERROR:  functions in index expression must be marked IMMUTABLE
>
> 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?

merlin