[postgis-users] Re: Restoring db with indexesusingPostGIS functions

[Obe, Regina]

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)),
    CONSTRAINT enforce_srid_the_geom CHECK ((public.srid(the_geom) =
4269))
);


ALTER TABLE census_ma_2006.arealandmarks OWNER TO postgres;

So well duh - if you know what schema a table is supposed to be in, then
please reference it in the CREATE TABLE statement.  I did when I created
the table.

Thanks,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon Corporation
Sent: Wednesday, October 01, 2008 6:28 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Re: Restoring db with indexesusingPostGIS
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
Leahy
Sent: Tuesday, September 30, 2008 11:32 PM
Cc: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Re: Restoring db with indexes usingPostGIS
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.

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
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
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.