Re: stable vs. immutable unaccent function

On Sun, May 5, 2013 at 5:05 AM, Thangalin <thangalin(at)gmail(dot)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