[postgis-tickets] [PostGIS] #3060: loader_load_staged_data relying on undefined behaviour when generating column lists

[PostGIS]

#3060: loader_load_staged_data relying on undefined behaviour when generating
column lists
----------------------------+-----------------------------------------------
 Reporter:  gibreel         |       Owner:  robe 
     Type:  defect          |      Status:  new  
 Priority:  medium          |   Milestone:       
Component:  tiger geocoder  |     Version:  2.1.x
 Keywords:  loader          |  
----------------------------+-----------------------------------------------
 The loader_load_staged_data function assumes that select statement for
 column names will produce the same sequence for both the source and
 destination tables.  While it might, such ordering behaviour isn't
 guaranteed as the error below shows.

 The simple fix is to add 'order by column_name' to both queries.  Patch
 attached.

 Trace output below:

 {{{
 + psql -c 'ALTER TABLE tiger_staging.county RENAME geoid TO cntyidfp;
 SELECT loader_load_staged_data(lower('\''county'\''),
 lower('\''county_all'\''));'
 NOTICE:  INSERT INTO
 tiger_data.county_all(statefp,countyfp,countyns,cntyidfp,name,namelsad,lsad,classfp,mtfcc,csafp,cbsafp,metdivfp,funcstat,aland,awater,intptlat,intptlon,the_geom)
 SELECT
 statefp,countyfp,countyns,name,namelsad,lsad,classfp,mtfcc,csafp,cbsafp,metdivfp,funcstat,aland,awater,intptlat,intptlon,the_geom,cntyidfp
 FROM tiger_staging.county;
 CONTEXT:  SQL function "loader_load_staged_data" statement 1
 ERROR:  column "awater" is of type double precision but expression is of
 type character varying
 LINE 1: ...mtfcc,csafp,cbsafp,metdivfp,funcstat,aland,awater,intptlat,i...
                                                              ^
 HINT:  You will need to rewrite or cast the expression.
 QUERY:  INSERT INTO
 tiger_data.county_all(statefp,countyfp,countyns,cntyidfp,name,namelsad,lsad,classfp,mtfcc,csafp,cbsafp,metdivfp,funcstat,aland,awater,intptlat,intptlon,the_geom)
 SELECT
 statefp,countyfp,countyns,name,namelsad,lsad,classfp,mtfcc,csafp,cbsafp,metdivfp,funcstat,aland,awater,intptlat,intptlon,the_geom,cntyidfp
 FROM tiger_staging.county;
 CONTEXT:  PL/pgSQL function loader_load_staged_data(text,text,text[]) line
 24 at EXECUTE statement

 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3060>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.