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

[Mike Leahy]

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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mike
> Leahy
> Sent: Tuesday, September 30, 2008 4:00 PM
> To: postgis-users at postgis.refractions.net
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
>