MIME-Version: 1.0
From: Edoardo Panfili <edoardopa@gmail.com>
Date: Mon, 21 Aug 2023 19:37:25 +0200
Message-ID: 
 <CAKyg5Sj=85niw8=ZhX-quHaCQTPhG40REi1s+HYF6Snz4Dfi7Q@mail.gmail.com>
Subject: The same prepared query yield "-1" the first six times and then
 "-1.0"
To: pgsql-jdbc@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAKyg5Sj%3D85niw8%3DZhX-quHaCQTPhG40REi1s%2BHYF6Snz4Dfi7Q%40mail.gmail.com>
Precedence: bulk

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/testdb"=
;
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 query
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'
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