Thread: Postgres jdbc driver inconsistent behaviour with double precession

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

Thanks Dave,

1- Is there any performance impact with this change ? As we have lot of double precession fields.

2- This prepareThreshold=0 property should be pass through connection url ? 

Thanks Dave for this much needed information.

I checked with by giving prepareThreshold = 0 it is working as expected . 

Also I tried another solution which is also working . 

I am setting binaryTransferDisable=FLOAT8 in the connection url and this seems also working fine . 

Is this approach is also recommended? 

Thanks ,

Rahul 

On 19-Mar-2024, at 3:22 PM, Dave Cramer <davecramer@postgres.rocks> wrote:



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

Thanks Dave,

1- Is there any performance impact with this change ? As we have lot of double precession fields.

Well the reason we do it is for performance. It saves a round trip, but it costs one to get the info we need. So all in all it has minimal impact on pefrormance 

2- This prepareThreshold=0 property should be pass through connection url ? 

Yes

Dave