PG incorrectly recognizes layers from non-default schema

[Mateusz Łoskot]

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

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

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

[Mateusz Łoskot]

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.

[pka]

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

[Mateusz Łoskot]

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.