Re: Postgres jdbc driver inconsistent behaviour with double precession

On Mon, 18 Mar 2024 at 17:33, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote:

> Hi Dave ,
>
> Will check this in the latest one but below is my detail observation .
> This issue is intermittent .
>
>
> Below are my observations when i was debugging the code of postgres-jdbc
> driver for double precision data type.
>
> 1- When the value in DB is 40 and fetched value is also 40
>      A - In the QueryExecuterImpl class method - receiveFields() , we
> create Fields metadata
>
>      private Field[] receiveFields() throws IOException {
>     pgStream.receiveInteger4(); // MESSAGE SIZE
>     int size = pgStream.receiveInteger2();
>     Field[] fields = new Field[size];
>
>     if (LOGGER.isLoggable(Level.FINEST)) {
>       LOGGER.log(Level.FINEST, " <=BE RowDescription({0})", size);
>     }
>
>     for (int i = 0; i < fields.length; i++) {
>       String columnLabel = pgStream.receiveCanonicalString();
>       int tableOid = pgStream.receiveInteger4();
>       short positionInTable = (short) pgStream.receiveInteger2();
>       int typeOid = pgStream.receiveInteger4();
>       int typeLength = pgStream.receiveInteger2();
>       int typeModifier = pgStream.receiveInteger4();
>       int formatType = pgStream.receiveInteger2();
>       fields[i] = new Field(columnLabel,
>           typeOid, typeLength, typeModifier, tableOid, positionInTable);
>       fields[i].setFormat(formatType);
>
>       LOGGER.log(Level.FINEST, "        {0}", fields[i]);
>     }
>
>     return fields;
>   }
>
> Output of this method is - [Field(id,FLOAT8,8,T),
> Field(client_id,FLOAT8,8,T), Field(create_ts,TIMESTAMP,8,T),
> Field(force_generation_flag,VARCHAR,65535,T),
> Field(instance_id,FLOAT8,8,T), Field(is_jmx_call,VARCHAR,65535,T),
> Field(ocode,VARCHAR,65535,T), Field(payload_type,VARCHAR,65535,T),
> Field(repository,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T)]
>
>
>
>
>      B- Then in the class PgResultSet , it calls the method
>               public java.math.@Nullable BigDecimal
> getBigDecimal(@Positive int columnIndex) throws SQLException {
>                    return getBigDecimal(columnIndex, -1);
>                 }
>       and then it calls the method
>        @Pure
>   private @Nullable Number getNumeric(
>       int columnIndex, int scale, boolean allowNaN) throws SQLException {
>     byte[] value = getRawValue(columnIndex);
>     if (value == null) {
>       return null;
>     }
>
>     if (isBinary(columnIndex)) {
>       int sqlType = getSQLType(columnIndex);
>       if (sqlType != Types.NUMERIC && sqlType != Types.DECIMAL) {
>         Object obj = internalGetObject(columnIndex, fields[columnIndex -
> 1]);
>         if (obj == null) {
>           return null;
>         }
>         if (obj instanceof Long || obj instanceof Integer || obj
> instanceof Byte) {
>           BigDecimal res = BigDecimal.valueOf(((Number) obj).longValue());
>           res = scaleBigDecimal(res, scale);
>           return res;
>         }
>         return toBigDecimal(trimMoney(String.valueOf(obj)), scale);
>       } else {
>         Number num = ByteConverter.numeric(value);
>         if (allowNaN && Double.isNaN(num.doubleValue())) {
>           return Double.NaN;
>         }
>
>         return num;
>       }
>     }
> Since the column format is text and not binary it converts the value to
> BigDecimal and give back the value as 40 .
>
> 2- When the value in DB is 40 and fetched value is 40.0 (trailing zero)
>    In this case the field metadata is -
>
>    [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B),
> Field(ocode,VARCHAR,65535,T), Field(payload_type,VARCHAR,65535,T),
> Field(repository,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T),
> Field(force_generation_flag,VARCHAR,65535,T),
> Field(is_jmx_call,VARCHAR,65535,T), Field(instance_id,FLOAT8,8,B),
> Field(create_ts,TIMESTAMP,8,B)]
>
> Now since the format is Binary Type hence in  PgResultSet  class and in
> Numeric method condition  isBinary(columnIndex) is true.
> and it returns  DOUBLE from there result in 40.0
>
> Now i am not sure for the same table and same column why we have two
> different format and this issue is intermittent.
>
>
> On 19-Mar-2024, at 1:48 AM, Dave Cramer <davecramer@postgres.rocks> wrote:
>
> 
>
>
> 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
>
>

As I said after 5 times with the same statement the driver switches to
binary.
You can disable this behaviour with prepareThreshold=0

Dave