Re: Postgres jdbc driver inconsistent behaviour with double precession

On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <rahul(dot)uniyal00(at)gmail(dot)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