[postgis-users] Schemas in 7.3

Carl Anderson candrsn at mindspring.com
Thu Feb 6 16:54:47 PST 2003

In Pg 7.3.x

schema's can hold anything that can be created
     tables, views, functions, types, operators, aggregates,....

The way I rolled out PostGIS was to put the installation into the 
"public" schema. for those of you not using Pg 7.3 yet the "public" 
schema acts as a global objects holder, a bit like the "SYS" schema in 
Oracle, except in Pg7.3 there is an actual search_path (set 
search_path=schema1,schema2,schema3,public;) that acts like a directory 
path when referencing objects without an explicit schema reference.  By 
convention public should always be post pended to the search_path so 
that the common global stuff is available.

I hand edited f_table_schema to be the actual schema name (not the db 
name the way create geometry columns does).  I hand edited get_srid to 
be aware that
a geometry table might not be in the default schema (namespace) search 
Of course update_geometry_columns and fix_geometry_columns needed to be 
changed also.

This way you can
    select parcel_id from tax.parcels, zoning.current where 
current.the_geom && parcels.the_geom and current.case = 'Z2000-0350';

If zoning and tax had installed PostGIS into their own schema's each 
would hold a unique datatype, each named geometry.  Unless a cast was 
built the above statement would not work as there would be no way to 
convert between the two distinct datatypes geometry (the datatype 
tax.geometry vs the datatype zoning.geometry).

As a corollary,  I might want to install a new version of PostGIS into 
a schema so that I could test it.  I did not say the non explicit 
object references stop at the first found.  so a local PostGIS 
installation would be incompatible with but would not conflict with a 
PostGIS in the public schema.

So I hope think that user should be recommended to install PostGIS into 
th public schema but not forced to. And that functions should become 
schema aware.

I am willing to help with plpgsql or C coding if that is desired.


On 2003.02.06 15:35 Paul Ramsey wrote:
> 7.3 is starting to stabilize now (7.3.2 was just released) so more 
> and more people are going to be using it, and by extension using 
> PostGIS with it.
> One of the new 7.3 features is support for "schemas". Schemas 
> essentially partition a single database into multiple namespaces, 
> which can have different priviledge levels. So it becomes possible to 
> have multiple "different" databases in the same simple database. The 
> reason to do this is so that "related" applications can share data 
> while keeping their application-specific data separate.
> So the "tax collection" branch can have all their tax table, but have 
> access to a general "citizen registry" table in another schema which 
> they have only read access to.
> Basically schemas are a means of managing complex relationships 
> between different users and user groups. Often PgSQL administrators 
> would solve the problem by partitioning different groups into 
> different databases. But what if the groups wanted to share data? It 
> is not possible ot join tables across databaes. Schemas are the 
> answer.
> Right now, PostGIS gets loaded into the schema which psql has 
> connected to. If you are loading as the 'postgres' user, then your 
> PostGIS support functions and objects will get loaded into the system 
> schema. If you are loading as another user, then they'll end up 
> somewhere else (like your personal schema, for example). So, the 
> question is: where should PostGIS support get loaded? Should we 
> enforce any particular loading location, or just "let it all hang 
> out" like we are doing now? The trouble with the current way of doing 
> things is that it is possible for people to mess up their install in 
> subtle ways, by loading PostGIS into some non-system schema which 
> ordinary users have no (or partial) access to.
> Food for healthy thoughts,
> Paul
>       __
>      /
>      | Paul Ramsey
>      | Refractions Research
>      | Email: pramsey at refractions.net
>      | Phone: (250) 885-0632
>      \_
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
Carl Anderson
candrsn at mindspring.com

New Hampshire tells us "common sense for all"
     where can I get mine?

More information about the postgis-users mailing list