From: Noah Misch <noah@leadboat.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: keiko713@gmail.com, pgsql-bugs@lists.postgresql.org, PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: BUG #15112: Unable to run pg_upgrade with earthdistance extension
Date: Wed, 4 Apr 2018 23:59:57 -0700
Message-ID: <20180405065957.GA2276534@rfd.leadboat.com> (raw)
In-Reply-To: <20180402202402.GA24073@momjian.us>
References: <152106914669.1223.5104148605998271987@wrigleys.postgresql.org>
<20180330205229.GS8476@momjian.us>
<20180402202402.GA24073@momjian.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noah@leadboat.com, bruce@momjian.us, pgsql-hackers@postgresql.org
Subject: Re: BUG #15112: Unable to run pg_upgrade with earthdistance extension
In-Reply-To: <20180405065957.GA2276534@rfd.leadboat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox