Mike, I wonder if it's a bug in PostgreSQL 8.3. I'm running both 8.2.5 and 8.3. 8.2.5 on a Windows 2003 and 8.3.3 on Linux. Which point version of 8.3 are you running? I vaguely recall a couple of fixes being done to the dump restore between 8.3.0 and 8.3.3, but I could be wrong. Anyrate sounds like a pg_dump bug.
I wouldn't notice your particular problem since all my indexes are gist indexes which are in pg_catalog anyway and I don't think I have any btree indexes I can think of that use public functions. They all use pg_catalog functions or no functions. Its strange constraints are not an issue and indexes are. I would have thought the same problem would arise. Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Leahy Sent: Tuesday, September 30, 2008 7:38 PM To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Re: Restoring db with indexes usingPostGIS functions Hello Regina, There's no problems with table constraints or anything like that...the only issue is with the indexes that use st_geometrytype. I can give the compressed format a try...what version of PostgreSQL are you using? I've got 8.3 on the machines I'm using, and I think the dump format has changed slightly from one version to the next (though I'm not 100% sure about that). I don't normally set the search path either. As you recommend, I explicitly reference the schema.table names in all of the SQL that I've been writing myself. It's only when restoring the database that I've encountered this. Mike Paragon Corporation wrote: > Mike, > > I'm a bit puzzled about your problem. Are you saying that you use > multiple schemas for tables and have your postgis functions in public > and when you restore your indexes they are not being created because > of the search path thing. I use multiple table schemas too and as far > as I can remember all my spatial constraints and indexes come back and > I don't see any prefixing of public in the functions. > > Although sounds like you are dumping to sql format and I always dump > to compressed format. Perhaps they behave differently. > > Is it just your indexes that are missing or the constraints as well? > Your indexes seem a little out of the ordinary, but I would expect you > would have the same issue With postgis generated constraints. Do your > table indexes actually show without the schema qualification on them > when you look at them. > > Mine always show as > CREATE INDEX sometable_idx > ON someschema.sometable > USING btree > (ST_GeometryType(the_geom)); > > But then again I always explicitly put in the schema prefix when > creating indexes. How would you be able to create the indexes > otherwise unless you are setting the search path before you create > your indexes > > Set search_path = schemaname > CREATE INDEX ... ON sometable > > Is that how you create your indexes or do you explicitly reference the > table schema name in the CREATE? > > > > Hope that helps, > Regina > > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Mike Leahy > Sent: Tuesday, September 30, 2008 4:00 PM > To: [email protected] > Subject: [postgis-users] Re: Restoring db with indexes using PostGIS > functions > > 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 > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
