[GDAL] ODBC postgresql information schema problems

[jmckenna@…]

original thread on gdal-dev: http://www.nabble.com/Virtual-Spatial-connection-to-PostgreSQL-ODBC-driver-tf2727255.html#a7606179

Here is my small test to replicate:

1) create database
==================

# createdb jm -U postgres
   CREATE DATABASE

2) load test data
=================

# psql -U postgres -d jm -f load.sql
   BEGIN
   CREATE TABLE
   INSERT 40259 1
   INSERT 40260 1
   COMMIT

load.sql contains:

BEGIN;
CREATE TABLE "test" 
(
  "lat_n" numeric(4,2), 
  "long_e" numeric(5,2),
  "id" integer
);
INSERT INTO test VALUES (45.42, -75.70, 1);
INSERT INTO test VALUES (49.20, -124.03, 2);
END;

3) create DSN
=============

- created DSN named 'gdal-test'
  - System DSN
  - tried both 'PostgreSQL ANSI' and 'PostgreSQL UNICODE' driver types
  - database: jm
  - server: 127.0.0.1
  - username: postgres

4) test with ogrinfo
====================

# ogrinfo ODBC:@gdal-test test -summary

ERROR 1: No column definitions found for table 'applicable_roles', layer not us
ble.
ERROR 1: No column definitions found for table 'check_constraints', layer not u
able.
ERROR 1: No column definitions found for table 'column_domain_usage', layer not
usable.
ERROR 1: No column definitions found for table 'column_privileges', layer not u
able.
ERROR 1: No column definitions found for table 'column_udt_usage', layer not us
ble.
ERROR 1: No column definitions found for table 'columns', layer not usable.
ERROR 1: No column definitions found for table 'constraint_column_usage', layer
not usable.
ERROR 1: No column definitions found for table 'constraint_table_usage', layer
ot usable.
ERROR 1: No column definitions found for table 'data_type_privileges', layer no
 usable.
ERROR 1: No column definitions found for table 'domain_constraints', layer not
sable.
ERROR 1: No column definitions found for table 'domain_udt_usage', layer not us
ble.
ERROR 1: No column definitions found for table 'domains', layer not usable.
ERROR 1: No column definitions found for table 'element_types', layer not usabl
.
ERROR 1: No column definitions found for table 'enabled_roles', layer not usabl
.
ERROR 1: No column definitions found for table 'information_schema_catalog_name
, layer not usable.
ERROR 1: No column definitions found for table 'key_column_usage', layer not us
ble.
ERROR 1: No column definitions found for table 'parameters', layer not usable.
ERROR 1: No column definitions found for table 'referential_constraints', layer
not usable.
ERROR 1: No column definitions found for table 'role_column_grants', layer not
sable.
ERROR 1: No column definitions found for table 'role_routine_grants', layer not
usable.
ERROR 1: No column definitions found for table 'role_table_grants', layer not u
able.
ERROR 1: No column definitions found for table 'role_usage_grants', layer not u
able.
ERROR 1: No column definitions found for table 'routine_privileges', layer not
sable.
ERROR 1: No column definitions found for table 'routines', layer not usable.
ERROR 1: No column definitions found for table 'schemata', layer not usable.
ERROR 1: No column definitions found for table 'sql_features', layer not usable

ERROR 1: No column definitions found for table 'sql_implementation_info', layer
not usable.
ERROR 1: No column definitions found for table 'sql_languages', layer not usabl
.
ERROR 1: No column definitions found for table 'sql_packages', layer not usable

ERROR 1: No column definitions found for table 'sql_sizing', layer not usable.
ERROR 1: No column definitions found for table 'sql_sizing_profiles', layer not
usable.
ERROR 1: No column definitions found for table 'table_constraints', layer not u
able.
ERROR 1: No column definitions found for table 'table_privileges', layer not us
ble.
ERROR 1: No column definitions found for table 'tables', layer not usable.
ERROR 1: No column definitions found for table 'triggered_update_columns', laye
 not usable.
ERROR 1: No column definitions found for table 'triggers', layer not usable.
ERROR 1: No column definitions found for table 'usage_privileges', layer not us
ble.
ERROR 1: No column definitions found for table 'view_column_usage', layer not u
able.
ERROR 1: No column definitions found for table 'view_table_usage', layer not us
ble.
ERROR 1: No column definitions found for table 'views', layer not usable.
INFO: Open of `ODBC:@gdal-test'
      using driver `ODBC' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 2
Layer SRS WKT:
(unknown)
lat_n: Real (4.2)
long_e: Real (5.2)
id: Integer (10.0)

