Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmKkw-007IDh-CU for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Mar 2024 21:42:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rmKku-006RxJ-TJ for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Mar 2024 21:42:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmKcH-006NfD-GO for pgsql-bugs@lists.postgresql.org; Mon, 18 Mar 2024 21:33:58 +0000 Received: from mail-pl1-x634.google.com ([2607:f8b0:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rmKcF-005FeW-F0 for pgsql-bugs@lists.postgresql.org; Mon, 18 Mar 2024 21:33:57 +0000 Received: by mail-pl1-x634.google.com with SMTP id d9443c01a7336-1dddbe47ac1so32662175ad.1 for ; Mon, 18 Mar 2024 14:33:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710797633; x=1711402433; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=wi1XjMkiNwHGfm4hZp1oKvbpFB1mf5hZgnPp8iTL+ZQ=; b=gtbDqC1vY+Fm5xXqcU05p4+8rYieRbgoUZZEuSqvy1H2PbTgX3m2au4qXPrHUg2MrA SWqy1KtMhForCoUZTp2LrDlhdRLETU55KmcfXaL4l26xIsEIsKv/sUiy0bBYseAxyRia 9BaHk6mP0h6BrqBjUCkeykcNYs4boZFQ/+X2DX6Dc3TCULnUV8v6BahT5fRKBkK7SVb5 9XGL+g5SppQptbdd4ZQxPk+fzp4QK6PXBpkqZ1mSX8lLtE3ubphgqwkJiX/IL/pQodi2 9+P/HiOuqkd8VzNalRJeEY6TSPU99VjY8pDZSihgop9HLZphIRTTK1olCtZSt6jeOgKC KeUw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710797633; x=1711402433; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=wi1XjMkiNwHGfm4hZp1oKvbpFB1mf5hZgnPp8iTL+ZQ=; b=IdbISs9RXEyxMiUkEULRAejxCU/225UMu+cXbF8spqDqPMyCJJ0A+2//vfNBTG8I0D LBH94h8hQhCijVUmxctwtzvLLNyqRNHivRL5SshzAudk5lY+WQQRt51kog5wHYLluQ+K yQW+/WjzqxUtqjPuESmtbP6M/JANTjC7kXFX7Ptj8Uhy5Naje6JvqQyK03+SRO9C4qC+ 996gy6FN3lqr8oPN334eqsQ+tXJeGO6AiWROyv223D8e9bfcQ//6Y8y7kLgFoSeBBfkh tGlwFjH5kB7YX90wu3DOez7DIpoerbV0aDwMBoFGFG9Nh1jBAXLAzy8OB1yIcWatwSAa oU+w== X-Gm-Message-State: AOJu0YymROodmO4NyP1UxT/SMyLbuGMxNhVESwYbzKkUBrWLLbPgP0a3 wJuyRWfkDvqkVdKkx7Y7kIHi59oKXtCGeEV23f18h8Nt4ejE+XH/vekamg026no= X-Google-Smtp-Source: AGHT+IHhpjrvb+Vtbi0WfiUZXUaxbK770xh2arTTFmDoMaFO4AxPL4oAJmdfp6GyAPKfgMXmbvax1g== X-Received: by 2002:a17:902:e882:b0:1df:f675:5ff5 with SMTP id w2-20020a170902e88200b001dff6755ff5mr1341602plg.18.1710797632774; Mon, 18 Mar 2024 14:33:52 -0700 (PDT) Received: from smtpclient.apple ([49.43.162.187]) by smtp.gmail.com with ESMTPSA id q6-20020a170902a3c600b001dede7dd3c7sm9407450plb.111.2024.03.18.14.33.51 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 18 Mar 2024 14:33:52 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-8E14C7A5-6DBC-426A-8C25-7F8D61868A5D Content-Transfer-Encoding: 7bit From: Rahul Uniyal Mime-Version: 1.0 (1.0) Subject: Re: Postgres jdbc driver inconsistent behaviour with double precession Date: Tue, 19 Mar 2024 03:03:40 +0530 Message-Id: <5DEDF4D2-BA2B-4975-89EF-5E8CDB9B1AD1@gmail.com> References: Cc: pgsql-bugs@lists.postgresql.org In-Reply-To: To: Dave Cramer X-Mailer: iPhone Mail (21D61) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-8E14C7A5-6DBC-426A-8C25-7F8D61868A5D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Dave , 

Will che= ck 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 dr= iver for double precision data type.

1- When the value in DB is 40 an= d fetched value is also 40
     A - In the QueryExecuterIm= pl class method - receiveFields() , we create Fields metadata = ;

     private Field[] receiveFields() throws IOExcept= ion {
    pgStream.receiveInteger4(); // MESSAGE SIZE
 =   int size =3D pgStream.receiveInteger2();
    Field[] fi= elds =3D new Field[size];

    if (LOGGER.isLoggable(Level.FI= NEST)) {
      LOGGER.log(Level.FINEST, " <=3DBE RowDes= cription({0})", size);
    }

    for (int i =3D= 0; i < fields.length; i++) {
      String columnLabel =3D= pgStream.receiveCanonicalString();
      int tableOid =3D= pgStream.receiveInteger4();
      short positionInTable =3D= (short) pgStream.receiveInteger2();
      int typeOid =3D= pgStream.receiveInteger4();
      int typeLength =3D pgSt= ream.receiveInteger2();
      int typeModifier =3D pgStrea= m.receiveInteger4();
      int formatType =3D pgStrea= m.receiveInteger2();
      fields[i] =3D new Field(columnL= abel,
          typeOid, typeLength, typeModifie= r, tableOid, positionInTable);
      fields[i].setFor= mat(formatType);

      LOGGER.log(Level.FINEST, " &nbs= p;      {0}", fields[i]);
    }

  &n= bsp; return fields;
  }

Output of this method is - [Fiel= d(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 BigDe= cimal getBigDecimal(@Positive int columnIndex) throws SQLException {
&nbs= p;                  return getB= igDecimal(columnIndex, -1);
            &nb= sp;   }
      and then it calls the method 
&= nbsp;      @Pure
  private @Nullable Number getNumeri= c(
      int columnIndex, int scale, boolean allowNaN) thr= ows SQLException {
    byte[] value =3D getRawValue(columnIndex= );
    if (value =3D=3D null) {
      return n= ull;
    }

    if (isBinary(columnIndex)) {      int sqlType =3D getSQLType(columnIndex);
  &nb= sp;   if (sqlType !=3D Types.NUMERIC && sqlType !=3D Types.DECI= MAL) {
        Object obj =3D internalGetObject(colum= nIndex, fields[columnIndex - 1]);
        if (obj =3D= =3D null) {
          return null;
  &nb= sp;     }
        if (obj instanceof Long |= | obj instanceof Integer || obj instanceof Byte) {
      &= nbsp;   BigDecimal res =3D BigDecimal.valueOf(((Number) obj).longValue(= ));
          res =3D scaleBigDecimal(res, scale= );
          return res;
     =   }
        return toBigDecimal(trimMoney(Strin= g.valueOf(obj)), scale);
      } else {
    &= nbsp;   Number num =3D ByteConverter.numeric(value);
    &= nbsp;   if (allowNaN && Double.isNaN(num.doubleValue())) {
&= nbsp;         return Double.NaN;
     =   }

        return num;
    &n= bsp; }
    }
Since the column format is text and not binary i= t 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)
&nb= sp;  In this case the field metadata is -

   [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), Field(ocod= e,VARCHAR,65535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VA= RCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T), Field(force_generatio= n_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 f= ormat is Binary Type hence in  PgResultSet  class and in Numeric m= ethod condition  isBinary(columnIndex) is true.
and it returns = DOUBLE from there result in 40.0

Now i am not sure for the same tabl= e and same column why we have two different format and this issue is intermi= ttent.



On 19-Mar-2024, at 1:48=E2=80=AFAM, Dave Cramer <davecramer@postgr= es.rocks> wrote:

=EF=BB=BF


On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote:
<= /div>

Hello Team,

Hope everyone is doing well her= e.

I am writing this email to unde= rstand an issue I'm facing when fetching data in our Java application. We ar= e using PostgreSQL JDBC Driver version 42.6.0.

Issue:

We are encountering an issue wh= ere the double precision data type in PostgreSQL is giving some intermittent= results when fetching data. For example, in the database the value is 40, b= ut 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 t= his issue is caused by the different data formats, such as Text and Binary. T= here is some logic in the PgResultSet class that converts values based on th= is data format.

Example:

Below is an example where we ar= e 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 f= ormats are coming for the same table.

Schema:

Below is the schema for the tab= le used:

SQL

 

 CREATE TABLE IF NOT EXISTS SUB= MISSION_QUEUE(
  ID          &n= bsp;    DOUBLE PRECISION,
  CLIENT_ID         &= nbsp; DOUBLE PRECISION,
  OCODE VARCHAR(20) NOT NULL,
  PAYLOAD_TYPE        VARCHAR(2= 0),
  REPOSITORY VARCHAR(16),
  SUB_REPOSITORY        &n= bsp; VARCHAR(20),
  FORCE_GENERATION_FLAG   BOOLEAN,
IS_JMX_CALL BOOLEAN,
INSTANCE_ID           DOUB= LE PRECISION,
CREATE_TS         TIMESTAMP(6) NOT N= ULL,
);


Request:

Team, would it be possible to g= ive some insight on this issue? Any help would be greatly appreciated.

As for why. After a prepared statement has been u= sed 5 times we switch to binary. You can disable this using prepareThresh= old=3D0

Can you do me a favour and confirm this i= s the case for the latest version of the driver?
Dave Cramer
www.po= stgres.rocks


= --Apple-Mail-8E14C7A5-6DBC-426A-8C25-7F8D61868A5D--