Numeric overflow when executing AddRasterConstraints

[rtorre]

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<sup>6. `</sup>

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.

[rtorre]

Related to [8396] and #1373

[rtorre]

I'm creating a new ticket and closing this one as duplicate to fix the formatting. Sorry for the inconveniences.

[rtorre]

Closing as duplicate of #3006 (with correct formatting)