On Mon, Apr 02, 2018 at 04:24:02PM -0400, Bruce Momjian wrote: > > I am not sure we can fix this without requiring people to drop and > > recreate such indexes. However, I am even at a loss in how to fix the > > CREATE FUNCTION to reference a cast in the same schema as the function, > > in this case 'public'. We can rewrite the cast to not use :: and use a > > function call with schema qualification. e.g. public.earth(), but how do > > we know what schema that is in, i.e. what if the extension is loaded > > into a schema other than public?
The task is to convert it to being a non-relocatable extension that uses @extschema@, like here: https://www.postgresql.org/docs/devel/static/extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE Keiko Oda, you can hack around this by modifying the problematic extension function in each database: CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8) RETURNS public.earth LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians($1))*cos(radians($2))),public.earth()*cos(radians($1))*sin(radians($2))),public.earth()*sin(radians($1)))::public.earth'; There's no need to drop and recreate indexes. However, if the table is small and nothing depends on the index, that is an easy workaround that one can employ right now. (Drop before upgrade and re-create after upgrade.) > > FYI, earthdistance is certainly not the only case of this problem. True; I've been expecting a report like this for contrib/xml2 especially.
