#1412 closed defect (fixed)
Schema names are not quoted by OGR tools (ogrinfo/ogr2ogr) when using PostGIS as datasource
Reported by: | 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 )
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)
Change History (13)
by , 17 years ago
Attachment: | test-schema.sql added |
---|
comment:1 by , 17 years ago
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 by , 17 years ago
Description: | modified (diff) |
---|---|
Milestone: | → 1.4.1 |
comment:5 by , 17 years ago
Status: | new → assigned |
---|
comment:6 by , 17 years ago
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 by , 17 years ago
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 by , 17 years ago
Milestone: | 1.4.1 → 1.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 by , 17 years ago
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 by , 17 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
comment:11 by , 17 years ago
Resolution: | fixed |
---|---|
Status: | closed → reopened |
comment:13 by , 17 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
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.
The postgres dump