Обсуждение: Java : Postgres double precession issue with different data format text and binary

Поиск
Список
Период
Сортировка

Java : Postgres double precession issue with different data format text and binary

От
Rahul Uniyal
Дата:

Hello Team,

Hope everyone is doing well here.

I am writing this email to understand an issue I'm facing when fetching data in our Java application. We are using PostgreSQL JDBC Driver version 42.6.0.

Issue:

We are encountering an issue where the double precision data type in PostgreSQL is giving some intermittent results when fetching data. For example, in the database the value is 40, but sometimes this value is fetched as 40.0. Similarly, for a value of 0.0005, it is being fetched as 0.00050, resulting in extra trailing zeros.

While debugging, it seems like this issue is caused by the different data formats, such as Text and Binary. There is some logic in the PgResultSet class that converts values based on this data format.

Example:

Below is an example where we are getting different data formats for the same table:

Text Format: [Field(id,FLOAT8,8,T), Field(client_id,FLOAT8,8,T), Field(create_ts,TIMESTAMP,8,T), ...]

Binary Format: [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), ...] (notice some format changes)

We are not sure why different formats are coming for the same table.

Schema:

Below is the schema for the table used:

SQL

 

 CREATE TABLE IF NOT EXISTS SUBMISSION_QUEUE(
  ID               DOUBLE PRECISION,
  CLIENT_ID           DOUBLE PRECISION,
  OCODE VARCHAR(20) NOT NULL,
  PAYLOAD_TYPE        VARCHAR(20),
  REPOSITORY VARCHAR(16),
  SUB_REPOSITORY          VARCHAR(20),
  FORCE_GENERATION_FLAG   BOOLEAN,
IS_JMX_CALL BOOLEAN,
INSTANCE_ID           DOUBLE PRECISION,
CREATE_TS         TIMESTAMP(6) NOT NULL,
);


Request:

Team, would it be possible to give some insight on this issue? Any help would be greatly appreciated.

Thanks,

Re: Java : Postgres double precession issue with different data format text and binary

От
Chapman Flack
Дата:
Hi,

On 03/16/24 11:10, Rahul Uniyal wrote:
> We are encountering an issue where the double precision data type
> in PostgreSQL is giving some intermittent results when fetching data.
> For example, in the database the value is 40, but sometimes this value
> is fetched as 40.0. Similarly, for a value of 0.0005, it is being
> fetched as 0.00050, resulting in extra trailing zeros.

As a first observation, the column names in your schema suggest that
these columns are being used as IDs of some kind, for which a float type
would be an unusual choice. Unless something in your situation requires it,
you might consider changing to integer types for IDs.

That said, you may have found something interesting in how JDBC handles
the float8 type in text vs. binary format, but comparing the results
of conversion to decimal string is not the most direct way to
investigate it.

It would be clearer to compare the raw bits of the values.

For example, with SELECT float8send(ID) FROM SUBMISSION_QUEUE,
you should see \x4044000000000000 if ID is 40, and you should see
\x3f40624dd2f1a9fc if ID is 0.0005.

Likewise, on the Java side,
Long.toHexString(Double.doubleToLongBits(id)) should also show you
4044000000000000 for the value 40, and 3f40624dd2f1a9fc for the
value 0.0005.

If you end up finding that the text/binary transmission format
sometimes causes the Java value not to have the same bits as the
PostgreSQL value, that information could be of interest on the
pgsql-jdbc list.

Regards,
Chapman Flack