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 1qY9pV-000vbX-Rj for pgsql-jdbc@arkaria.postgresql.org; Mon, 21 Aug 2023 18:40:45 +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 1qY9pU-009i3i-NI for pgsql-jdbc@arkaria.postgresql.org; Mon, 21 Aug 2023 18:40:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY9pU-009i20-BJ for pgsql-jdbc@lists.postgresql.org; Mon, 21 Aug 2023 18:40:44 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qY9pO-000CUx-QX for pgsql-jdbc@lists.postgresql.org; Mon, 21 Aug 2023 18:40:42 +0000 Received: from mail-lj1-f171.google.com (mail-lj1-f171.google.com [209.85.208.171]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 692402031B for ; Mon, 21 Aug 2023 14:40:37 -0400 (EDT) Received: by mail-lj1-f171.google.com with SMTP id 38308e7fff4ca-2bcc4347d2dso12772121fa.0 for ; Mon, 21 Aug 2023 11:40:37 -0700 (PDT) X-Gm-Message-State: AOJu0YzeMPNNtB9kdPO8SdtDWCjUazZRp99gs9xSHpYidkK2wug5w6xn uCDKI+ksVg1pI3nk8fglYEA/j/7aD+8rk5q/V7k= X-Google-Smtp-Source: AGHT+IGN2jEswRUFkCvePfJW3kyQAYUc0bqZIeY84BmxMMWwARIa2yS2p1jjsH3yLQ3Nx18HMIlm02+3z2RVfW4sJg4= X-Received: by 2002:a2e:80c3:0:b0:2bc:c004:cc22 with SMTP id r3-20020a2e80c3000000b002bcc004cc22mr2314575ljg.33.1692643235652; Mon, 21 Aug 2023 11:40:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Mon, 21 Aug 2023 14:40:18 -0400 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: The same prepared query yield "-1" the first six times and then "-1.0" To: Edoardo Panfili Cc: pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000caeb5d06037336f9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000caeb5d06037336f9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Edoardo, Thanks for the question. So here is what is happening . As you can see after 5 times this query starts using a named statement (S_1). Additionally when this occurs we switch to binary data instead of text. That said, I don't think the results should change. Thanks for the repro. I will have a look. Dave Cramer www.postgres.rocks On Mon, 21 Aug 2023 at 13:37, Edoardo Panfili wrote: > Hy, I posted this topic in pgsql-general and they say that maybe it is > better to ask in psql-jdbc so... > > I am using > postgresql version: 15.3 (Debian 15.3-0+deb12u1) > org.postgresql.postgresql JDBC driver version: 42.6.0 > via Java 17.0.7 > > I discovered an unattended (for me) situation: when I execute > 10 times the same prepared query the result is not always the same. > > I wrote a little test case to show this. > > this is the db that I am using: > CREATE TABLE number( > name character varying(30) NOT NULL, > dim1 real DEFAULT '-1' NOT NULL > ); > insert into number (name) VALUES('first'); > > and the test program: > static final String DB_URL =3D "jdbc:postgresql://192.168.64.7:5432/testd= b"; > static final String USER =3D "user"; > static final String PASS =3D "password"; > public static void main(String[] args) throws Exception { > Connection conn =3D DriverManager.getConnection(DB_URL, USER, PASS); > for(int i=3D0; i<10; i++) { > try( PreparedStatement istruzioneCelle =3D conn.prepareStatement( > "SELECT dim1 FROM number WHERE name=3D'first'") ) { > ResultSet rs =3D istruzioneCelle.executeQuery(); > rs.next(); > System.out.print("p: "+rs.getString("dim1")+"\n=E2=80=9D); > } catch (SQLException e) { > e.printStackTrace(); > } > } > conn.close(); > } > > The attended result was a sequence of ten equal values but this is the > actual result: > > p: -1 > p: -1 > p: -1 > p: -1 > p: -1 > p: -1.0 > p: -1.0 > p: -1.0 > p: -1.0 > p: -1.0 > > Semantically the same but not the representation. > > All works fine if I open and close the connection after every single quer= y > but in production I am using pooled connections. > This is what I can read in postgresql logs (it seems that after 4 queries > the statement becomes named and the result changes after the second call = to > the named query): > > 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute > : SET extra_float_digits =3D 3 > 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute > : SET application_name =3D 'PostgreSQL JDBC Driver' > 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute > : SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute > : SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute > : SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute > : SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D'first' > 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1: > SELECT dim1 FROM number WHERE name=3D=E2=80=98first' > > Can I do something to avoid this problem? > > thank you > Edoardo > > > --000000000000caeb5d06037336f9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Edoardo,

Thanks for the questio= n.

So here is what is happening . As you can see a= fter 5 times this query starts using a named statement (S_1). Additionally = when this occurs we switch=C2=A0to binary data instead of text. That said, = I don't think the results should change.=C2=A0
Thanks for the= repro. I will have a look.
Dave = Cramer
www.postgres.rocks


On Mon, 21 A= ug 2023 at 13:37, Edoardo Panfili <edoardopa@gmail.com> wrote:
Hy, I posted this topic in pgsql-general and they say = that maybe it is
better to ask in psql-jdbc so...

I am using
postgresql version: 15.3 (Debian 15.3-0+deb12u1)
org.postgresql.postgresql JDBC driver version: 42.6.0
via Java 17.0.7

I discovered an unattended (for me) situation: when I execute
10 times the same prepared query the result is not always the same.

I wrote a little test case to show this.

this is the db that I am using:
CREATE TABLE number(
name character varying(30) NOT NULL,
dim1 real DEFAULT '-1' NOT NULL
);
insert into number (name) VALUES('first');

and the test program:
static final String DB_URL =3D "jdbc:postgresql://192.168.64.7:5= 432/testdb";
static final String USER =3D "user";
static final String PASS =3D "password";
public static void main(String[] args) throws Exception {
=C2=A0 Connection conn =3D DriverManager.getConnection(DB_URL, USER, PASS);=
=C2=A0 for(int i=3D0; i<10; i++) {
=C2=A0 =C2=A0 try( PreparedStatement istruzioneCelle =3D conn.prepareStatem= ent(
=C2=A0 =C2=A0 =C2=A0 "SELECT dim1 FROM number WHERE name=3D'first&= #39;") ) {
=C2=A0 =C2=A0 =C2=A0 ResultSet rs =3D istruzioneCelle.executeQuery();
=C2=A0 =C2=A0 =C2=A0 rs.next();
=C2=A0 =C2=A0 =C2=A0 System.out.print("p: "+rs.getString("di= m1")+"\n=E2=80=9D);
=C2=A0 =C2=A0 } catch (SQLException e) {
=C2=A0 =C2=A0 =C2=A0 e.printStackTrace();
=C2=A0 =C2=A0 }
=C2=A0 }
=C2=A0 conn.close();
}

The attended result was a sequence of ten equal values but this is the
actual result:

p: -1
p: -1
p: -1
p: -1
p: -1
p: -1.0
p: -1.0
p: -1.0
p: -1.0
p: -1.0

Semantically the same but not the representation.

All works fine if I open and close the connection after every single query<= br> but in production I am using pooled connections.
This is what I can read in postgresql logs (it seems that after 4 queries the statement becomes named and the result changes after the second call to=
the named query):

2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute
<unnamed>: SET extra_float_digits =3D 3
2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute
<unnamed>: SET application_name =3D 'PostgreSQL JDBC Driver'<= br> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute
<unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute
<unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute
<unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute
<unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D'first'
2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1:
SELECT dim1 FROM number WHERE name=3D=E2=80=98first'

Can I do something to avoid this problem?

thank you
Edoardo


--000000000000caeb5d06037336f9--