Add schema support to PG driver in ogr2ogr

[sfrost@…]

Schemas in PostgreSQL are seperate namespaces with permissions per namespace (create or usage) and there's a 'search_path' variable that lists out the schemas to be searched for when no specific schema is given.

It's a lot like Oracle accounts except you don't actually have to have an account to have a schema, which is *very* nice. Generally each real user has his/her own schema though, except for role accounts which just use other schemas.

Schemas are tracked in Postgres via the pg_namespace system table.

Example:

Login as user sfrost; default search_path is '$user,public'. Now, there exists a 'sfrost' schema, so that's what I'm looking at when I first log in and do \d. I only see things in the 'sfrost' schema and the 'public' schema though. Now, say there's another schema called 'lerg', I can reference that schema directly by saying:

select * from lerg.table;

Assuming I have 'usage' rights on the schema 'lerg' and select rights on the table 'lerg.table'.

Now, what happened when I ran ogr2ogr was that it dumped the new tables into 'sfrost', my default schema. What I'd like to have happen is for the tables to be loaded into their own schema. I wouldn't expect ogr2ogr to actually create the schema, that's generally an operation reserved for the database superuser.

What I'd like is the ability to tell ogr2ogr what schema to use. Having support directly in ogr2ogr would mean that you could then specify a list of schemas to search for layers, instead of tables, which would make things easier both from an ogr2ogr point of view and from a user's point of view, I'd think.

Additionally, it doesn't appear possible to specify a schema to use in the PGconnectdb string.

From what I can tell ogr2ogr just needs to issue a 'set schema_path to %s' command where the %s is provided on the command-line to get basic schema support.

[warmerdam]

I'm not a C++ programmer, thus I think it's not a good idea to change the
ogr source. But my idea is to add a -lco schema=<schemaname>. Now you can
insert this <schemaname> into geometry_columns.f_schema_name for the layer
and you can extent the SQL inserts like insert into <schemaname>.<tablename>
(wkb_geometry,.....) values (wkb_geometry,....);

With best regards
 
Dr. Horst Düster

[sfrost@…]

I don't see it in the bug report, but I actually already implemented
what I suggested.  If you'd like a patch I can probably create one
against the latest gdal/ogr sources if they havn't changed overly much.
I thought I had sent in a patch for it previously actually...

Additionally, there is already a 'f_table_schema' in the
'geometry_columns' table, when I was using ogr2ogr it was to import new
tables and it doesn't seem like this would help that at all, I hate the
'geometry_columns' and 'spatial_ref_sys' tables being in the 'public'
schema and think they should be somewhere else (though I guess that's a
PostGIS issue, and really I dislike them in general but having them in
public and always showing up in my default \d really annoys me), and
this doesn't seem like it'd help with the foolishness of ogr2ogr going
through all of the tables in the database (something else my patch
fixes- it'll only look at the tables in the specified schema).

So, I guess feel free to add that if you'd like, but I don't think it'd
fix anything for me nor do I think it'd be justification to close this
bug..

Thanks,
       
        Stephen

[warmerdam]

Stephen, 

Yes, I would appreciate your patch.  Please attach to this report. 
Patches emailed to me, and too complex to apply at the moment I get
them often disappear into the depths of my email heap. 

I am still very confused about this whole schema issue.  As I see it there
are two useful things I could be looking at:

 1) Provide a way of specifying the schema into which a layer should be
    placed when it is created new.  This would (at least) set the 
    f_table_schema in geometry_columns, and I would presume also require
    some special option on the CREATE TABLE command. 

 2) Provide a way of restricting the layer enumeration to only use 
    some set of schemas (ie. the users and public?).  

Are there other issues at play?  Doesn't the current suggestion 
address issue (1)? 

[cavallini@…]

Any news about this bug? We also find the present ogr2ogr behaviour, of putting
everything in the "public" schema, quite unconvenient in any medium to large
Spatial Data Infrastructure.

All the best.
Paolo Cavallini
cavallini@faunalia.it
Emilia Venturato
venturato@faunalia.it

[Mateusz Łoskot]

(In reply to comment #3)
>  1) Provide a way of specifying the schema into which a layer should be
>     placed when it is created new.  This would (at least) set the 
>     f_table_schema in geometry_columns, and I would presume also require
>     some special option on the CREATE TABLE command. 

There are two solutions for this 1st case:

1. Put specified schema in command line as an active schema by setting search_path correctly:

SET search_path TO myschema,public

where myschema will become a kind of active schema, so every DDL SQL (ie. CREATE TABLE) will operate to the myschema.
Having also the public schema in the search_path will make it possible to query PostGIS metaschema tables (geometry_columns, spatial_ref_sys) without problems.

2. Second solution is to explicitly specify schema name in DDL commands:

CREATE TABLE myschema.mytable ...

The PostgreSQL manual confirms both solutions are feasible:

"If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema."

http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

By the way, currently, the OGRPGDataSource::CreateLayer operation does use PostGIS' AddGeometryColumn() accepting name of target schema.

>  2) Provide a way of restricting the layer enumeration to only use 
>     some set of schemas (ie. the users and public?).  

This is another aspect of schema support, and it's quite separate from the 1st one.

> Are there other issues at play?

I don't see any.

> Doesn't the current suggestion address issue (1)? 

According to my understanding of this report, yes, addressing the 1st issue is the case.

This would be the end of my analysis of the problem.

Now, summarizing current state of the PostgreSQL driver, it seem the SCHEMA support has been added already:

"The layer name may be of the form "schema.table"."

"Layer Creation Options (...) SCHEMA: Set name of schema for new table"

http://www.gdal.org/ogr/drv_pg.html

Is my understanding correct that this issue has been fixed? 
Or it's fixed but partially and needs some additional work?

[Mateusz Łoskot]

Frank, I'm taking over this bug according to our TODO roadmap.

[Mateusz Łoskot]

Frank,

Could you review my recent comments. I'd like to precise what is the current state of schema support in PG driver and what's not supported yet.

[Even Rouault]

All remaining functionnality should have been added in r16145 and tested in r16146