Opened 10 years ago

Closed 10 years ago

#3005 closed defect (duplicate)

Numeric overflow when executing AddRasterConstraints

Reported by: rtorre Owned by: Bborie Park
Priority: high Milestone: PostGIS 2.1.5
Component: raster Version: 2.1.x
Keywords: Cc:

Description

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 106. `

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.

Change History (3)

comment:1 by rtorre, 10 years ago

Related to [8396] and #1373

comment:2 by rtorre, 10 years ago

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

comment:3 by rtorre, 10 years ago

Resolution: duplicate
Status: newclosed

Closing as duplicate of #3006 (with correct formatting)

Note: See TracTickets for help on using tickets.