On Sun, May 5, 2013 at 5:05 AM, Thangalin <thanga...@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.1291351...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs