Re: pg_upgrade fails saying function unaccent(text) doesn't exist

\dFd unaccent
List of text search dictionaries
Schema | Name | Description
--------+----------+-------------
public | unaccent |
(1 row)

\dFd+ unaccent
List of text search dictionaries
Schema | Name | Template | Init options | Description
--------+----------+-----------------+--------------------+-------------
public | unaccent | public.unaccent | rules = 'unaccent' |
(1 row)

Cheers,
Gulli

On Thu, Aug 30, 2018 at 1:48 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(at)momjian(dot)us
> > <mailto:bruce(at)momjian(dot)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(at)momjian(dot)us <mailto:bruce(at)momjian(dot)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(dot)klaver(at)aklaver(dot)com
>
>