Re: pg_upgrade fails saying function unaccent(text) doesn't exist
От | Adrian Klaver |
---|---|
Тема | Re: pg_upgrade fails saying function unaccent(text) doesn't exist |
Дата | |
Msg-id | 6c73c320-95b7-c259-e309-c46300557852@aklaver.com обсуждение исходный текст |
Ответ на | Re: pg_upgrade fails saying function unaccent(text) doesn't exist (Gunnlaugur Thor Briem <gunnlaugur@gmail.com>) |
Ответы |
Re: pg_upgrade fails saying function unaccent(text) doesn't exist
(Gunnlaugur Thor Briem <gunnlaugur@gmail.com>)
|
Список | pgsql-general |
On 08/30/2018 02:24 AM, Gunnlaugur Thor Briem wrote: > Thank you! That got the pg_upgrade to completion. But then during > ./analyze_new_cluster.sh vacuum fails thus: > > vacuumdb: processing database "dm_test": Generating minimal optimizer > statistics (1 target) > vacuumdb: vacuuming of database "dm_test" failed: ERROR: text search > dictionary "unaccent" does not exist > CONTEXT: SQL function "semantic_normalize" statement 1 > > And yet a text search dictionary with that name does exist: > > $ psql -d dm_test -c '\dFd+ unaccent' > List of text search dictionaries > Schema | Name | Template | Init options | Description > --------+----------+-----------------+--------------------+------------- > public | unaccent | public.unaccent | rules = 'unaccent' | > (1 row) > > Running VACUUM ANALYZE semantic_mapping in psql works: > > $ psql -d dm_test -c 'VACUUM ANALYZE semantic_mapping' > VACUUM > Time: 1231,767 ms (00:01,232) > > But running it with the vacuumdb command doesn't: > > vacuumdb -z -t semantic_mapping dm_test > vacuumdb: vacuuming database "dm_test" > vacuumdb: vacuuming of table "semantic_mapping" in database "dm_test" > failed: ERROR: text search dictionary "unaccent" does not exist > CONTEXT: SQL function "semantic_normalize" statement 1 > > This is presumably a similar search path problem, because I can > reproduce this in psql by setting the search path to exclude public: > > set search_path to "$user"; > vacuum analyze public.semantic_mapping; > ERROR: text search dictionary "unaccent" does not exist > CONTEXT: SQL function "semantic_normalize" statement 1 > Time: 851,562 ms > > Can't find a place to poke the "public." prefix in to work around this > ... I can't even see where it's getting the link to the text search > dictionary from. Is that in native code in the unaccent extension? Since the semantic_normalize function is tripping it and it uses unaccent I would say it is native to the extension. What does: \dFd unaccent show? > > The unaccent definition looks like this: > > \df+ public.unaccent > List of functions > Schema | Name | Result data type | Argument data types | Type | > Volatility | Parallel | Owner | Security | Access privileges | Language > | Source code | Description > --------+----------+------------------+---------------------+--------+------------+----------+-------+----------+-------------------+----------+---------------+------------- > public | unaccent | text | regdictionary, text | normal | > stable | safe | gthb | invoker | | c > | unaccent_dict | > public | unaccent | text | text | normal | > stable | safe | gthb | invoker | | c > | unaccent_dict | > (2 rows) > > Any tips? > > Cheers, > Gulli > > On Wed, Aug 29, 2018 at 7:06 PM Bruce Momjian <bruce@momjian.us > <mailto:bruce@momjian.us>> wrote: > > On Wed, Aug 29, 2018 at 06:09:53PM +0000, Gunnlaugur Thor Briem wrote: > > Hi, > > > > I'm trying to upgrade an old PG 9.4 cluster to PG 10, and it's > failing on > > creating an index that uses the unaccent(text) function. > > > > That function is part of the unaccent extension, which is > installed in the old > > DB cluster. I expect pg_upgrade to create that extension as part > of the > > upgrade. It does create other extensions that are installed in > the old DB > > cluster. I don't get why this one isn't included. > > This is caused by security changes made in PG 10.3 and other minor > releases. Please see this thread for an outline of the issue: > > https://www.postgresql.org/message-id/flat/152106914669.1223.5104148605998271987%40wrigleys.postgresql.org > > I think you have to change your index function to specify the schema > name before the unacces function call, e.g. > > SELECT lower(public.unaccent(btrim(regexp_replace( > > -- > Bruce Momjian <bruce@momjian.us <mailto:bruce@momjian.us>> > http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Следующее
От: Gunnlaugur Thor BriemДата:
Сообщение: Re: pg_upgrade fails saying function unaccent(text) doesn't exist