[postgis-tickets] [PostGIS] #3975: ST_Transform references spatial_ref_sys without schema

[PostGIS]

#3975: ST_Transform references spatial_ref_sys without schema
------------------------+---------------------------
 Reporter:  javitonino  |      Owner:  pramsey
     Type:  defect      |     Status:  new
 Priority:  medium      |  Milestone:  PostGIS 2.4.3
Component:  postgis     |    Version:  2.4.x
 Keywords:              |
------------------------+---------------------------
 When trying to pg_restore a dump with some index that uses ST_Transform
 ({{{CREATE INDEX ON schema.tab (st_transform(geom,3857)); }}}) you end up
 getting errors that {{{spatial_ref_sys}}} does not exists.

 Steps to reproduce:
 {{{
 psql -U postgres
 create database db;
 \c db
 create extension postgis;
 create schema sc;
 create table sc.tab(geom geometry);
 create index on sc.tab (st_transform(geom,3857));
 insert into sc.tab values (st_setsrid(st_point(0,0), 4326));
 \q
 pg_dump -U postgres db -Fc > dump
 dropdb -U postgres db
 createdb -U postgres db
 pg_restore -U postgres -d db dump

 pg_restore: [archiver (db)] Error from TOC entry 3435; 1259 15521376 INDEX
 tab_st_transform_idx1 postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation
 "spatial_ref_sys" does not exist
 LINE 1: SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMI...
 }}}

 I think the issue is that the SQL query is run from C code, where it has
 not been qualified with @extschema@ like the rest of the code since 2.3,
 causing the query to fail when run from pg_restore (which sets the
 search_path to the schema of the objects it's restoring).

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3975>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.