Re: BUG #13979: Strange pg_stat_statements results with PREPARE/EXECUTE

On Tuesday, February 23, 2016, <kasahara(dot)tatsuhito(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13979
> Logged by: Kasahara Tatsuhito
> Email address: kasahara(dot)tatsuhito(at)gmail(dot)com <javascript:;>
> PostgreSQL version: 9.5.1
> Operating system: RHEL
> Description:
>
> Hi, I noticed that in some cases pg_stat_statements shows strange results.
> (example)
>
> =# CREATE TABLE tt(c1 int);
> =# INSERT INTO tt SELECT generate_series(1,10);
> =# SELECT pg_stat_statements_reset();
>
> Then, performed simple SELECTs via java. (see the end of this mail)
> After that, I got the following pg_stat_statements result, and ISTM it's
> reasonable.
>
> =# SELECT queryid, calls, query FROM pg_stat_statements WHERE query LIKE
> '%tt%';
> queryid | calls | query
> -----------+-------+--------------------------------
> 575935600 | 10 | SELECT * FROM tt WHERE c1 = $1
>
>
> Next, reset the pg_stat_statements and performed PREPARE and EXECUTE via
> psql.
>
> =# SELECT pg_stat_statements_reset();
> =# PREPARE p1(int) AS SELECT * FROM tt WHERE c1 = $1;
> =# EXECUTE p1(1);
> =# SELECT queryid, calls, query FROM pg_stat_statements WHERE query LIKE
> '%tt%';
> queryid | calls | query
> -----------+-------+----------------------------------------------------
> 575935600 | 1 | PREPARE p1(int) AS SELECT * FROM tt WHERE c1 = $1;
> (1 row)
>
> Then, performed same SELECTs via java.
> After that, I got a bit strange pg_stat_statements result..
>
> =# SELECT queryid, calls, query FROM pg_stat_statements WHERE query LIKE
> '%tt%';
> queryid | calls | query
> -----------+-------+----------------------------------------------------
> 575935600 | 11 | PREPARE p1(int) AS SELECT * FROM tt WHERE c1 = $1;
> (1 row)
>
>
> I'm not sure it's expected behaviors, but seems a bug.
>
> Best regards,
>
> // Java Test code
> import java.lang.*;
> import java.sql.*;
>
> class Test {
> public static void main(String[] args) {
> String driver = "org.postgresql.Driver";
> String url =
>
> "jdbc:postgresql://localhost/postgres?user=postgres&ApplicationName=my_jdbc_test";
> try{
> Connection con = DriverManager.getConnection(url);
> con.setAutoCommit(false);
> String sql1 = "SELECT * FROM tt WHERE c1 = ?";
> PreparedStatement ps1 = con.prepareStatement(sql1);
>
> for (int i=1; i<=10; i++) {
> ps1.setInt(1,i);
> ResultSet rs = ps1.executeQuery();
> while(rs.next()){
> System.out.println("results c1:" + rs.getInt(1));
> }
> }
>
> con.commit();
> ps1.close();
> con.close();
> }catch (Exception e){
> e.printStackTrace();
> }
> }
> }
>
>
You need to tell people what exactly you think is strange/what you would
expect to see.

David J.