[postgis-users] Re: Restoring db with indexes using PostGIS functions

[Mike Leahy]

Mark Cave-Ayland wrote:
> Mike Leahy wrote:
> > Hello list,
> > 
> > I ran into a problem today when restoring a database of mine - I'm not 
> > sure if this is anything new, or if I'm doing something I shouldn't be 
> > doing.  I have in my db a variety of tables that are stored in separate 
> > schemas.  Some of these tables have geometry columns that I am using to 
> > store variable geometry types, but since most of the queries on these 
> > tables generally focus on one geometry type at a time, I added indexes 
> > to them as follows:
> > 
> > CREATE INDEX mytable_geomtype_idx ON schemaname.mytable USING btree 
> > (st_geometrytype(geom));
> > 
> > However, when I dump the database, the resulting sql sets search paths 
> > rather than explicitly naming schema.table in each statement. 
> > Effectively, this is what happens when the dumped sql is loaded into a 
> > new database:
> > 
> > mydb=# SET search_path = schemaname, pg_catalog;
> > SET
> > mydb=# CREATE INDEX mytable_geomtype_idx ON mytable USING btree 
> > (public.st_geometrytype(geom));
> > ERROR:  function geometrytype(public.geometry) does not exist
> > LINE 1: SELECT  geometrytype( $1 )
> >                 ^
> > HINT:  No function matches the given name and argument types. You might 
> > need to add explicit type casts.
> > QUERY:  SELECT  geometrytype( $1 )
> > CONTEXT:  PL/pgSQL function "st_geometrytype" line 6 during statement 
> > block local variable initialization
> > mydb=#
> > 
> > It seems that setting the search path has undesirable effects with the 
> > st_geometrytype function (I don't know if the same happens with other 
> > functions).  I suspect 'geometrytype( $1 )' would have to be substituted 
> > with 'public.geometrytype( $1 )' for this to work.
> > 
> > Is this just something I should be aware of?  It's not a show-stopper 
> > for me at the moment, but it means that I'd have to manually recreate 
> > any indexes like the one above if performance became an issue after 
> > restoring the database (not in my current situation, but maybe down the 
> > road).
> > 
> > Regards,
> > Mike
> 
> 
> Hi Mike,
> 
> If you are using newer PostgreSQL releases, there was an issue a while 
> back where the use of search_path within stored procedures was 
> restricted to stop people from overriding in-built stored procedures. 
> Otherwise it could simply be that the dump ordering is wrong, and 
> altering the restore order so that geometrytype() is restored *before* 
> ST_geometrytype should resolve the issue.
> 
> 
> HTH,
> 
> Mark.

Hello Mark,

I don't think it is a problem with the order of restoration of the 
geometrytype() vs. st_geometrytype() functions, because those are loaded 
and working fine before anything else happens in the restore script. 
The problem is when indexes are being restored for the tables inside my 
schemas - the dump script will set the search path for one schema, 
create the indexes, then repeat for any other schemas.  It does this 
throughout from what I can tell.

This problem, from what I can tell, is not limited to the restore 
scripts.  The problem occurs just by setting the search path to 
something other than 'public', then calling public.st_geometrytype(), 
which in turn calls geometrytype().  Since st_geometrytype() isn't 
calling public.geometrytype(), it can't find the function in the current 
search path.  At least, this is what I think the issue is.

The only way for me to get around this as things are now would be to 
edit the restore script so that it uses 'public' for the search path, 
then rewrite all the create index statements to refer to schema.table.

Mike