On Sun, May 5, 2013 at 5:05 AM, Thangalin <thangalin@gmail.com> wrote:
> CREATE OR REPLACE FUNCTION unaccent_text(text)
> -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
> -- comment this line out when calling pg_dump.
The fact that someone wrote such a comment should be a clue that it's a hack. :)
There are good reasons why unaccent() isn't IMMUTABLE:
http://www.postgresql.org/message-id/16472.1291351806@sss.pgh.pa.us
> I believe this violates the principle of least astonishment. I was
> astonished that wrapping the unaccent function within another function
> affects the outcome.
I agree it can be astonishing, but STABLE/IMMUTABLE annotations are
promises to the database about the behavior of the function.
PostgreSQL relies on the function definer getting it right. A STABLE
function may call an IMMUTABLE function, but not the other way around.
http://www.postgresql.org/docs/current/static/xfunc-volatility.html :
"An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever. This
category allows the optimizer to pre-evaluate the function when a
query calls it with constant arguments."
PostgreSQL already does some sanity checking for SQL and PL/pgSQL
functions, but it doesn't detect this case.
> Ideas on why this happens, and any work-arounds, are appreciated.
The MusicBrainz project has their own unaccent implementation that's
designed to be properly STABLE:
https://github.com/metabrainz/musicbrainz-server/tree/master/postgresql-musicbrainz-unaccent
Another approach is storing the result of unaccent(label) in a
separate column (e.g. using a trigger) and index & query that. It
won't solve the fact that unaccent may return different results at
different times, but you will always get consistent results to your
queries.
Regards,
Marti
