[postgis-tickets] [PostGIS] #3012: Calling PostGIS functions in matviews causes unrestorable pg_dump

[PostGIS]

#3012: Calling PostGIS functions in matviews causes unrestorable pg_dump
--------------------------+-------------------------------------------------
 Reporter:  jeffcasavant  |       Owner:  pramsey
     Type:  defect        |      Status:  new    
 Priority:  medium        |   Milestone:         
Component:  postgis       |     Version:  2.1.x  
 Keywords:                |  
--------------------------+-------------------------------------------------
 I have a matview which sets one column using ST_AsLatLonText(geometry).  I
 dumped my database using pg_dumpall and when attempting to load it with
 psql got the error:

 {{{
 psql:dump.sql:7730: ERROR:  function ST_AsLatLonText(public.geometry,
 unknown) does not exist
 LINE 1:  SELECT ST_AsLatLonText($1, '')
                 ^
 HINT:  No function matches the given name and argument types. You might
 need
 to add explicit type casts.
 QUERY:   SELECT ST_AsLatLonText($1, '')
 CONTEXT:  SQL function "st_aslatlontext" during inlining
 }}}

 Thinking this was a pg_dump bug, I submitted bug #12465 to the pgsql bug
 tracker and was told that the issue was with the function definition
 itself rather than pg_dump ([http://www.postgresql.org/message-
 id/20150108212429.11502.18220 at wrigleys.postgresql.org/ discussion
 thread]).  Note that ST_AsLatLonText(geometry) function calls
 ST_AsLatLonText(geometry, text) without specifying its schema. The
 suggested solution (for function A calling B) is:

 "You could fix it by schema-qualifying b in the text of a,
 or by adding a 'SET schema_path' clause to a."

 Translating this - ST_AsLatLonText(geometry) should call
 public.ST_AsLatLonText(geometry, text) rather than just
 ST_AsLatLonText(geometry, text).

 This is also occurring in other functions which are implemented similarly
 (ST_Distance, etc).

 My workaround for the time being is to process the dump with:

 {{{
 sed -E 's/(search_path = .+);/\1, public;/'
 }}}

 which adds public to the end of the search path every time it's set.

 It was suggested to me on freenode/#postgis that this may be related to
 [http://trac.osgeo.org/postgis/ticket/2485/ #2485].

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3012>
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.