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

[PostGIS]

#3012: Calling  ST_AsLatLonText  in matviews causes unrestorable pg_dump
---------------------------+---------------------------
  Reporter:  jeffcasavant  |      Owner:  robe
      Type:  defect        |     Status:  closed
  Priority:  medium        |  Milestone:  PostGIS 2.2.2
 Component:  postgis       |    Version:  2.1.x
Resolution:  fixed         |   Keywords:
---------------------------+---------------------------

Comment (by robe):

 On closer inspeaction, I think this function was rewritten to use default
 args, so it actually doesn't need this patch anyway - the mat view came
 back without it.

 For general MatView issues with other functions that couldn't be redone to
 not call other functions, these would be the steps -- I'm using
 ST_Distance for geography since that still calls other functions.

 Here are the steps I used to replicate:


 {{{
 CREATE EXTENSION postgis;
 CREATE SCHEMA data;

 CREATE MATERIALIZED VIEW data.mv_disttest AS
 SELECT ST_Distance(ST_POint(40,50)::geography, ST_Point(50,50)::geography)
 As dist;
 }}}
 Backup the data and try to restore the database.

 If you are running PostGIS 2.3.0, your materialized view will come back
 fine.

 If 2.2 something - you'll get this error during restore:

 {{{
 pg_restore: processing data for table "public.spatial_ref_sys"
 pg_restore: [archiver (db)] Error from TOC entry 3299; 0 427143 TABLE DATA
 spatial_ref_sys postgres
 pg_restore: [archiver (db)] COPY failed for table "spatial_ref_sys":
 ERROR:  duplicate key value violates unique constraint
 "spatial_ref_sys_pkey"
 DETAIL:  Key (srid)=(5013) already exists.
 CONTEXT:  COPY spatial_ref_sys, line 1
 pg_restore: creating MATERIALIZED VIEW DATA "data.mv_disttest"
 pg_restore: [archiver (db)] Error from TOC entry 3423; 0 428293
 MATERIALIZED VIEW DATA mv_disttest postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation
 "mv_disttest" does not exist
 }}}

 So you'll need to do this first


 {{{
 CREATE EXTENSION postgis;
 \i share\contrib\postgis-2.2\postgis_proc_set_search_path.sql
 }}}

 Then restore your data.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3012#comment:9>
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.