headers
Mike Leahy | 30 Sep 04:46

Restoring db with indexes using PostGIS functions

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
(Continue reading)

Permalink | Reply |
headers
Mark Cave-Ayland | 30 Sep 12:01

Re: Restoring db with indexes using PostGIS functions

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
(Continue reading)

Permalink | Reply |
headers
Mike Leahy | 30 Sep 22:00

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 )
(Continue reading)

Permalink | Reply |
headers
Paragon Corporation | 30 Sep 22:56
Favicon

RE: Re: Restoring db with indexes using PostGIS functions

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
(Continue reading)

Permalink | Reply |
headers
Mike Leahy | 1 Oct 01:37

Re: Re: Restoring db with indexes using PostGIS 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.
>
(Continue reading)

Permalink | Reply |
headers
Paragon Corporation | 1 Oct 02:57
Favicon

RE: Re: Restoring db with indexes usingPostGIS functions

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?
(Continue reading)

Permalink | Reply |
headers
Mike Leahy | 1 Oct 05:31

Re: Re: Restoring db with indexes using PostGIS functions

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.
(Continue reading)

Permalink | Reply |
headers
Paragon Corporation | 1 Oct 12:28
Favicon

RE: Re: Restoring db with indexes usingPostGIS functions


Mike,

I actually don't know why pg_dump fiddles with setting search paths in the
first place.  I mean it should load the system in the environment the
database lives in because obviously functions, indexes and what-not work in
the context of the normal search path state of the database.  Though I
haven't checked to see what it does if you do a compressed dump.  I suppose
I could dump to tar extract and see if its doing the same thing.

I find it kind of annoying that when I restore a database, it doesn't seem
to restore the search_paths I set specifically for that database.  So I end
up having to remember to do that (or maybe I am doing something wrong here).

Forcing people to put postgis functions in its own specifically named schema
is a breaking change and not to mention a bit annoying granted
organizationally better.  I mean other modules would have similar issues.  I
as a user like to control which schema I install 3rd party functions and if
3rd party functions have to assume that their functions will sit in some
specifically named schema, that seems like a bit of an unnecessary burden,
though I suppose it would prevent collision of functions between third-party
providers e.g. ESRI.

Just my two cents.

Regina

-----Original Message-----
From: postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Mike
(Continue reading)

Permalink | Reply |
headers
Obe, Regina | 1 Oct 13:06
Favicon

RE: Re: Restoring db with indexesusingPostGIS functions

Mike,

FYI  I just did a tar backup and looked at the restore.sql

It does the same search_path thing.  I'm still considering this a
pg_dump bug because to me its inconsistent behavior.

It seems it needs to set the search_path only because when it creates
tables it doesn't explicitly reference the schema in the create table
statement, but wait a minute later on when setting the owner and what
not it explicitly references the schema  -- here is a snippet

SET search_path = census_ma_2006, pg_catalog;
--
-- Name: arealandmarks; Type: TABLE; Schema: census_ma_2006; Owner:
postgres; Tablespace: 
--

CREATE TABLE arealandmarks (
    ogc_fid integer NOT NULL,
    the_geom public.geometry,
    module character(8),
    file character(5),
    state numeric(2,0),
    county numeric(3,0),
    cenid character(5),
    polyid numeric(10,0),
    land numeric(10,0),
    CONSTRAINT enforce_dims_the_geom CHECK ((public.ndims(the_geom) =
2)),
(Continue reading)

Permalink | Reply |

Gmane