Обсуждение: Re: Java : Postgres double precession issue with different data format text and binary
Re: Java : Postgres double precession issue with different data format text and binary
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 <rahul.uniyal00@gmail.com> wrote:
Re: Java : Postgres double precession issue with different data format text and binary
Hi Rahul, On 03/18/24 15:52, Rahul Uniyal wrote: > Since the column format is text and not binary it converts the value > to BigDecimal and give back the value as 40 . > ... > Now since the format is Binary ... 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. I don't see, in this message or your earlier one, which public ResultSet API method your Java client code is calling. It sounds as if you are simply calling getObject, the flavor without a second parameter narrowing the type, and you are finding that the object returned is sometimes of class Double and sometimes of class BigDecimal. Is that accurate? That would seem to be the nub of the issue. You seem to have found that the class of the returned object is influenced by whether text or binary format was used on the wire. I will guess that would be worth reporting to the PGJDBC devs, using the pgsql-jdbc list. The question of why the driver might sometimes use one wire format and sometimes the other seems secondary. There may be some technical explanation, but it would not be very interesting except as an implementation detail, if it did not have this visible effect of changing the returned object's class. For the time being, I assume that if your Java code calls a more specific method, such as getObject(..., BigDecimal.class) or getObject(..., Double.class), or simply getDouble, you will get results of the desired class whatever wire format is used. The issue of the wire format influencing what class of object getObject returns (when a specific class hasn't been requested) is probably worth raising on pgsql-jdbc. Regards, -Chap