Обсуждение: Problem with java infinity mapping to postgres real column type

Hi,

I have searched the mail archives at the postgres site but have not come up with anything that addresses this particular issue.
I have found that when trying to insert a Java Float.POSITIVE_INFINITY or its negative counterpart into a database column with data type real using a jdbc driver 8.1-406 or 8.2-504 postgres will throw an overflow error.
Since both Java and Postgres real data type support infinity then this seems very strange.

After some testing I found that the situation described above does not throw an error using a jdbc driver 8.0-310. After some more debugging it seems that this is caused by the Oid type being used for float input. The 8.0 driver uses Oid.FLOAT4 but the 8.1 and 8.2 drivers both use Oid.FLOAT8 (AbstractJdbc2Statement.java setFloat()). I also found that it is possible to insert inifinity into a column with data type real if the setObject function is used with the targetSqlType being REAL since this data type uses Oid.FLOAT4.

What would be the best practice in this situation?
This did not seem a problem when using the double precision column data type. Althouh it seems a little extreme to have to convert a column data type to accomodate a few values that can not be inserted correctly. How much would a tables size increase if a real data type is converted to a double precision?

--
Johan Miller

"Johan Miller" <johanmiller@gmail.com> writes:
> I have found that when trying to insert a Java Float.POSITIVE_INFINITY or
> its negative counterpart into a database column with data type real using a
> jdbc driver 8.1-406 or 8.2-504 postgres will throw an overflow error.
> Since both Java and Postgres real data type support infinity then this seems
> very strange.

It sounds like "infinity" is getting translated to "some large number"
rather than the correct "Infinity" or "-Infinity" symbol.  The latter
two should work on IEEE-compliant platforms, but you might want to check
manually:

regression=# select 'infinity'::float4;
  float4
----------
 Infinity
(1 row)

regression=# select '-infinity'::float4;
  float4
-----------
 -Infinity
(1 row)

            regards, tom lane

On Wed, 16 Jul 2008, Tom Lane wrote:

> It sounds like "infinity" is getting translated to "some large number"
> rather than the correct "Infinity" or "-Infinity" symbol.  The latter
> two should work on IEEE-compliant platforms, but you might want to check
> manually:

Actually the problem is:

jurka=# select 'infinity'::float8::float4;
ERROR:  type "real" value out of range: overflow

I would have expected that the float8 -> float4 conversion would just keep
it as infinity.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> Actually the problem is:
> jurka=# select 'infinity'::float8::float4;
> ERROR:  type "real" value out of range: overflow

Well, that's definitely a problem, but what PG version are you testing
on what platform?  I get this using CVS HEAD on Fedora 9, Darwin, and
HPUX:

regression=# select 'infinity'::float8::float4;
  float4
----------
 Infinity
(1 row)

I suspect that you aren't getting true "Infinity" from
'infinity'::float8 in the first place, but some large number,
which then fails to fit into float4.

            regards, tom lane

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>> Actually the problem is:
>> jurka=# select 'infinity'::float8::float4;
>> ERROR:  type "real" value out of range: overflow
>
> Well, that's definitely a problem, but what PG version are you testing
> on what platform?  I get this using CVS HEAD on Fedora 9, Darwin, and
> HPUX:
>
> regression=# select 'infinity'::float8::float4;
>   float4
> ----------
>  Infinity
> (1 row)
>

I get the error (or variant) for 7.4 -> 8.2 on Debian Linux.  8.3 and
HEAD work.

I see this in the 8.3 release notes:

Fix float4/float8 to handle Infinity and NAN (Not A Number) consistently
(Bruce)  The code formerly was not consistent about distinguishing
Infinity from overflow conditions.

Kris Jurka

"Johan Miller" <johanmiller@gmail.com> writes:
> On Thu, Jul 17, 2008 at 08:22, Kris Jurka <jurka@ejurka.com> wrote:
>> I see this in the 8.3 release notes:
>>
>> Fix float4/float8 to handle Infinity and NAN (Not A Number) consistently
>> (Bruce)  The code formerly was not consistent about distinguishing Infinity
>> from overflow conditions.

> What would be the options to handle this on earlier version where it is not
> fixed? Would it be a possibility that this will get changed in the 8.1 and
> 8.2 versions also?

I believe the conclusion was that that change shouldn't be back-patched
because it would be a behavioral change that might break applications.

            regards, tom lane