Postgis-functions in Materialized views fail in Postgres 17 due to not fully qualified search path

[maxbo]

Since Postgres 17, While REFRESH MATERIALIZED VIEW is running, the search_path is temporarily changed to pg_catalog, pg_temp.

Materialized Views, that use a directly some PostGIS-Functions fail, if these functions call other PostGIS-Functions without a full schema qualifier.

For example: public.st_value(raster, integer, integer, boolean)

calls

st_value($1, 1::integer, $2, $3, $4)

And if this is done during a "REFRESH MATERIALIZED VIEW", st_value(raster, integer, integer, integer, boolean) cannot be found.

Another example: public.st_worldtorastercoordx(raster, geometry); refers to ST_x, ST_y, ST_SRID and ST_geometrytype which cannot be found during a "REFRESH MATERIALIZED VIEW".

The issue could be fixed by changing the postgis-functions that call other postgis-functions by adding the schema:

IF ( public.ST_geometrytype(pt) != 'ST_Point' ) THEN

RAISE EXCEPTION 'Attempting to compute raster coordinate with a non-point geometry';

END IF; IF public.ST_SRID(rast) != public.ST_SRID(pt) THEN

RAISE EXCEPTION 'Raster and geometry do not have the same SRID';

END IF; SELECT columnx INTO xr FROM public._ST_worldtorastercoord($1, public.ST_x(pt), public.ST_y(pt)); RETURN xr;

[mboeringa]

Just wanted to add that I am running into similar issues with a "CREATE MATERIALIZED VIEW" statement, see the error message below that indicates the 'spatial_ref_sys' table cannot be found (but it is installed in the proper 'public' schema location where it always resided). This is PG17 / PostGIS 3.5.0. If I swap out "CREATE MATERIALIZED VIEW" with "CREATE TABLE" in the original SQL statement (not included here), the exact same SQL will work.

SQL Error [42P01]: ERROR: relation "spatial_ref_sys" does not exist
LINE 2:  FROM spatial_ref_sys WHERE srid=public.ST_SRID($1);
              ^
QUERY:  SELECT public.postgis_transform_geometry($1, proj4text, $2, 0)
	FROM spatial_ref_sys WHERE srid=public.ST_SRID($1);
CONTEXT:  SQL function "st_transform" during inlining

[Regina Obe <lr@…>]

In b5fbbdd/git:

MISSING SCHEMA qualifications

• Schema qualify all uses of spatial_ref_sys and postgis functions in postgis.sql.in script
• Schema qualify postgis and raster functions

and types in rtpostgis.sql.in

References #5790 for PostGIS 3.6.0

[Regina Obe <lr@…>]

In 38a47dd1/git:

MISSING SCHEMA qualifications

• Schema qualify all uses of spatial_ref_sys and postgis functions in postgis.sql.in script
• Schema qualify postgis and raster functions

and types in rtpostgis.sql.in

Closes #5790 for PostGIS 3.5.1

[Paul Ramsey <pramsey@…>]

In db17508/git:

MISSING SCHEMA qualifications

• Schema qualify all uses of spatial_ref_sys and postgis functions in postgis.sql.in script
• Schema qualify postgis and raster functions

and types in rtpostgis.sql.in

References #5790 for PostGIS 3.6.0

[robe]

Oops forgot to push this to 3.4. Will reclose after I do that.

[Regina Obe <lr@…>]

In a914ff7/git:

MISSING SCHEMA qualifications

Schema qualify all uses of spatial_ref_sys and postgis functions in postgis.sql.in script
Schema qualify postgis and raster functions and types in rtpostgis.sql.in

Closes #5790 for PostGIS 3.4.4

[Regina Obe <lr@…>]

In 3779b5ae/git:

Fix typo
Add to NEWS
Closes #5790 for PostGIS 3.4.4