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_TYPEVARCHAR(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

[Rahul Uniyal]

Hello Chapman,

Thanks for the reply and suggestion.

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.

Thanks,

Rahul 

> On 19-Mar-2024, at 1:02 AM, Rahul Uniyal  wrote:
>