BUG #15530: unable restore index on function from earthdistance contrib

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: sk(at)zsrv(dot)org
Subject: BUG #15530: unable restore index on function from earthdistance contrib
Date: 2018-11-30 11:46:01
Message-ID: 15530-3dc5f66205393dc0@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15530
Logged by: Sergei Kornilov
Email address: sk(at)zsrv(dot)org
PostgreSQL version: 11.1
Operating system: Linux Debian
Description:

Hello

We have some immutable language SQL functions in earthdistance contrib
module. So we can build indexes on this functions. But it is unable to
restore such indexes from dump due search_path restrictions in pg_dump.

Simple reproducer is:

SELECT pg_catalog.set_config('search_path', '', false);

CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;

CREATE TABLE public.points (
id integer NOT NULL,
name character varying(255) NOT NULL,
lng double precision NOT NULL,
lat double precision NOT NULL
);

CREATE INDEX points_coordinates ON public.points USING gist
(public.ll_to_earth(lat, lng));

With error on last line:
> ERROR: type "earth" does not exist
> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
> ^
> QUERY: SELECT
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
> CONTEXT: SQL function "ll_to_earth" during inlining

Seems to be broken in all supported versions. I checked on fresh HEAD, 11.1
and 10.6.
How can we fix this? Extension is marked as relocatable

regards, Sergei

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-11-30 13:46:26 BUG #15531: python-sqlobject shipped with EPEL 7 not compatible with PostgreSQL 9.6.11
Previous Message PG Bug reporting form 2018-11-30 10:17:35 BUG #15529: interval max value '178000000 years' in doc is not exact. It should 178956970.