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

[Paragon Corporation]

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