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

[Mark Cave-Ayland]

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.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063