Re: [NOVICE] index refuses to build

On Thu, Dec 29, 2011 at 10:40:19PM -0500, Tom Lane wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> > On Thu, Dec 29, 2011 at 5:10 PM, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
> >> CREATE INDEX tst1m_name_lu_ix ON tst1m(unaccent(name));
> >> ERROR: functions in index expression must be marked IMMUTABLE
>
> > 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?
>
> Hmm ... it's clear why unaccent(text) is only stable, because it depends
> on the current search_path to find the "unaccent" dictionary. But I
> wonder whether it was an oversight that unaccent(regdictionary, text)
> is stable and not immutable. We don't normally mark functions as stable
> just because you could in principle change their behavior by altering
> some outside-the-database configuration files.

Should we change the function signature for unaccent(regdictionary,
text)?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +