stable vs. immutable unaccent function
Hi,
Given the following function:
CREATE OR REPLACE FUNCTION unaccent_text(text)
RETURNS text AS
$BODY$
-- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
-- comment this line out when calling pg_dump.
SELECT unaccent($1);
-- Uncomment this line when calling pg_dump.
--SELECT ''::text;
$BODY$
LANGUAGE sql IMMUTABLE
COST 1;
Consider the following query as part of a function that takes a single
parameter, p_label text:
SELECT
t.id
INTO
v_id
FROM
some_table t
WHERE
unaccent_text(t.label) % p_label
ORDER BY
similarity(t.label, p_label) DESC, t.label
LIMIT 1;
The above code fails; the value for v_id is never set. Without calling *
unaccent_text*, the following code works:
SELECT
t.id
INTO
v_id
FROM
some_table t
WHERE
unaccent(t.label) % p_label
ORDER BY
similarity(t.label, p_label) DESC, t.label
LIMIT 1;
I believe this violates the principle of least astonishment. I was
astonished that wrapping the unaccent function within another function
affects the outcome. What's even more interesting is that the following
code, when issued outside of a function, works (by removing the INTO
clause):
SELECT
t.id
FROM
some_table t
WHERE
unaccent_text(t.label) % p_label
ORDER BY
similarity(t.label, p_label) DESC, t.label
LIMIT 1;
Pretty strange without knowing PostgreSQL internals. :-)
# psql --version
psql (PostgreSQL) 9.1.9
# uname -a
Linux panther 3.2.0-39-generic #62-Ubuntu SMP Thu Feb 28 00:28:53 UTC 2013
x86_64 x86_64 x86_64 GNU/Linux
# cat /etc/issue
Ubuntu 12.04.2 LTS \n \l
As an aside, the comments in the unaccent_text function are accurate:
pg_dump cannot dump the database without first commenting out the SELECT
unaccent($1); line.
Ideas on why this happens, and any work-arounds, are appreciated.
Thank you!
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:
/messages/by-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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Sun, May 5, 2013 at 9:01 PM, Marti Raudsepp <marti@juffo.org> wrote:
A STABLE function may call an IMMUTABLE function, but not the other way around.
Well no, an immutable function can and may call a stable (or even
volatile) function. Postgres won't stand in your way. It's probably a
bad idea unless it's carefully thought out though.
--
greg
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
I wrote the unaccent_text wrapper function to achieve the following:
CREATE INDEX table_name_label_unaccent_idx
ON table_name
USING gin
(unaccent_text(label::text) COLLATE pg_catalog."default" gin_trgm_ops);
I would have liked to use unaccent directly.
The fact that someone wrote such a comment should be a clue that it's a
hack. :)
My comment. I acknowledge it was a hack. Couldn't see how to add the
unaccented index (without adding an extra column, which is just another
hack). ;-)
A proper solution, Marti, I thank you for: using the STABLE unaccent
function from MusicBrainz. I'll take a look -- hopefully it works with 9.1.
T