Opened 22 months ago

Closed 22 months ago

Last modified 21 months ago

#3490 closed defect (fixed)

Create perl script to add in the ALTER FUNCTION set path calls

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 2.2.2
Component: postgis Version: 2.2.x
Keywords: restore, materialized views, foreign tables, raster Cc:

Description (last modified by robe)

As discussed in several tickets.

PostGIS functions that call postgis functions fail when the schema that postgis is installed in is not in the search_path.

The generated script will look something like this - https://trac.osgeo.org/postgis/browser/trunk/postgis/postgis_functions_search_path.sql for will have all functions

Such a scenario happens in the following cases:

  1. During database restore - raster constraints rely on functions that call other functions, and as a result data does not come back if your data is not in the same schema as where postgis is installed
  1. Restore of spatial indexes that use ST_Transform, again if your data is not in same schema as postgis.
  1. Materialized views restore - views that use ST_Intersects etc. may not come back. So this is fairly serious. Again if not in same schema as postgis
  1. Foreign Tables. The Foreign tables as I recall are set to ignore search_path so a foregin table calling ST_Intersects etc will fail if the table is not in the same schema as where PostGIS is installed.

I'm working on a perl script to set the search_path setting of all functions. I plan to distribute this as part of PostGIS 2.2 (not as part of extension install). With an FAQ in the docs that people suffering from this issue need to

1) Install postgis first

2) Run this script

3) then restore their data

For PostGIS 2.3, I'd like to just include this as part of the CREATE EXTENSION install so I don't have to explain this to anybody ever again.

At least 5 people have complained to me about this already and I have suffered from it myself. Enough is enough.

Change History (11)

comment:1 Changed 22 months ago by robe

Owner: changed from pramsey to robe

comment:2 Changed 22 months ago by robe


This is a little annoying. I have the script all ready and put it as part of create extension. It doesn't error but also doesn't set search path of the functions. I checked and the script has it. Anyway going to check in my 2.3 work in a bit and back port the perl script and make file changes to generate the script for postgis and raster.


Scratch that comment. Was mistaken - got the wrong postgres instance running.

Last edited 22 months ago by robe (previous) (diff)

comment:3 Changed 22 months ago by robe

Okay this is a bit of a bummer. Looks like setting the search_path of function thwarts the use of spatial indexes. So I guess I can't blindly set it on all functions as I had hoped.

The issue seems to be that setting the search_path makes the SQL functions not inlined anymore. So guess it can't be used on any of the relationship functions which is a bummer since that's a lot of the area we have issue.

Last edited 22 months ago by robe (previous) (diff)

comment:4 Changed 22 months ago by robe

(In [14749]) script to set search_path of functions. Sadly this thwarts use of spatial indexes. Need to think about this more. References #3490

comment:5 Changed 22 months ago by robe

Description: modified (diff)

comment:6 Changed 22 months ago by robe

(In [14750]) Revise script generation to exclude SQL functions that are NOT STRICT. Integrate into testing Integrate into create extension References #3490 for PostGIS 2.3

comment:7 Changed 22 months ago by robe

Keywords: restore materialized views foreign tables raster added

Okay I think I have an 80% winner here. I revised the script to not put in search path on SQL functions that are not marked as STRICT.

C, plpgsql, and STRICT sql functions can't be inlined, so for those it's fine that search_path makes inlining not possible.

This should fix partly:

#3076 (it will handle ST_Distance for example, however it will not handle ST_DWithin in postgres fdw and materialized views.

#3012 - this will fix since ST_AsLatLonText will now have a function search path.

#3277 - this will be fixed too since non of the constraint functions are non strict and are for the most part plpgsql

#3485 - will also be fixed

Last edited 22 months ago by robe (previous) (diff)

comment:8 Changed 22 months ago by robe

Resolution: fixed
Status: newclosed

(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

comment:9 Changed 22 months ago by robe

(In [14753]) FAQ about materialized views sometimes not restoring in backup. References #3490

comment:10 Changed 21 months ago by robe

(In [14771]) Exclude all c functions from schema qual except ST_Transform we know calls spatial_ref_sys and is an issue in materialized views and spatial indexes. References #3490 for 2.2

comment:11 Changed 21 months ago by robe

(In [14772]) Exclude all c functions from schema qual except ST_Transform we know calls spatial_ref_sys and is an issue in materialized views and spatial indexes. References #3490 for 2.3 (also added pg_catalog since dumps have it - in theory it gets automatically added, but just in case)

Note: See TracTickets for help on using tickets.