From: Rahul Uniyal <rahul.uniyal00@gmail.com>
To: Dave Cramer <davecramer@postgres.rocks>
Cc: pgsql-bugs@lists.postgresql.org
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> (raw)
In-Reply-To: <CADK3HHJNLOJHNnbK52BGrFt7wgZH=pJxurkY=3AQf=-6Q8WVhg@mail.gmail.com>
References: <CADK3HHJNLOJHNnbK52BGrFt7wgZH=pJxurkY=3AQf=-6Q8WVhg@mail.gmail.com>
--Apple-Mail-8E14C7A5-6DBC-426A-8C25-7F8D61868A5D
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi Dave , <div><br></div><div>Will che=
ck this in the latest one but below is my detail observation . This issue is=
intermittent . </div><div><br></div><div><p style=3D"-webkit-text-size=
-adjust: auto; margin: 0px 0px 16px; font-stretch: normal; font-size: 12px; l=
ine-height: normal; font-family: Helvetica; font-size-adjust: none; font-ker=
ning: auto; font-variant-alternates: normal; font-variant-ligatures: normal;=
font-variant-numeric: normal; font-variant-east-asian: normal; font-variant=
-position: normal; font-feature-settings: normal; font-optical-sizing: auto;=
font-variation-settings: normal; background-color: rgb(255, 255, 255);"><br=
>Below are my observations when i was debugging the code of postgres-jdbc dr=
iver for double precision data type.<br><br>1- When the value in DB is 40 an=
d fetched value is also 40<br> A - In the QueryExecuterIm=
pl class method - receiveFields() , we create Fields metadata =
;<br><br> private Field[] receiveFields() throws IOExcept=
ion {<br> pgStream.receiveInteger4(); // MESSAGE SIZE<br> =
int size =3D pgStream.receiveInteger2();<br> Field[] fi=
elds =3D new Field[size];<br><br> if (LOGGER.isLoggable(Level.FI=
NEST)) {<br> LOGGER.log(Level.FINEST, " <=3DBE RowDes=
cription({0})", size);<br> }<br><br> for (int i =3D=
0; i < fields.length; i++) {<br> String columnLabel =3D=
pgStream.receiveCanonicalString();<br> int tableOid =3D=
pgStream.receiveInteger4();<br> short positionInTable =3D=
(short) pgStream.receiveInteger2();<br> int typeOid =3D=
pgStream.receiveInteger4();<br> int typeLength =3D pgSt=
ream.receiveInteger2();<br> int typeModifier =3D pgStrea=
m.receiveInteger4();<br> int formatType =3D pgStrea=
m.receiveInteger2();<br> fields[i] =3D new Field(columnL=
abel,<br> typeOid, typeLength, typeModifie=
r, tableOid, positionInTable);<br> fields[i].setFor=
mat(formatType);<br><br> LOGGER.log(Level.FINEST, " &nbs=
p; {0}", fields[i]);<br> }<br><br> &n=
bsp; return fields;<br> }<br><br>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)]</p><p style=3D"-webkit-text-size-adjust: auto; m=
argin: 0px 0px 16px; font-stretch: normal; font-size: 12px; line-height: nor=
mal; font-family: Helvetica; font-size-adjust: none; font-kerning: auto; fon=
t-variant-alternates: normal; font-variant-ligatures: normal; font-variant-n=
umeric: normal; font-variant-east-asian: normal; font-variant-position: norm=
al; font-feature-settings: normal; font-optical-sizing: auto; font-variation=
-settings: normal; background-color: rgb(255, 255, 255);"> </p><p style=
=3D"-webkit-text-size-adjust: auto; margin: 0px 0px 16px; font-stretch: norm=
al; font-size: 12px; line-height: normal; font-family: Helvetica; font-size-=
adjust: none; font-kerning: auto; font-variant-alternates: normal; font-vari=
ant-ligatures: normal; font-variant-numeric: normal; font-variant-east-asian=
: normal; font-variant-position: normal; font-feature-settings: normal; font=
-optical-sizing: auto; font-variation-settings: normal; background-color: rg=
b(255, 255, 255);"> <br>  =
;B- Then in the class PgResultSet , it calls the method <br> =
; public java.math.@Nullable BigDe=
cimal getBigDecimal(@Positive int columnIndex) throws SQLException {<br>&nbs=
p; return getB=
igDecimal(columnIndex, -1);<br> &nb=
sp; }<br> and then it calls the method <br>&=
nbsp; @Pure<br> private @Nullable Number getNumeri=
c(<br> int columnIndex, int scale, boolean allowNaN) thr=
ows SQLException {<br> byte[] value =3D getRawValue(columnIndex=
);<br> if (value =3D=3D null) {<br> return n=
ull;<br> }<br><br> if (isBinary(columnIndex)) {<br=
> int sqlType =3D getSQLType(columnIndex);<br> &nb=
sp; if (sqlType !=3D Types.NUMERIC && sqlType !=3D Types.DECI=
MAL) {<br> Object obj =3D internalGetObject(colum=
nIndex, fields[columnIndex - 1]);<br> if (obj =3D=
=3D null) {<br> return null;<br> &nb=
sp; }<br> if (obj instanceof Long |=
| obj instanceof Integer || obj instanceof Byte) {<br> &=
nbsp; BigDecimal res =3D BigDecimal.valueOf(((Number) obj).longValue(=
));<br> res =3D scaleBigDecimal(res, scale=
);<br> return res;<br> =
}<br> return toBigDecimal(trimMoney(Strin=
g.valueOf(obj)), scale);<br> } else {<br> &=
nbsp; Number num =3D ByteConverter.numeric(value);<br> &=
nbsp; if (allowNaN && Double.isNaN(num.doubleValue())) {<br>&=
nbsp; return Double.NaN;<br> =
}<br><br> return num;<br> &n=
bsp; }<br> }<br>Since the column format is text and not binary i=
t converts the value to BigDecimal and give back the value as 40 .<br><br>2-=
When the value in DB is 40 and fetched value is 40.0 (trailing zero)<br>&nb=
sp; In this case the field metadata is -</p><p style=3D"-webkit-text-s=
ize-adjust: auto; margin: 0px 0px 16px; font-stretch: normal; font-size: 12p=
x; line-height: normal; font-family: Helvetica; font-size-adjust: none; font=
-kerning: auto; font-variant-alternates: normal; font-variant-ligatures: nor=
mal; font-variant-numeric: normal; font-variant-east-asian: normal; font-var=
iant-position: normal; font-feature-settings: normal; font-optical-sizing: a=
uto; font-variation-settings: normal; background-color: rgb(255, 255, 255);"=
> [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)] <br><br>Now since the f=
ormat is Binary Type hence in PgResultSet class and in Numeric m=
ethod condition isBinary(columnIndex) is true.<br>and it returns =
DOUBLE from there result in 40.0<br><br>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.</p><div dir=3D"ltr"><br></div><div dir=3D"ltr"><br><blockquote type=3D=
"cite">On 19-Mar-2024, at 1:48=E2=80=AFAM, Dave Cramer <davecramer@postgr=
es.rocks> wrote:<br><br></blockquote></div><blockquote type=3D"cite"><div=
dir=3D"ltr">=EF=BB=BF<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><di=
v dir=3D"ltr"><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" clas=
s=3D"gmail_attr">On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <<a href=3D"m=
ailto:rahul.uniyal00@gmail.com">rahul.uniyal00@gmail.com</a>> wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><br i=
d=3D"m_-7985646807441805566lineBreakAtBeginningOfSignature"><div dir=3D"ltr"=
><p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font=
-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font=
-kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;f=
ont-variant-numeric:normal;font-variant-east-asian:normal;font-feature-setti=
ngs:normal;background-color:rgb(255,255,255)">Hello Team,</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Hope everyone is doing well her=
e.</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">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.</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Issue:</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">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.</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">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.</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Example:</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Below is an example where we ar=
e getting different data formats for the same table:</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Text Format: [Field(id,FLOAT8,8=
,T), Field(client_id,FLOAT8,8,T), Field(create_ts,TIMESTAMP,8,T), ...]</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Binary Format: [Field(id,FLOAT8=
,8,B), Field(client_id,FLOAT8,8,B), ...] (notice some format changes)</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">We are not sure why different f=
ormats are coming for the same table.</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Schema:</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Below is the schema for the tab=
le used:</p>
<p style=3D"margin:0px 0px 16px;font-style:normal;font-variant-caps:normal;f=
ont-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;f=
ont-kerning:auto;font-variant-alternates:normal;font-variant-ligatures:norma=
l;font-variant-numeric:normal;font-variant-east-asian:normal;font-feature-se=
ttings:normal;background-color:rgb(255,255,255)">SQL</p>
<p style=3D"margin:0px 0px 16px;font-style:normal;font-variant-caps:normal;f=
ont-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;f=
ont-kerning:auto;font-variant-alternates:normal;font-variant-ligatures:norma=
l;font-variant-numeric:normal;font-variant-east-asian:normal;font-feature-se=
ttings:normal;background-color:rgb(255,255,255)"> </p>
<p style=3D"margin:0px;font-style:normal;font-variant-caps:normal;font-stret=
ch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-kerni=
ng:auto;font-variant-alternates:normal;font-variant-ligatures:normal;font-va=
riant-numeric:normal;font-variant-east-asian:normal;font-feature-settings:no=
rmal;background-color:rgb(255,255,255)"> CREATE TABLE IF NOT EXISTS SUB=
MISSION_QUEUE(<br>
ID &n=
bsp; DOUBLE PRECISION,<br>
CLIENT_ID &=
nbsp; DOUBLE PRECISION,<br>
OCODE VARCHAR(20) NOT NULL,<br>
PAYLOAD_TYPE VARCHAR(2=
0),<br>
REPOSITORY VARCHAR(16),<br>
SUB_REPOSITORY &n=
bsp; VARCHAR(20),<br>
FORCE_GENERATION_FLAG BOOLEAN,<br>
IS_JMX_CALL BOOLEAN,<br>
INSTANCE_ID DOUB=
LE PRECISION,<br>
CREATE_TS TIMESTAMP(6) NOT N=
ULL,<br>
);</p>
<p style=3D"margin:0px;font-style:normal;font-variant-caps:normal;font-stret=
ch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-kerni=
ng:auto;font-variant-alternates:normal;font-variant-ligatures:normal;font-va=
riant-numeric:normal;font-variant-east-asian:normal;font-feature-settings:no=
rmal;background-color:rgb(255,255,255);min-height:13.8px"><br></p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Request:</p>
<p style=3D"margin:16px 0px;font-style:normal;font-variant-caps:normal;font-=
stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica;font-=
kerning:auto;font-variant-alternates:normal;font-variant-ligatures:normal;fo=
nt-variant-numeric:normal;font-variant-east-asian:normal;font-feature-settin=
gs:normal;background-color:rgb(255,255,255)">Team, would it be possible to g=
ive some insight on this issue? Any help would be greatly appreciated.</p></=
div></div></blockquote><div>As for why. After a prepared statement has been u=
sed 5 times we switch to binary. You can disable this using <span style=
=3D"color:rgb(31,35,40);font-family:-apple-system,"system-ui",&quo=
t;Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Appl=
e Color Emoji","Segoe UI Emoji";font-size:16px">prepareThresh=
old=3D0</span></div><div><br></div>Can you do me a favour and confirm this i=
s the case for the latest version of the driver?<br clear=3D"all"><div><div d=
ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Dave Cramer<div>www.po=
stgres.rocks</div></div></div></div><div><br></div><div><br></div></div></di=
v></div></div>
</div></blockquote></div></body></html>=
--Apple-Mail-8E14C7A5-6DBC-426A-8C25-7F8D61868A5D--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: rahul.uniyal00@gmail.com, davecramer@postgres.rocks, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres jdbc driver inconsistent behaviour with double precession
In-Reply-To: <5DEDF4D2-BA2B-4975-89EF-5E8CDB9B1AD1@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox