Thread: Re: [NOVICE] index refuses to build
Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Dec 29, 2011 at 5:10 PM, Jean-Yves F. Barbier <12ukwn@gmail.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.
regards, tom lane
On Thu, Dec 29, 2011 at 10:40:19PM -0500, Tom Lane wrote: > Merlin Moncure <mmoncure@gmail.com> writes: > > On Thu, Dec 29, 2011 at 5:10 PM, Jean-Yves F. Barbier <12ukwn@gmail.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Sun, Aug 26, 2012 at 09:47:01AM -0400, Bruce Momjian wrote: > On Thu, Dec 29, 2011 at 10:40:19PM -0500, Tom Lane wrote: > > Merlin Moncure <mmoncure@gmail.com> writes: > > > On Thu, Dec 29, 2011 at 5:10 PM, Jean-Yves F. Barbier <12ukwn@gmail.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)? Did we decide not to do this? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +