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

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 +