Обсуждение: Don't understand error?

Hi everyone,

I'm working with a large spatial dataset at the moment. About 109m
rows. What I do is join some other tables to it, do some maths and
spatial joins, and then put the results into a new table (which is
therefore 109m rows too).

When I run my SQL for 50 people it's fine. 1 person is fine. A
thousand people it's fine. But when I try and run it for the whole
dataset I'm given this error:

ERROR: value out of range: underflow
****************Error *******************
Error: Value out of range: underflow
SQL state: 22003

I've looked this up, but I don't really get what it is saying. I'd
appreciate some advice? I was going to post my actual SQL code but
it's about 150 lines long and I wasn't sure it would be useful.

Thanks

James

James David Smith <james.david.smith@gmail.com> writes:
> When I run my SQL for 50 people it's fine. 1 person is fine. A
> thousand people it's fine. But when I try and run it for the whole
> dataset I'm given this error:

> ERROR: value out of range: underflow
> ****************Error *******************
> Error: Value out of range: underflow
> SQL state: 22003

This looks like an arithmetic problem not a SQL problem.  Is your
calculation working with very small floating-point values anywhere?

            regards, tom lane

Hi Tom,

Yes, I thought the same. Glad to know that I might be along the right
lines. I've started taking parameters out one-by-one to try and find
the culprit. Yes, there are a variety of numbers stored as NUMERIC in
various places. For example:

0.23423424234
5.6434535
10.2131

etc.

Then there are various multiplications and divisions occurring in
fairly complex formulas.

I guess I just slowly work through the code and try to figure out
which bit of the maths is causing the error?

Cheers

James


On 14 April 2014 14:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> James David Smith <james.david.smith@gmail.com> writes:
>> When I run my SQL for 50 people it's fine. 1 person is fine. A
>> thousand people it's fine. But when I try and run it for the whole
>> dataset I'm given this error:
>
>> ERROR: value out of range: underflow
>> ****************Error *******************
>> Error: Value out of range: underflow
>> SQL state: 22003
>
> This looks like an arithmetic problem not a SQL problem.  Is your
> calculation working with very small floating-point values anywhere?
>
>                         regards, tom lane

James David Smith <james.david.smith@gmail.com> writes:
> Yes, I thought the same. Glad to know that I might be along the right
> lines. I've started taking parameters out one-by-one to try and find
> the culprit. Yes, there are a variety of numbers stored as NUMERIC in
> various places. For example:

AFAIR, there is no such thing as an underflow error in the type-NUMERIC
code --- it will happily round to zero instead.  You should be looking for
something involving float4 or float8 (aka real/double precision).

            regards, tom lane

Good tip. Thanks. I don't think there are many values stored like
that, so it should make it a bit easier to find.

On 14 April 2014 15:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> James David Smith <james.david.smith@gmail.com> writes:
>> Yes, I thought the same. Glad to know that I might be along the right
>> lines. I've started taking parameters out one-by-one to try and find
>> the culprit. Yes, there are a variety of numbers stored as NUMERIC in
>> various places. For example:
>
> AFAIR, there is no such thing as an underflow error in the type-NUMERIC
> code --- it will happily round to zero instead.  You should be looking for
> something involving float4 or float8 (aka real/double precision).
>
>                         regards, tom lane

Just to say thanks. It looks like the error originated from multiplying the result of an EXTRACT(EPOCH from time) . Once I added ::numeric to this, the query ran ok. I also changed a couple of integer types to numeric, so it might have been those either.