Ticket #1572 (closed defect: fixed)
PG incorrectly recognizes layers from non-default schema
|Reported by:||mloskot||Owned by:||mloskot|
|Severity:||critical||Keywords:||postgis postgresql schema|
NOTE: This issue was reported on the gdal-dev list by Richard Matsunaga:
Below, I give a code-level explanation of the problem.
- There is a custom schema in PostgreSQL database, ie. myschema
- 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
- 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
The command described in the point 3 fails.
There are two main reasons:
- OGR does not set a custom schema to be used as current schema.
- Implied by the 1st, the layer name is generated by concatenation schema and table names: myschema.mypoints, in 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 ogr2ogr.cpp:605, because compared values are:
myschema.mypoints vs mypoints
Finally, because no layer can be found for updated, ogr2ogr 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.