Обсуждение: 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:
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

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

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