5) verify that 'postgres' user owns the information schema
==========================================================

psql# SET search_path TO INFORMATION_SCHEMA, public;
psql# \d

                            List of relations
       Schema       |              Name               | Type  |  Owner
--------------------+---------------------------------+-------+----------
 information_schema | applicable_roles                | view  | postgres
 information_schema | check_constraints               | view  | postgres
 information_schema | column_domain_usage             | view  | postgres
 information_schema | column_privileges               | view  | postgres
 information_schema | column_udt_usage                | view  | postgres
 information_schema | columns                         | view  | postgres
 information_schema | constraint_column_usage         | view  | postgres
 information_schema | constraint_table_usage          | view  | postgres
 information_schema | data_type_privileges            | view  | postgres
 information_schema | domain_constraints              | view  | postgres
 information_schema | domain_udt_usage                | view  | postgres
 information_schema | domains                         | view  | postgres
 information_schema | element_types                   | view  | postgres
 information_schema | enabled_roles                   | view  | postgres
 information_schema | information_schema_catalog_name | view  | postgres
 information_schema | key_column_usage                | view  | postgres
 information_schema | parameters                      | view  | postgres
 information_schema | referential_constraints         | view  | postgres
 information_schema | role_column_grants              | view  | postgres
 information_schema | role_routine_grants             | view  | postgres
 information_schema | role_table_grants               | view  | postgres
 information_schema | role_usage_grants               | view  | postgres
 information_schema | routine_privileges              | view  | postgres
 information_schema | routines                        | view  | postgres
 information_schema | schemata                        | view  | postgres
 information_schema | sql_features                    | table | postgres
 information_schema | sql_implementation_info         | table | postgres
 information_schema | sql_languages                   | table | postgres
 information_schema | sql_packages                    | table | postgres
 information_schema | sql_sizing                      | table | postgres
 information_schema | sql_sizing_profiles             | table | postgres
 information_schema | table_constraints               | view  | postgres
 information_schema | table_privileges                | view  | postgres
 information_schema | tables                          | view  | postgres
 information_schema | triggered_update_columns        | view  | postgres
 information_schema | triggers                        | view  | postgres
 information_schema | usage_privileges                | view  | postgres
 information_schema | view_column_usage               | view  | postgres
 information_schema | view_table_usage                | view  | postgres
 information_schema | views                           | view  | postgres
 public             | test                            | table | postgres
(41 rows)

6) Try in MapServer
===================

# shp2img -m gdal-test.map -o test.png

msOGRFileNextShape(): OGR error. No column definitions found for table 'views',
layer not usable.

Testing Environment
===================

WinXP
GDAL CVS-head (01/09/07)
MapServer CVS-head (01/09/07)
PostgreSQL 8.1.5
  pg_hba.conf:
      host     jm       all     0.0.0.0/0            trust

[mikesaunt@…]

Mike Saunt - The same error occurs when trying to access an Oracle View.  We were able to get around it by simply using the Oracle View SQL Statement (changing SRCLayer to SRCSQL in the VRT)

[Mateusz Łoskot]

I followed exactly the same steps as listed below, using the same command line tools and here are my results:

=======================================================
C:\Program Files\FWTools1.1.3>ogrinfo ODBC:@gdal-test-ansi test -summary
INFO: Open of `ODBC:@gdal-test-ansi'
      using driver `ODBC' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 2
Layer SRS WKT:
(unknown)
lat_n: Real (4.2)
long_e: Real (5.2)
id: Integer (10.0)
=======================================================
C:\Program Files\FWTools1.1.3>ogrinfo ODBC:@gdal-test test -summary
INFO: Open of `ODBC:@gdal-test'
      using driver `ODBC' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 2
Layer SRS WKT:
(unknown)
lat_n: Real (4.2)
long_e: Real (5.2)
id: Integer (10.0)
=======================================================

So, in my environment I'm not able to reproduce the problem.

Here is my environment in details:
WinXP
GDAL from FWTools 1.1.3
PostgreSQL 8.2
PostgreSQL ODBC Driver 8.02.02.00 10/24/2006 (PSQLODBC30A.DLL, PSQLODBC30W.DLL)
  pg_hba.conf
    host    all         all         127.0.0.1/32          md5
    host    all         all         0.0.0.0     0.0.0.0     md5

[Mateusz Łoskot]

Unfortunately, I wasn't able to reproduce this bug, so I have to leave it as UNCONFIRMED and close as WONTFIX.
Today, I talked with Jeff about it and we agreed to close it.
If Jeff meets the problem again, we will get back to fight it.