Re: BUG #15112: Unable to run pg_upgrade with earthdistance extension

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.