Re: Postgres jdbc driver inconsistent behaviour with double precession

On Mon, 18 Mar 2024 at 17:33, Rahul Uniyal <rahul(dot)uniyal00(at)gmail(dot)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(dot)math(dot)(at)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(at)postgres(dot)rocks> wrote:
>
> 
>
>
> 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
>
>

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

Dave