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

[Mike Leahy]

Regina,

I'm using 8.3.3 (and 8.3.4 on another machine).  I'm not quite sure this 
is a bug with pg_dump...though maybe it is.

 From what I can tell, the reason that constraints are not a problem 
(i.e., those typically created by the addgeometrycolumn() function) are 
because they generally use geometrytype(), not st_geometrytype() - the 
latter calls the former.  So in my dump file, for the constraints, I'll 
see something like this in a table's definition:

CONSTRAINT enforce_geotype_extent CHECK (((public.geometrytype(extent) = 
'POLYGON'::text) OR (extent IS NULL))),

This is fine.  But for the indexes that I had created, I have the 
following pairs of statements, where 'mytable' is in 'myschema':

SET search_path = myschema, pg_catalog;
CREATE INDEX mytable_geomtype_idx ON mytable USING btree 
(public.st_geometrytype(geom));

It seems that pg_dump knows to put the 'public' schema in front of the 
function names in the constraint or index statements.  However, the 
'CREATE FUNCTION' statement for the st_geometrytype() function does not 
refer to public.geometrytype(), but only geometrytype().  I think this 
is where the problem lies - because once the search path is set to 
something other than 'public', then executing public.st_geometrytype() 
causes an error since it can no longer find geometrytype() in the 
current schema.

To make a long story short, if I edit the st_geometrytype() function in 
my dump file so that it points to public.geometrytype(), then the 
problem I encountered goes away.

If we assume pg_dump is at fault here, then either it's strategy would 
have to be rewritten so that it explicitly includes schema names instead 
of setting the search path throughout the output script (I don't imagine 
I could make a persuasive argument for this to the PostgreSQL 
developers), or it has to somehow know to add the 'public' schema to any 
references to functions inside other functions, such as in 
st_geometrytype() (should it actually be able to do this?).

Alternatively, PostGIS could have explicit schema references where any 
functions are referenced inside other functions.  My guess is this might 
be the simpler solution.  Though for my case, now that I understand the 
problem, it's easy to work around.

Thanks for your help,

Mike

> 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