[postgis-tickets] [PostGIS] #3006: Numeric overflow when executing AddRasterConstraints

[PostGIS]

#3006: Numeric overflow when executing AddRasterConstraints
--------------------+-------------------------------------------------------
 Reporter:  rtorre  |       Owner:  dustymugs    
     Type:  defect  |      Status:  new          
 Priority:  high    |   Milestone:  PostGIS 2.1.5
Component:  raster  |     Version:  2.1.x        
 Keywords:          |  
--------------------+-------------------------------------------------------
 We are experiencing a `numeric field overflow` when adding overviews to a
 raster and subsequently calling `AddRasterConstraints`.

 Executing this:
 {{{
 SELECT
 AddRasterConstraints('cdb_importer','o_256_importer_e7efb068796911e4a19a5e0004719e63','the_raster_webmercator',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);
 }}}
 I get this in the postgresql log:
 {{{
  PL/pgSQL function
 addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
  line 53 at RETURN
 psql:/tmp/imports/20141201-2293-x4pho4/importer_e7efb068796911e4a19a5e0004719e63.sql:7472:
  NOTICE:  SQL used for failed constraint: ALTER TABLE
  cdb_importer.o_256_importer_e7efb068796911e4a19a5e0004719e63 ADD
  CONSTRAINT enforce_sca
  lex_the_raster_webmercator CHECK
  (st_scalex(the_raster_webmercator)::numeric(16,10) =
  (1252219.04682188)::numeric(16,10))
          CONTEXT:  PL/pgSQL function
  _add_raster_constraint_scale(name,name,name,character) line 38 at RETURN
          PL/pgSQL function addrasterconstraints(name,name,name,text[])
 line
  60 at assignment
          PL/pgSQL function
 addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
  line 53 at RETURN
 psql:/tmp/imports/20141201-2293-x4pho4/importer_e7efb068796911e4a19a5e0004719e63.sql:7472:
  NOTICE:  Returned error message: numeric field overflow
          CONTEXT:  PL/pgSQL function
  _add_raster_constraint_scale(name,name,name,character) line 38 at RETURN
 }}}

 I think the problem is located in the file
 `./raster/rt_pg/rtpostgis.sql.in`, around lines 6964-6968:
 {{{
     sql := 'ALTER TABLE ' || fqtn
       || ' ADD CONSTRAINT ' || quote_ident(cn)
       || ' CHECK (st_scale' || $4 || '('
       || quote_ident($3)
       || ')::numeric(16,10) = (' || attr || ')::numeric(16,10))';
     RETURN _add_raster_constraint(cn, sql);
 }}}

 (See [8396] and #1373 for more on that constraint)

 Executing this from psql I get the same error:
 {{{
 # select  (1252219.04682188)::numeric(16,10);
 ERROR:  numeric field overflow
 DETAIL:  A field with precision 16, scale 10 must round to an absolute
 value less than 10^6.
 }}}

 I think this is related to other issues people is having, not only because
 of big values of `st_scale` but also with (very) small values, when input
 values have too much precision:

  * http://gis.stackexchange.com/questions/118120/numeric-issue-while-
 loading-floating-point-valued-geotiff-into-postgis
  * http://gis.stackexchange.com/questions/59732/uploading-raster-format-
 tif-to-postgis-through-raster2pgsql

 This file from Natural Earth can be used to reproduce the issue:
 http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/raster/SR_HR.zip

 I think the input values must be rounded instead of adding a constraint.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3006>
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.