'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX?

Question

I'm running PostgreSQL 9.3 on Mac OS X Yosemite.

I try to create an unaccent lowercase trigram index. To achieve it I did this:

mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; 
       CREATE EXTENSION unaccent SCHEMA public; 
       ALTER FUNCTION unaccent(text) IMMUTABLE;
CREATE EXTENSION
CREATE EXTENSION
ALTER FUNCTION

Then I tried to create the index:

mydb=# CREATE INDEX author_label_hun_gin_trgm ON address 
       USING gin (public.unaccent(lower(label_hun)) gin_trgm_ops);
ERROR:  text search dictionary "unaccent" does not exist

... and got this error. If I try to list the available text search dictionaries the unaccent dictionary seems to be there:

mydb=# \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description                        
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
 public     | unaccent        | 

Any idea what could be wrong here?

Answer 1

Do not alter the original function:

ALTER FUNCTION unaccent(text) IMMUTABLE;

I recommend this alternative approach to get an IMMUTABLE unaccent() function:

CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$;

Use that function for the expression index and in all queries. Detailed explanation:

• Does PostgreSQL support “accent insensitive” collations?

... which also solves any possible issues with the search_path: The fact that you had to schema-qualify public.unaccent() in your index definition suggests that you might not have public in your search_path.

Then, your index can be:

CREATE INDEX author_label_hun_gin_trgm ON address 
USING gin (f_unaccent(label_hun) gin_trgm_ops);

I did not include lower(). You can, but trigram indexes support case insensitive patterns out of the box:

• Pattern matching with LIKE, SIMILAR TO or regular expressions

Troubleshooting

If you are running all of this in the same session with the same search_path and one call works while the next reports function ... does not exist, then something is seriously broken in your database.

First make sure you are testing with a valid search_path setting:

SHOW search_path

See:

• How does the search_path influence identifier resolution and the "current schema"

If you diagnose breakage, switch into alarm mode and take all the necessary steps to avoid data loss (like drawing a backup before you do anything else).

A broken index for the system table pg_proc might be the cause. To reindex all system tables from the shell:

reindexdb [connection-option...] --system mydb