Thread

Re: Postgres jdbc driver inconsistent behaviour with double precession

Dave Cramer <davecramer@postgres.rocks> — 2024-03-18T20:18:08Z

On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote:

>
> 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.
>
As for why. After a prepared statement has been used 5 times we switch to
binary. You can disable this using prepareThreshold=0

Can you do me a favour and confirm this is the case for the latest version
of the driver?
Dave Cramer
www.postgres.rocks