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