Can't use earth_distance in a materialized view using postgres docker image

[kylehodgson]

Hi folks! I'm attempting to build a test harness where I can test my database migrations and pgsql functions with a docker container. My migration scripts do work well in my production DB, which is a timescale cloud instance. I first found this problem in the timescale docker image, and then tried the same thing against the vanilla postgres docker image and found that both acted the same way.

After I install earthdistance and its dependencies, I can run ll_to_earth and earth_distance fine as long as I'm not trying to put it in a mat view ... am I missing something, or is something wrong with the docker image?

Steps to reproduce

• install vanilla postgres docker image

docker pull postgres
docker run -d --name vanillapg -p 65432:5432 -e POSTGRES_PASSWORD=password postgres 
psql -d "postgres://postgres:password@localhost:65432/postgres"

• install earthdistance and cube:

CREATE EXTENSION IF NOT EXISTS cube CASCADE;
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;

• check that they are installed with \dT (display types) and \dx (display extensions):

postgres=# \dT
                                              List of data types
 Schema | Name  |                                         Description                                         
--------+-------+---------------------------------------------------------------------------------------------
 public | cube  | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOAT-1, FLOAT-2, ..., FLOAT-N)'
 public | earth | 
(2 rows)

postgres=# \dx
                                    List of installed extensions
     Name      | Version |   Schema   |                         Description                          
---------------+---------+------------+--------------------------------------------------------------
 cube          | 1.5     | public     | data type for multidimensional cubes
 earthdistance | 1.1     | public     | calculate great-circle distances on the surface of the Earth
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

• check if earth_distance is funcitonal (it is):

select earth_distance(ll_to_earth(43.6425195558155, -79.38697097158408),  ll_to_earth(48.858281803005454, 2.2947012351358174));
  earth_distance   
-------------------
 6004643.286160037
(1 row)

• try to use it in a mat view, and receive an error:

create materialized view test as select earth_distance(ll_to_earth(43.6425195558155, -79.38697097158408),  ll_to_earth(48.858281803005454, 2.2947012351358174));
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 startup

Other research

When chasing type 'earth' does not exist, you get lots of posts about pg_dump and search paths, but a) I'm not restoring from a pg_dump, and b) my search path does include public (which is where earthdistance is installed as per \dT):

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

[LaurentGoderre]

Using the following fixes the issue but I don't understand why it is required

ALTER FUNCTION ll_to_earth SET search_path = public;
ALTER FUNCTION earth_distance SET search_path = public;

CREATE MATERIALIZED VIEW test1 AS SELECT earth_distance(ll_to_earth(43.6425195558155, -79.38697097158408),  ll_to_earth(48.858281803005454, 2.2947012351358174));
SELECT 1
postgres=# select * from test1;
  earth_distance   
-------------------
 6004643.286160037
(1 row)

[LaurentGoderre]

I believe a recent change in the extension might have caused this: postgres/postgres@969bbd0

/cc @tglsfdc

[kylehodgson]

that helps, thanks @LaurentGoderre ! Put that in my migration scripts and I'm past the blocker. Much appreciated.

[yosifkit]

This does not look like something we can fix via the image. Closing.