PG: active_schema incompatible with PostGIS databases not using public schema

[rulus]

The usage of ogr2ogr's active_schema option is incompatible with PostGIS databases using a schema other than 'public' for their functions and tables.

Using active_schema=<active_schema> sets the users' search_path to <active_schema>,public, overriding the search_path set in the database: (ogr/ogrsf_frmts/pg/ogrpgdatasource.cpp:533)

if (strcmp(osActiveSchema, "public") != 0)
    {
        CPLString osCommand;
        osCommand.Printf("SET search_path='%s',public", osActiveSchema.c_str());
        PGresult    *hResult = OGRPG_PQexec(hPGConn, osCommand );

It seems to me it would be better to prepend the <active_schema> to the users' search_path, although this needs to be tested with other use cases (f.ex. overwriting?).

Steps to reproduce:

• Create a new PostgreSQL database <dbname>
• Install PostGIS (tested with 2.1) as follows:

  create extension postgis;
  create schema postgis;
  alter extension postgis set schema postgis;
  alter database <dbname> set search_path = "$user",public,postgis;
  

• Create a new schema for the data and load some data into it.

Try exporting the layer using the active_schema option:

ogr2ogr.exe --debug on -f SQLite -dsco SPATIALITE=yes C:\Users\USERNAME\db.sqlite PG:"dbname=DBNAME host=HOST port=5432 user='USER' password='PASSWORD' sslmode=require active_schema=bezwaren_app" oude_bezwaren
PG: DBName="'DBNAME'"
PG: PostgreSQL version string : 'PostgreSQL 9.3.4 on i386-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit'
ERROR 1: ERROR:  relation "geometry_columns" does not exist
LINE 1: ...attnum as attnum FROM pg_class c, pg_namespace n, geometry_c...

A workaround is to avoid the active_schema option, declare the schema of the table explicitly and use the nln option to strip the schemaname again:

ogr2ogr.exe --debug on -f SQLite -dsco SPATIALITE=yes C:\Users\USERNAME\db.sqlite PG:"dbname='DBNAME' host=HOST port=5432 user='USER' password='PASSWORD' sslmode=require" bezwaren_app.oude_bezwaren -nln oude_bezwaren
PG: DBName="'DBNAME'"
PG: PostgreSQL version string : 'PostgreSQL 9.3.4 on i386-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit'
PG: PostGIS version string : '2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
OGR: OGROpen(PG:dbname='DBNAME' host=HOST port=5432 user='USER' password='PASSWORD' sslmode=require/0000000002E4E640) succeeded as PostgreSQL.
SQLITE: SpatiaLite v4 DB found !
PG: Primary key name (FID): ogc_fid
PG: Using column 'ogc_fid' as FID for table 'oude_bezwaren'
OGR2OGR: 1976 features written in layer 'oude_bezwaren'
SQLITE: Error no such table: layer_statistics
OGR: Unloading VirtualOGR module
PG: 1976 features read on layer 'bezwaren_app.oude_bezwaren'.

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.