id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc 1572,PG incorrectly recognizes layers from non-default schema,Mateusz Łoskot,Mateusz Łoskot,"'''NOTE:''' This issue was reported on the gdal-dev list by Richard Matsunaga: [http://lists.maptools.org/pipermail/gdal-dev/2007-April/012549.html Problem with ogr2ogr and PG] ---- Below, I give a code-level explanation of the problem. * '''Assumptions''' 1. There is a custom schema in PostgreSQL database, ie. '''myschema''' 2. There is a table (let's say '''mypoints''') already imported under custom schema {{{ ogr2ogr -update -f PostgreSQL PG:""dbname=test"" mypoints.shp \ -lco SCHEMA=myschema }}} 3. The goal is to (re)import data into '''mypoints''' layer in '''append''' or '''overwrite''' mode: {{{ ogr2ogr -append -update -f PostgreSQL PG:""dbname=test"" mypoints.shp \ -lco SCHEMA=myschema }}} * '''Problem''' The command described in the point 3 fails. * '''Why?''' There are two main reasons: 1. OGR does not set a custom schema to be used as current schema. 2. Implied by the 1st, the layer name is generated by concatenation schema and table names: ''myschema.mypoints'', in [http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp#L225 ogrpgtablelayer.cpp:225] The concatenation in point 2 in turn causes that the ogr2ogr does not find any destination layer to append or overwrite, in [http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogr2ogr.cpp#L605 ogr2ogr.cpp:605], because compared values are: '''myschema.mypoints''' vs '''mypoints''' Finally, because no layer can be found for updated, ogr2ogr [http://trac.osgeo.org/gdal/browser/trunk/gdal/ogr/ogr2ogr.cpp#L646 tries to create new layer] using the same name what fails because the name already exists. * '''Possible solution''' If custom schema is given, OGR PG can set a custom schema as current schema as follows: {{{ test=# SET search_path TO myschema, public; SET test=# SELECT current_schema(); current_schema ---------------- myschema (1 row) }}} and then the PG driver will generate layer name without schema name concatenated. I think it's not reasonable to solve this problem by hacking the ogr2ogr.cpp utility because this issue is very PostgreSQL-specific. ",defect,closed,highest,1.4.2,OGR_SF,1.4.1,critical,fixed,postgis postgresql schema,pka