Обсуждение: BUG #10457: Problem with double precision field.

The following bug has been logged on the website:

Bug reference:      10457
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.0.17
Operating system:   Linux
Description:

Hi I have one database with table which I could not restore via
pg_dump/pg_restore.

I localized the problem at double precision values which could not be
inserted into double precision (weird it is).

[local]:5432 postgres@comon3=#  select record_rating from rating.record
where record_rating>0 order by 1 limit 50;
    record_rating
----------------------
 6.5397686164648e-309
 8.1108289380243e-309
 2.0326868922821e-308
and so on...

But:
select '6.5397686164648e-309'::double precision;
ERROR:  "6.5397686164648e-309" is out of range for type double precision
LINE 2: select '6.5397686164648e-309'::double precision;

select record_rating::text::double precision from rating.record where
record_rating>0 order by record_rating limit 10;
ERROR:  "6.5397686164648e-309" is out of range for type double precision

So somehow double precision field could content value which could not be
inserted into it again.
Any ideas what's wrong with the database welcome.


PS: table structure:
 obj_id                            | bigint                   | not null
default nextval('rating.record_obj_id_seq'::regclass)
 obj_created                       | timestamp with time zone | not null
default now()
 obj_modified                      | timestamp with time zone | not null
default now()
 obj_status_did                    | smallint                 | not null
default 1
 record_rating                     | double precision         |
 record_rating_without_attenuation | double precision         | default 0
Indexes:
    "record_pkey" PRIMARY KEY, btree (obj_id)

Kind Regards,
Maksym

maxim.boguk@gmail.com writes:
> select '6.5397686164648e-309'::double precision;
> ERROR:  "6.5397686164648e-309" is out of range for type double precision

This is fixed in 9.2 and up.
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c318aeed84438619fc6b8c647def1730a110f04b

            regards, tom lane

Hi,

On 2014-05-29 13:56:29 +0000, maxim.boguk@gmail.com wrote:
> PostgreSQL version: 9.0.17

> [local]:5432 postgres@comon3=#  select record_rating from rating.record
> where record_rating>0 order by 1 limit 50;
>     record_rating
> ----------------------
>  6.5397686164648e-309
>  8.1108289380243e-309
>  2.0326868922821e-308
> and so on...
>
> But:
> select '6.5397686164648e-309'::double precision;
> ERROR:  "6.5397686164648e-309" is out of range for type double precision
> LINE 2: select '6.5397686164648e-309'::double precision;

Those are 'denormalized' floating point numbers. Code to handle those
was added to 9.2+ in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c318aeed84438619fc6b8c647def1730a110f04b

I guess it could be backpatched now that it has survived testing for a
fair while. It's annoying not to be able to restore data... Till then
you can just apply the patch.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services