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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
