Re: pg_upgrade fails saying function unaccent(text) doesn't exist
| От | Gunnlaugur Thor Briem |
|---|---|
| Тема | Re: pg_upgrade fails saying function unaccent(text) doesn't exist |
| Дата | |
| Msg-id | CAPs+M8KRP3McWzVwU1hs9QZXsUHXbb7RSgtshqcy4NwDgW0VHg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: pg_upgrade fails saying function unaccent(text) doesn't exist (Bruce Momjian <bruce@momjian.us>) |
| Ответы |
Re: pg_upgrade fails saying function unaccent(text) doesn't exist
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| Список | pgsql-general |
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
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)
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)
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
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
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?
\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)
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> 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> 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 +
В списке pgsql-general по дате отправления: