Opened 12 years ago

Closed 12 years ago

#1572 closed defect (fixed)

PG incorrectly recognizes layers from non-default schema

Reported by: Mateusz Łoskot Owned by: Mateusz Łoskot
Priority: highest Milestone: 1.4.2
Component: OGR_SF Version: 1.4.1
Severity: critical Keywords: postgis postgresql schema
Cc: pka

Description

NOTE: This issue was reported on the gdal-dev list by Richard Matsunaga:

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
  1. 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.
  1. 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.

Change History (5)

comment:4 in reply to:  description ; Changed 12 years ago by pka

The creation option are not visible when updating an existing table. You have to use the -nln option:

ogr2ogr -append -update -f PostgreSQL PG:"dbname=test" mypoints.shp \
   -nln myschema.mypoints

In my opinion, ogr2ogr should have command line options for the source geometry or maybe even general options.

For the PG schema support I worked around this using -lco, -nln and the schema.table notation, depending on the operation. In the Interlis driver I'm using an environment varable for passing an interpolation value for the source geometry. Another way to pass options to a driver is via the connection string. But I would prefer an additional ogr2ogr flag to replace the use of environment variables (like PGSQL_OGR_FID in the PG driver) or similar workarounds.

comment:5 in reply to:  4 Changed 12 years ago by Mateusz Łoskot

Owner: changed from warmerdam to Mateusz Łoskot
Status: newassigned

Replying to pka:

For the PG schema support I worked around this using -lco, -nln and the schema.table notation, depending on the operation.

I confirm this workaround fixes the problem and the following command works for me:

ogr2ogr -append -update -f PostgreSQL PG:"dbname=test" mypoints.shp \
   -nln myschema.mypoints -lco SCHEMA=myschema

Here, the -nln option is not used to assign new name to a layer but to inform ogr2ogr to use fully qualified layer name, because it's located under custom, non-default shema.

comment:6 Changed 12 years ago by Mateusz Łoskot

Milestone: 1.4.2

comment:7 in reply to:  description Changed 12 years ago by pka

Cc: pka added
  • 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.

My first implementation of the schema support was based on this method. But I run into severe limits and reimplemented it in the more intrusive way.

comment:8 Changed 12 years ago by Mateusz Łoskot

Resolution: fixed
Status: assignedclosed

Richard Matsunaga confirmed that the -nln workaround works for him. However, if in fugure we will find that this is insufficient solution, new ticket can be submitted and I will try to find a better solution.

I'm closing this issue as fixed.

Note: See TracTickets for help on using tickets.