4

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?

  • I've tried to replicate your issue but the index creation worked. – dezso Apr 8 '15 at 15:17
  • Is the "public" schema mapped in your search_path? Besides of that, I highly recommend you not executing that ALTER FUNCTION, create your own SQL IMMUTABLE function and wrap the unaccent call on it, this will help on upgrades. – MatheusOl Apr 8 '15 at 16:34
6

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

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

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

And do not alter the original function:

ALTER FUNCTION unaccent(text) IMMUTABLE;

.. 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:

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:

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

Details in this related answer

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
  • Hi, thanks for the tip. I actually tried to use "public.unaccent" to make sure it really searches in he public shcema. But it should have worked with pure unaccent() as well. – Balázs E. Pataki Apr 9 '15 at 8:00
  • I also tried your suggestion, I could create a new f_unaccent function, use it with a simple select(), however, when I try to use it in the create index I get # CREATE INDEX author_label_hun_gin_trgm ON address USING gin (f_unaccent(label_hun) gin_trgm_ops); ERROR: function unaccent(unknown, text) does not exist LINE 2: SELECT unaccent('unaccent', $1) ^ – Balázs E. Pataki Apr 9 '15 at 8:05
  • @BalázsE.Pataki: I added a chapter for troubleshooting. – Erwin Brandstetter Apr 9 '15 at 12:14
  • Thank you, Erwin! My database was imported from an sql dump. I re-imported it in a new database and now I can create the trigram index without any problem. I couldn't figure out what went wrong with my original import, though. – Balázs E. Pataki Apr 9 '15 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.