Calling ST_AsLatLonText in matviews causes unrestorable pg_dump

[jeffcasavant]

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 (​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 #2485.

[jeffcasavant]

Using Postgres 9.4 with PostGIS 2.1

(postgresql-9.4-postgis-2.1 from deb ​http://ppa.launchpad.net/ubuntugis/ubuntugis-unstable/ubuntu trusty main)

[jeffcasavant]

Please view the ​ongoing discussion.

[robe]

Yes it is related to #2485. We can't solve the problem generically short of forcing everyone to install postgis in a specific schema and your assessment is not quite right. Calling PostGIS functions in matviews is fine as long as the function you call doesn't call another PostGIS function.

In your particular case, if you replace your view definition reference

ST_AsLatLonText(geom)

with

 ST_AsLatLonText(geom,'')

then I bet it restores fine.

When views are compiled, they actually do convert everything to schema qualified. So the issue is that ST_AsLatLonText(geom) does an unqualified schema call to ST_AsLatLonText(geom,).

In your case with ST_AsLatLonText the issue is we have two functions:

st_aslatlontext(geometry, text) and ST_AsLatLonText(geometry). So the one you are using calls the other one which is not schema qualified thus the error.

We can fix this particular situation by dropping one of the functions and just have a default arg for the second. I'll patch that in for 2.2. I can't patch it in 2.1 since I would be removing a function definition.

strk — Why did we ever define two functions for ST_AsLatLonText? when in my mind one with a default arg would have been sufficient, or was this before we switched to default args?

[robe]

By your assessment was not quite right, I meant to say that the title was a little misleading. This is an issue only with PostGIS functions that call other PostGIS functions, and not with all PostGIS functions as the original title had suggested. Sadly we do have a lot of these it seems and as people start using materialized views more and more, it will become more of an issue.

Luckily it's not an issue with regular views since those don't try to run the functions in the view. One thing I haven't tried is defining a materialize view with NO DATA. I'm not sure if pg_dump maintains that setting. I'm guessing it does. That said — another work around would be to do:

CREATE MATERIALIZED VIEW vw_mat_postgis
    AS  SELECT ....

    WITH NO DATA;

and then after restore run a

REFRESH MATERIALIZED VIEW vw_mat_postgis;

[robe]

Refer to #3490

[robe]

(In [14752]) Script to set search path for raster and postgis functions To fix database restore issues and materialized views Closes #3490 Closes #3485 Closes #3277 Closes #3012

[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.