Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#1412 closed defect (fixed)

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

Reported by: dolf.andringa@… Owned by: Mateusz Łoskot
Priority: normal Milestone: 1.4.2
Component: OGR_SF Version: 1.3.2
Severity: minor Keywords: postgresql postgis
Cc:

Description (last modified by warmerdam)

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');

Attachments (1)

test-schema.sql (1.1 KB) - added by dolf.andringa@… 13 years ago.
The postgres dump

Download all attachments as: .zip

Change History (13)

Changed 13 years ago by dolf.andringa@…

Attachment: test-schema.sql added

The postgres dump

comment:1 Changed 13 years ago by 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.

comment:4 Changed 13 years ago by warmerdam

Description: modified (diff)
Milestone: 1.4.1

comment:5 Changed 13 years ago by Mateusz Łoskot

Status: newassigned

comment:6 Changed 13 years ago by Mateusz Łoskot

Keywords: postgresql postgis added

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.

comment:7 Changed 13 years ago by 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

comment:8 Changed 13 years ago by warmerdam

Milestone: 1.4.11.4.2

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.

comment:9 Changed 13 years ago by 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.

comment:10 Changed 13 years ago by Mateusz Łoskot

Resolution: fixed
Status: assignedclosed

comment:11 Changed 13 years ago by Mateusz Łoskot

Resolution: fixed
Status: closedreopened

comment:12 Changed 13 years ago by Mateusz Łoskot

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

comment:13 Changed 13 years ago by Mateusz Łoskot

Resolution: fixed
Status: reopenedclosed

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.

comment:14 Changed 13 years ago by Mateusz Łoskot

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

Note: See TracTickets for help on using tickets.