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

[PostGIS]

#3005: 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);
 ```

 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 ScaleX or ScaleY 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

 I'll try to provide a simple means of reproducing this issue. As for the
 patch, I think the input values must be rounded instead of adding a
 constraint.

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