Postgresql missing text search configuration

Question

I've created a text search configuration on my PostgreSQL server 9.3.5 like this following the postgresql documentation:

 CREATE TEXT SEARCH CONFIGURATION french_noaccent ( COPY = french );
 ALTER TEXT SEARCH CONFIGURATION french_noaccent ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

I'm using it already one of my Django project database and can see it using \dF :

   Schéma   |       Nom        |              Description              
 ------------+------------------+---------------------------------------
 pg_catalog | danish           | configuration for danish language
 pg_catalog | dutch            | configuration for dutch language
 pg_catalog | english          | configuration for english language
 pg_catalog | finnish          | configuration for finnish language
 pg_catalog | french           | configuration for french language
 pg_catalog | german           | configuration for german language
 pg_catalog | hungarian        | configuration for hungarian language
 pg_catalog | italian          | configuration for italian language
 pg_catalog | norwegian        | configuration for norwegian language
 pg_catalog | portuguese       | configuration for portuguese language
 pg_catalog | romanian         | configuration for romanian language
 pg_catalog | russian          | configuration for russian language
 pg_catalog | simple           | simple configuration
 pg_catalog | spanish          | configuration for spanish language
 pg_catalog | swedish          | configuration for swedish language
 pg_catalog | turkish          | configuration for turkish language
 public     | french_noaccent  | 
 public     | spanish_noaccent |

However, when I try to use it on another project database (still on the same server), I get this error

 text search configuration "public.french_noaccent" does not exist
 LINE 1: ...rank_cd(watson_searchentry.search_tsv, to_tsquery('public.fr...

(Sorry I don't know how to get the full LINE :/)

Does somebody has any idea why is it so, and how I can make my text search config available on the 2nd database ?

Thank you very much

Edit (thanks to @doru): I created the text search config using the root account, but my projects database have a custom owner, when I use \dF with this account I can't see them in the list. I tried

ALTER TEXT SEARCH CONFIGURATION french_noaccent OWNER TO myuser;

but still can't see them. So I tried this with my custom account:

myuser=> CREATE TEXT SEARCH CONFIGURATION french_noaccenttst ( COPY = french );
CREATE TEXT SEARCH CONFIGURATION
myuser=> ALTER TEXT SEARCH CONFIGURATION french_noaccenttst ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;
ERROR:  text search dictionary "unaccent" does not exist

So I guess my question now is How can I make the unaccent dictionary available to my user ?

If you run SHOW default_text_search_config; what do you get? — doru
– doru, Commented Dec 10, 2015 at 10:26
default_text_search_config ---------------------------- pg_catalog.simple But in both my project I tell postgres to use the public.french_noaccent text config — mrequillart
– mrequillart, Commented Dec 10, 2015 at 10:32
Did you do SET default_text_search_config = 'public.french_noaccent';? — doru
– doru, Commented Dec 10, 2015 at 10:37
No, but because I don't want it to be default ^^ On both project I use the same search backend where the text configuration is retrieved from the user language settings, it works fine on the 1st but not the 2nd. — mrequillart
– mrequillart, Commented Dec 10, 2015 at 10:44
both db have same user owner? From docs "The user who defines a text search configuration becomes its owner." — doru
– doru, Commented Dec 10, 2015 at 10:45

DavidG · Accepted Answer · 2015-12-10 17:26:00Z

Ok so I finally got it working, and think I might be able to explain why.

At some point I checked the permission of my user on my databases using \dn+

On the first one (the one which was working fine) I had :

                           Liste des schémas
           Nom               | Propriétaire | Droits d'accès |  Description 
          public             | myuser       | root=UC/root + 
                                              =UC/root       | standard public schema

and on the second one (which was not working)

                           Liste des schémas
           Nom               | Propriétaire | Droits d'accès |  Description 
          public             | myuser       |                | standard public schema

So I granted access to the public schema to root to match the first configuration, using myuser :

myuser=> grant usage on schema public to root;
myuser=> grant create on schema public to root;

                           Liste des schémas
           Nom               | Propriétaire | Droits d'accès    |  Description 
          public             | myuser       | myuser=UC/myuser +|standard public schema 
                                              root=UC/myuser    |

Not exactly the same, but it should work, my text search configuration was still not available with \dF, but let's see if the unaccent module is available with \dFd

                 Liste des dictionnaires de la recherche de texte
   Schéma   |       Nom       |                        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        |

So I recreated my text search config using the first 2 command of my initial post, only this time I did it using the myuser account on my buggy database

myuser=> CREATE TEXT SEARCH CONFIGURATION french_noaccenttst ( COPY = french );
myuser=> ALTER TEXT SEARCH CONFIGURATION french_noaccenttst ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

And this time it worked great, and they appeared in \dF

       Liste des configurations de la recherche de texte
   Schéma   |       Nom        |              Description              
------------+------------------+---------------------------------------
 pg_catalog | danish           | configuration for danish language
 pg_catalog | dutch            | configuration for dutch language
 pg_catalog | english          | configuration for english language
 pg_catalog | finnish          | configuration for finnish language
 pg_catalog | french           | configuration for french language
 pg_catalog | german           | configuration for german language
 pg_catalog | hungarian        | configuration for hungarian language
 pg_catalog | italian          | configuration for italian language
 pg_catalog | norwegian        | configuration for norwegian language
 pg_catalog | portuguese       | configuration for portuguese language
 pg_catalog | romanian         | configuration for romanian language
 pg_catalog | russian          | configuration for russian language
 pg_catalog | simple           | simple configuration
 pg_catalog | spanish          | configuration for spanish language
 pg_catalog | swedish          | configuration for swedish language
 pg_catalog | turkish          | configuration for turkish language
 public     | french_noaccent  | 
 public     | spanish_noaccent |

Finally \o/

I'm not sure why this access privileges where buggy, but I think it comes from AWS RDS : At the launch of the RDS instance, I had asked to create the user with the guilty database. It might be something else but I think it's the only difference there was between the 2 databases.

Sorry for the long post, but after all it's only a summary of my afternoon :D

That's not one to me, but I'm French that might be why. We like to curse. I don't understand why you care though.
Half of my family comes from a country that swears 10 times more than the French, but I still know when it's a good idea to avoid using words like that. I care because I care about keeping this site a clean reference and it's also agreed that we don't use them. I'm sure you are already aware that using vulgar language is considered bad taste.
Clearly we don't have the same interpretation of the post you linked, nor on expletives, but after all everyone has different tastes. Have fun correcting other people vocabulary.
Just to be clear, the reason I came here to edit this post is because it was flagged up in one of the chat channels by an automated system that specifically looks for profanity and spam. And while I'm here, I'm not correcting vocabulary, I'm removing unneeded an unnecessary words from your post.

Collectives™ on Stack Overflow

Postgresql missing text search configuration

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related