On AWS RDS Postgres, how to have dictionaries and unaccented full-text search?

Question

I want to use PostgreSQL 9.5.4 on AWS RDS, leveraging the full-text search, dictionaries with stop words, unaccented full-text search.

The context:

With 'unaccent' on, this full-text (Json) query will find 'F(e-acute)vrier' even if I did not enter the accent properly

psql>select * from proto_model.product where to_tsvector((body ->> 'description')) @@ to_tsquery('Fevrier');

With an English dictionary, the same search for 'the', 'any', 'you'... will find nothing as they are 'stop words' defined in the English dictionary and ignored.

The issue:

On my local Postgres, this is not an issue at all. On the managed AWS this is one. EC2+Docker on AWS would not be an issue of course but I focus on RDS Postgres for now.

Locally, the default value of default_text_search_config (get it with psql>show all) is 'pg_catalog.english' which uses the English dictionary and stop words. On RDS this is 'pg_catalog.simple'.

1) In AWS, I cannot add a dictionary or modify one as you need file system access that you don't have. There is no programatical solution to create/update a dictionary AFAIK.

2) In AWS, as the 'postgres' user or even as the 'rds_superuser' that you can create, I cannot alter the global configuration

psql>ALTER SYSTEM SET default_text_search_config = 'pg_catalog.english';
ERROR:  must be superuser to execute ALTER SYSTEM command

In addition, it is absent of the RDS Postgres parameter groups that you can associate to a new Postgres instance and you cannot add missing values! Granting more to 'rds_superuser' (psql>grant all on schema public to ...) does not help.

3) In AWS, as 'postgres' or 'rds_superuser', I can however set for my session, the current text configuration

psql>set default_text_search_config = 'pg_catalog.english'; 
SET

4) Sadly in AWS, as 'postgres' or 'rds_superuser', I cannot alter the search configuration (global) to ignore the accents. This works fine locally.

psql>ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;
ERROR:  must be owner of text search configuration english

5) In AWS, as 'postgres' or 'rds_superuser', I can create a new search configuration (English + Unaccent) but I cannot set it as the default even in my session!

psql>CREATE TEXT SEARCH CONFIGURATION english2 (copy=english);
CREATE...
psql>ALTER TEXT SEARCH CONFIGURATION english2 ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;
ALTER...
psql>set default_text_search_config = 'pg_catalog.english2';
ERROR:  invalid value for parameter "default_text_search_config": "pg_catalog.english2"

So it seems that I am cooked.

The best that I can see could be to associate automatically without a need for individual psql>set default_text_search_config = ... my user connection to a set of configuration options

psql>alter role somerole set default_text_search_config = 'pg_catalog.english';
psql>select * from pg_user; (the option is present by default for all my connections under this role)

Outside of moving out of AWS RDS to EC2+Docker, do you know any solution to (4) or (5) providing me dictionary+unaccent?

Answer 1

You can change by altering the role parameters in the database like:

ALTER ROLE [role] IN DATABASE [database]
    SET default_text_search_config TO 'pg_catalog.english';