Tweaking the TEXT SEARCH CONFIGURATION with an existing database


We realized we forgot to adjust the TEXT SEARCH CONFIGURATION in PostgreSQL for a Nuxeo database, to make the full text searches accent-insensitive.

We tested the CREATE EXTENSION unaccent; etc. commands on a medium-sized Nuxeo, which worked well except the first login after launching Nuxeo took longer than usual.

I reckon this is more PostgreSQL-related, but does the database have to rebuild the whole index after making changes to this configuration? This could be a problem if we execute the script on our production database, which is much larger than the first one (we don't want to end up with Nuxeo being frozen for… an undefinite amount of time).

Thanks for your advice

0 votes

1 answers



The question really is about how/when the Postgres FTS configuration change applies. As far as I can tell the text search configuration is set up in the nx_to_tsvector function, which is used to build the fts index on the fulltext table.

Somehow when default-repository-config.xml is modified, Nuxeo must be able to detect this and tell Postgres to rebuild its index. The question is really: does it do this on startup, at first nxql query, some other time?


Nuxeo does not automatically rebuild fulltext indexes when the fulltext configuration changes. The slowdown you're seeing must be coming from somewhere else.

If you changed the fulltext configuration you'll need to reindex everything, the nuxeo-reindex-fulltext addon can do that for you.

1 votes

Thanks for your answer.

The addon works by changing the dc:title metadata and then changing it back. Isn't it more efficient to drop the nx_to_tsvector function, recreate it with the correct configuration, then recreate the three fulltext indexes straight from PostgreSQL? As far as I can see it would bring the fts search offline between the indexes being dropped and them being rebuilt, but it may be preferable to an inconsistent search (queries being constructed with the 'fr' analyzer running against an index built with the 'english' one).

Besides I'm uncertain how changing the repo.xml would change the function used to build the index. I'm hardly a PostgreSQL expert but I understand you can't change a function, only drop it and recreate it. And dropping the nx_to_tsvector function would either not work or drop the fts indexes too (depending on whether the drop is cascaded) - in which case they need to be rebuilt manually.


Yes you're right, in your case if all that changed is the PostgreSQL TEXT SEARCH CONFIGURATION then that would be enough and much more efficient.

The addon is designed to do other kinds of reindexing, when for instance new binary converters have been added to Nuxeo, or new fields have been added to (or removed from) the indexable fields.