Schema names are not quoted by OGR tools (ogrinfo/ogr2ogr) when using PostGIS as datasource

[dolf.andringa@…]

Hey people,

When I create a schema with a - in the name and create a table inside that schema with geographic information and then try to list the features in that table using ogrinfo, no features are listed. When I turn on CPL_DEBUG and then run ogrinfo, the output below is shown. As you can see the schemaname is not quoted, causing the queries to fail. Below the output I have put a dump that will create the schema and table and will insert one feature into the table (and insert a row into public.geometry_columns). When the - in the schemaname is changed into an _ the one feature in the table is listed by ogrinfo. So it is just the problem that the schemaname is not quoted by ogr.

Dolf.

GR_PG: DBName="<dbname>"
OGR_PG: PostSIS version string: '1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1' -> '1.1'
OGR_PG: POSTGIS_VERSION=1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
OGR_PG: Using column 'ogc_fid' as FID for table 'test-table'
OGR_PG: Layer 'test-table' geometry type: POINT:Point, Dim=2
OGR: OGROpen(PG:dbname=<dbname> user=<username> password=<pwd> host=<host>/0x80650d8) succeeded as PostgreSQL.
INFO: Open of `PG:dbname=<dbname> user=<username> password=<pwd> host=<host>'
      using driver `PostgreSQL' successful.
OGR: GetLayerCount() = 1

INFO: ---------- General Layer Report ----------

Layer name: test-schema.test-table
Geometry: Point
OGR_PG: PQexec(DECLARE countCursor CURSOR for SELECT count(*) FROM test-schema."test-table" )

OGR_PG: DECLARE countCursor CURSOR for SELECT count(*) FROM test-schema."test-table" ; failed.
Feature Count: 0
OGR_PG: Unable to get extent by PostGIS. Using standard OGRLayer method.
OGR_PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT "ogc_fid", AsEWKT("wkb_geometry"), "adresnr", "huislt", "huisnr", "postk_a", "postk_n", "straat", "toev", "x_coord", "y_coord" FROM test-schema."test-table" )
Layer SRS WKT:
(unknown)

INFO: ---------- Geometry Column Report ----------

FID Column = ogc_fid
Geometry Column = wkb_geometry
adresnr: String (0.0)
huislt: String (0.0)
huisnr: String (0.0)
postk_a: String (0.0)
postk_n: String (0.0)
straat: String (0.0)
toev: String (0.0)
x_coord: String (0.0)
y_coord: String (0.0)

INFO: ---------- Features Dump ----------

OGR_PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT "ogc_fid", AsEWKT("wkb_geometry"), "adresnr", "huislt", "huisnr", "postk_a", "postk_n", "straat", "toev", "x_coord", "y_coord" FROM test-schema."test-table" )




SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;
CREATE SCHEMA "test-schema";
SET search_path = "test-schema", pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;
CREATE TABLE "test-table" (
    ogc_fid integer,
    wkb_geometry public.geometry,
    huislt character varying,
    postk_n character varying,
    toev character varying,
    postk_a character varying,
    adresnr character varying,
    straat character varying,
    huisnr character varying,
    x_coord character varying,
    y_coord character varying
);

INSERT INTO "test-table" VALUES (1, '0101000000122DB29D214F0041E388416007591B41', NULL, '3435', NULL, 'XB', '28844', 'Aalscholver', '1', '133604.202', '448065.843999995');

INSERT INTO public.geometry_columns VALUES ('', 'test-schema', 'test-table', 'wkb_geometry', 2, -1, 'POINT');

[dolf.andringa@…]

The postgres dump

[warmerdam]

Mateusz,

Could you investigate this?  It is an objective that the postgres driver quote
all names to allow funky table and column names if requested by the application.

If a fix is not too messy it should be backported into 1.4 branch.

[Mateusz Łoskot]

I believe it's a good idea to discuss this issue a little. PostgreSQL handles quoted identifiers in a bit ​way.

The manual recommends following one of strategies:

If you want to write portable applications you are advised to always quote a particular name or never quote it.

Currently, the PG driver uses always quotes identifier of table but identifier of schema is not quoted. If this is an intentional strategy, I think it's reasonable to follow this rule and always quote schema name as well.

I will wait a day for comments and will fix it tomorrow yet.

[Mateusz Łoskot]

Doh! The Trac has broken my URL to PostgreSQL manual, the text is:

PostgreSQL handles quoted identifiers in a bit non-standard way.

and should link to:

​http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html

[warmerdam]

Mateusz,

I think always quoting the schema is a good idea, but it is too late to deal with this for 1.4.1 so I've moved it off to 1.4.2.

[Mateusz Łoskot]

I added quoting of schema name in the PG driver (r11252).

The fix has been applied only to the trunk. Probably, if everything works well, it will be backported to the 1.4 stable branch. So, please test it and confirm if everything works so we're sure that with changing the quoting strategy we have not broken anything.

[Mateusz Łoskot]

I backported this tiny fix to stable branch 1.4 (r11351).

[Mateusz Łoskot]

The reporter has not confirmed if the issue has been fixed. I tested it on my own and I'm going to close it as fixed.

[Mateusz Łoskot]

I added some improvements to names quoting (r11352) and new test to the autotest package (r11353).