[raster] 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);

(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.

[robe]

rtorre,

thanks, for the report.

[robe]

rtorre,

I'll try to come with a better scale precision to use. The reason swe don't use round is since the raster values are stored as numeric, round doesn;t work.

So for example:

SELECT round( (1252219.04682188555)::double precision,10);

would give you an error of: ERROR: function round(double precision, integer) does not exist

So safest to just keep it as numeric cast and increase the casting scale and precision.

[robe]

I meant stored as double precision not numeric.

[rtorre]

Storing as double looks ok to me.

[rtorre]

Replying to rtorre:

Storing as double looks ok to me.

Well, after reviewing the discussion in #1373 (that motivated the changes [8396]) maybe that's not the best option because that can cause arithmetic precision issues again.

[rtorre]

Since the purpose of the constraint is to check for equality of numeric values, something like

CHECK abs(st_scalex(the_raster_webmercator) - (attr)) <= delta

should be more appropriate, with delta = 1e-10 for instance.

[rtorre]

A delta of 1e-6 should be enough to cover scale values ranging from 1e-5 through 1e14.

I'll prepare a patch with a test with my proposal.

[rtorre]

I'm adding this patch with the hope of opening a productive discussion. I realized it breaks backwards compatibility with existing tables because the source of the constraints is used to retrieve information (see raster_columns and _raster_constraint_info_scale). Suggestions are more than welcomed.

[robe]

rtorre — thinking about this more I think your change while in principal is fine is too much of a breaking change.

Talked with dustymugs on IRC and he was okay with just increasing the precision to 25,10.

The issue with your change is that that check constraint is parsed out by the function _raster_constraint_info_scale

So your proposed change would require us to rewrite to support that new format as well as older constraint format. Since this is a critical change that should go into 2.1.5, I don't feel comfortable doing such a change that requires such extensive testing.

[robe]

rtorre I think we can consider your patch for 2.2.0, but not for 2.1.5. For 2.1.5 probably should just increase the precision.

[rtorre]

increasing the precision to 25,10

That's good enough for me.

I think we can consider your patch for 2.2.0, but not for 2.1.5. For 2.1.5 probably should just increase the precision.

I wouldn't do much on top of functions/views that parse constraints, if for only maintainability (and possibly performance).

Thanks a lot for the prompt response.

[robe]

increased precision to 25,10 in trunk 2.2 at r13136 — will make same change in 2.1 and then try to add your new big test regression tsst to mix and then backport to 2.1.

[robe]

did the same fro 2.1 at r13137

Still need to commit the big raster tests

[dustymugs]

big raster tests?

[rtorre]

Replying to dustymugs:

big raster tests?

I guess she's talking about the tests in the patch I uploaded. Given the change finally committed into trunk and 2.1 I do believe they are a bit overkill.

IMHO this ticket can be closed as resolved.

[dustymugs]

I'll close this once I have the chance to see the change affects existing databases…

[robe]

rtorre,

I wanted to include your regress ticket. We try to test anything in regress fixed especially since you went thru the trouble of making the tests and this seemed like an important bug to regress test. I was planning to test the regress test and then commit. Should do within the next week.

[robe]

I'm going to take rtorre's advice and just close this. I'll open another ticket that we need big raster regress test.

[dustymugs]

Reopening because I think we could get this to work better…

round(ST_ScaleX(rast)::numeric, 10) == round(MYREF::numeric, 10)

[dustymugs]

Changed in -trunk as of r13467. I'm not going to make the same change to 2.1 as I got rid of all use of numeric(16, 10) in favor of round(VALUE::numeric, 10).

[strk]

This change broke upgrades, see #3118