2

I have many such entries in the postgresql main log, ever since upgrading to Postgres 10:

2018-03-28 08:51:00.281 CEST [97547] ERROR:  text search dictionary "unaccent" does not exist
2018-03-28 08:51:00.281 CEST [97547] CONTEXT:  automatic analyze of table "dbname.public.periodical"

I do use unaccent in many indexes in all tables (by making unaccent immutable (I know it is not the recommended way, but it is so historically)).

If I manually run vacuumdb dbname --table periodical or VACUUM periodical or VACUUM ANALYZE periodical, it does not generate such an error (neither on the command line, nor in the log).

Does anyone have a tip how I could stop these messages and what their real cause may be?

PostgreSQL version: 10.3 (Debian 10.3-1.pgdg80+1)

  • No, I do not. I would like add the public schema to the autovacuum search_path if I knew how to do that, but I found nothing in the documentation to that effect (just that they removed it from there, but no way to put it back, I would not like to downgrade or recompile postgres). I could also try to transfer the unaccent extension to the pg_catalog schema, but that is also seemingly impossible. – P.Péter Aug 13 '18 at 7:19
  • The search_path for autovacuum has been changed for security reasons. I don't expect them to revert that change nor to offer users the unsafe option to do so. Have you tried to schema-qualify function and dictionary as suggested? – Erwin Brandstetter Aug 13 '18 at 7:43
  • Schema-qualifying need application-side change which I am not sure I can make. Also, it needs a complete database rebuild using the new unaccent function. I may try it later, but not in the very near future. The security measure does not really apply to me, as I have a single database with a single user and access control is all in the application, so it is more annoying than useful to me; it would be nice to be able to turn it off. – P.Péter Aug 13 '18 at 8:09
  • Actually, you can change the function definition with CREATE OR REPLACE FUNCTION ... without rebuilding anything. Expression indexes rely on the immutability of the used function and are not stopping you. If you make sure that the function keeps using the same function & dictionary, indexes keep working properly. (You can't DROP the function, though.) – Erwin Brandstetter Aug 13 '18 at 8:18
1

You are obviously using a fake-IMMUTABLE function wrapper for unaccent() to use it in indexes. It's based on the search dictionary "unaccent" implicitly or explicitly. The dictionary to be used can be provided as optional first function parameter to unaccent(). The name of the dictionary can be schema-qualified. Else it needs to reside in one of the schemas of the current search_path.

I do not advise to manipulate the function unaccent() provided by the extension. Instead, create and use a wrapper function like instructed here:

In your case, the additional module unaccent might be installed in a schema that's missing from the search_path of the session reporting these errors. When you try manually, you do that in a session where the search_path includes that same schema, so no error there.

You can either set your search_path to include the schema of the extension in all sessions, or schema-qualify the dictionary in the function calls of unaccent().

Security for client programs has been tightened with Postgres 10.3 / 9.6.8 etc. Now you need to schema-qualify function and dictionary when used in any indexes - as demonstrated in the linked answer above.

The release notes for Postgres 10.3:

Also, the changes described in the second changelog entry below may cause functions used in index expressions or materialized views to fail during auto-analyze, or when reloading from a dump. After upgrading, monitor the server logs for such problems, and fix affected functions.

...

  • Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

    pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well.

    In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior.

Bold emphasis mine.

See the special mention of auto-analyze and autovacuum? To fix, schema-qualify function name and dictionary in the unaccent() call of the wrapper function used in your index(es) as demonstrated in the linked answer. Like:

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;

You can change an existing function definition with CREATE OR REPLACE FUNCTION ... without rebuilding existing indexes based on it. Make sure the function keeps using the same function & dictionary, then indexes keep working properly without REINDEX. (You can't DROP the function, though, while it's used in an index.)

Moving the unaccent module to the system catalog pg_catalog also works. Then everything is in the search_path automatically. You ended up doing so, but I do not advocate that. Messing with system catalogs is generally discouraged and can easily damage your installation if you don't know exactly what you are doing.

Related:


The search dictionary "unaccent" is also regularly used by text search configurations in full text search. Related:

  • The other idea is most probably not true, as it was not an "upgrade" in the strict sense of the word, but a recreation of the database in postgres 10. – P.Péter Mar 29 '18 at 10:31
  • @P.Péter: Did you re-install the additional module unaccent in the process? I added some more above. – Erwin Brandstetter Mar 29 '18 at 12:05
  • Of course I installed that extension. It is actually included in the official debian postgres server package (postgresql-10), so I cannot really not install it. :) Also, if I did not have it installed, then it really coudn't work properly, no matter what session the query would run. I will try to look into your search_path hypothesis, but I will have to identify the search_path during automatic analyze. – P.Péter Mar 29 '18 at 12:58
  • In light of the fix in pg 10.3, I think my first hypothesis idea with the search_path was a good lead. Consider the update. – Erwin Brandstetter Mar 30 '18 at 15:13
  • As I wrote in my question, I am already on 10.3 (Debian 10.3-1.pgdg80+1). – P.Péter Apr 4 '18 at 7:36

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.