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
