pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres jdbc driver inconsistent behaviour with double precession
7+ messages / 2 participants
[nested] [flat]
* Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-16 17:55 Rahul Uniyal <rahul.uniyal00@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rahul Uniyal @ 2024-03-16 17:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
Thanks,=
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-18 20:18 Dave Cramer <davecramer@postgres.rocks>
parent: Rahul Uniyal <rahul.uniyal00@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2024-03-18 20:18 UTC (permalink / raw)
To: Rahul Uniyal <rahul.uniyal00@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <rahul.uniyal00@gmail.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-18 21:33 Rahul Uniyal <rahul.uniyal00@gmail.com>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 7+ messages in thread
From: Rahul Uniyal @ 2024-03-18 21:33 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: pgsql-bugs@lists.postgresql.org
--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--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-18 22:36 Dave Cramer <davecramer@postgres.rocks>
parent: Rahul Uniyal <rahul.uniyal00@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2024-03-18 22:36 UTC (permalink / raw)
To: Rahul Uniyal <rahul.uniyal00@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Mon, 18 Mar 2024 at 17:33, Rahul Uniyal <rahul.uniyal00@gmail.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.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.
>
>
> On 19-Mar-2024, at 1:48 AM, Dave Cramer <davecramer@postgres.rocks> wrote:
>
>
>
>
> On Sat, 16 Mar 2024 at 15:30, Rahul Uniyal <rahul.uniyal00@gmail.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-19 09:52 Dave Cramer <davecramer@postgres.rocks>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2024-03-19 09:52 UTC (permalink / raw)
To: Rahul Uniyal <rahul.uniyal00@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 18 Mar 2024 at 23:11, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote:
>
> Thanks Dave,
>
> 1- Is there any performance impact with this change ? As we have lot of
> double precession fields.
>
Well the reason we do it is for performance. It saves a round trip, but it
costs one to get the info we need. So all in all it has minimal impact on
pefrormance
>
> 2- This prepareThreshold=0 property should be pass through connection url
> ?
>
Yes
Dave
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-19 11:39 Dave Cramer <davecramer@postgres.rocks>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2024-03-19 11:39 UTC (permalink / raw)
To: Rahul Uniyal <rahul.uniyal00@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, 19 Mar 2024 at 06:55, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote:
>
> Thanks Dave for this much needed information.
>
> I checked with by giving prepareThreshold = 0 it is working as expected .
>
> Also I tried another solution which is also working .
>
> I am setting binaryTransferDisable=FLOAT8 in the connection url and this
> seems also working fine .
>
> Is this approach is also recommended?
>
This might be better as it will retain the prepared statement behaviour for
all the other types.
P.S. please reply all so that others see the answers.
Dave
>
> Thanks ,
> Rahul
>
> On 19-Mar-2024, at 3:22 PM, Dave Cramer <davecramer@postgres.rocks> wrote:
>
>
>
>
> On Mon, 18 Mar 2024 at 23:11, Rahul Uniyal <rahul.uniyal00@gmail.com>
> wrote:
>
>>
>> Thanks Dave,
>>
>> 1- Is there any performance impact with this change ? As we have lot of
>> double precession fields.
>>
>
> Well the reason we do it is for performance. It saves a round trip, but it
> costs one to get the info we need. So all in all it has minimal impact on
> pefrormance
>
>>
>> 2- This prepareThreshold=0 property should be pass through connection
>> url ?
>>
>
> Yes
>
> Dave
>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres jdbc driver inconsistent behaviour with double precession
@ 2024-03-19 18:15 Rahul Uniyal <rahul.uniyal00@gmail.com>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 0 replies; 7+ messages in thread
From: Rahul Uniyal @ 2024-03-19 18:15 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
--Apple-Mail-69CC7A4D-3178-4F4C-ACEB-997E07571806
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"><br id=3D"lineBreakAtBeginningOfSignature">=
<div dir=3D"ltr">Thanks Dave for the much needed help. Thank you so much .</=
div><div dir=3D"ltr">I am using binaryTransferDisable=3DFLOAT8 to resolve th=
is problem.</div><div dir=3D"ltr"><br></div><div dir=3D"ltr">Thanks </d=
iv><div dir=3D"ltr">Rahul </div><div dir=3D"ltr"><br><blockquote type=3D=
"cite">On 19-Mar-2024, at 5:09=E2=80=AFPM, 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"><br clear=3D"all"><d=
iv><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><br></div></d=
iv></div></div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_at=
tr">On Tue, 19 Mar 2024 at 06:55, Rahul Uniyal <<a href=3D"mailto:rahul.u=
niyal00@gmail.com">rahul.uniyal00@gmail.com</a>> wrote:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><br id=3D"m_-31350=
06102984822208lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Thanks Dave f=
or this much needed information.</div><div dir=3D"ltr"><br></div><div dir=3D=
"ltr">I checked with by giving prepareThreshold =3D 0 it is working as expec=
ted . </div><div dir=3D"ltr"><br></div><div dir=3D"ltr">Also I tried an=
other solution which is also working . </div><div dir=3D"ltr"><br></div=
><div dir=3D"ltr">I am setting binaryTransferDisable=3DFLOAT8 in the connect=
ion url and this seems also working fine . </div><div dir=3D"ltr"><br><=
/div><div dir=3D"ltr">Is this approach is also recommended? </div></div=
></blockquote><div><br></div><div>This might be better as it will retain the=
prepared statement behaviour for all the other types.</div><div>P.S. please=
reply all so that others see the answers.</div><div><br></div><div>Dave&nbs=
p;</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><=
div dir=3D"ltr"><br></div><div dir=3D"ltr">Thanks ,</div><div dir=3D"ltr">Ra=
hul </div><div dir=3D"ltr"><br><blockquote type=3D"cite">On 19-Mar-2024=
, at 3:22=E2=80=AFPM, Dave Cramer <davecramer@postgres.rocks> wrote:<b=
r><br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF=
<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">On Mon, 18 Mar 2024 at 23:11, Rahul Uni=
yal <<a href=3D"mailto:rahul.uniyal00@gmail.com" target=3D"_blank">rahul.=
uniyal00@gmail.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote"=
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"auto"><br id=3D"m_-3135006102984822208m_-3471697=
71142216717lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Thanks Dave,</d=
iv><div dir=3D"ltr"><br></div><div dir=3D"ltr">1- Is there any performance i=
mpact with this change ? As we have lot of double precession fields.</div></=
div></blockquote><div><br></div><div>Well the reason we do it is for perform=
ance. It saves a round trip, but it costs one to get the info we need. So al=
l in all it has minimal impact on pefrormance </div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"ltr"><br></div><d=
iv dir=3D"ltr">2- This <span style=3D"color:rgb(31,35,40);font-family:-=
apple-system,system-ui,"Segoe UI","Noto Sans",Helvetica,=
Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";fo=
nt-size:16px">prepareThreshold=3D0 property should be pass through connectio=
n url ? </span></div></div></blockquote><div><br></div><div>Yes</div><d=
iv><br></div><div>Dave</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div dir=3D"auto"><blockquote type=3D"cite"><div dir=3D"ltr">
</div></blockquote></div></blockquote></div></div>
</div></blockquote></div></blockquote></div></div>
</div></blockquote></body></html>=
--Apple-Mail-69CC7A4D-3178-4F4C-ACEB-997E07571806--
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-03-19 18:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-16 17:55 Postgres jdbc driver inconsistent behaviour with double precession Rahul Uniyal <rahul.uniyal00@gmail.com>
2024-03-18 20:18 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-18 21:33 ` Rahul Uniyal <rahul.uniyal00@gmail.com>
2024-03-18 22:36 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-19 09:52 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-19 11:39 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-19 18:15 ` Rahul Uniyal <rahul.uniyal00@gmail.com>
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