[fdo-trac] #344: PostGIS Defect : selecting features does not work when primary index is not the first index ceated

[FDO]

#344: PostGIS Defect : selecting features does not work when primary index is not
the first index ceated
--------------------------------+-------------------------------------------
   Reporter:  bscott            |       Owner:  mloskot
       Type:  defect            |      Status:  new    
   Priority:  blocker           |   Milestone:  3.4.0  
  Component:  PostGIS Provider  |     Version:  3.2.0  
   Severity:  1                 |    Keywords:         
External_id:                    |  
--------------------------------+-------------------------------------------
 If the first index created on a PostGIS table is not the primary one, it's
 not possible to select a feature in Mapguide. To reproduce you just have
 to drop the primary index and recreate it, thus this index become created
 after the spatial index and selecting won't work anymore. To reanable it,
 just drop the spatial index and recreate it ...
 Another effect of this defect, is in mapguide studio when you created a
 new PostGIS data connection we have the ability to preview all the tables.
 All the unselectable tables are not displayed...

 The bug reside in the PgTableColumnsReader class, the sql statement to
 retreive all the columns is incorrect

 I have added a where condition on i.indisprimary = 't' and it fixed the
 problem.
         "SELECT a.attnum AS ordinal_position, a.attname AS column_name"
         ",t.typname AS data_type, a.attlen AS character_maximum_length"
         ",a.atttypmod AS modifier, a.attnotnull AS notnull"
         ",a.atthasdef AS hasdefault, d.adsrc AS defaultVal"
         ",a.attnum = ANY (i.indkey) AS isprimarykey, i.indkey AS primKey,
 i.indisprimary AS indisprimary"
         " FROM pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.attrelid =
 d.adrelid"
         ", pg_type t, pg_namespace n"
         ", pg_class c LEFT OUTER JOIN pg_index i ON i.indrelid = c.oid"
         " WHERE a.attnum > 0 AND a.attrelid = c.oid"
         " AND a.atttypid = t.oid AND c.relnamespace = n.oid"
         " AND t.typname !~ '^geom'"
 '''        " AND i.indisprimary = 't'"'''        " AND c.relname = '" +
 table + "' AND n.nspname = '" + schema + "' ORDER BY a.attnum;");

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/344>
FDO <http://fdo.osgeo.org/>
Feature Data Objects