Re: [BUG] pg_stat_statements and extended query protocol

[Michael Paquier]

On Wed, Apr 05, 2023 at 05:39:35PM -0700, Andres Freund wrote:
> Seems like a complicated enough facility to benefit from a test or two? Peter
> Eisentraut added support for the extended query protocol to psql, so it
> shouldn't be too hard...

PQsendQueryGuts() does not split yet the bind/describe phase and the
execute phases, so we'd need a couple more libpq APIs to do that, with
more tracking of the state we're currently on when looping across
multiple execute fetches.  My guess is that it is possible to follow a
model similar to JDBC here.  I don't think that's necessarily
complicated, but it is not as straight-forward as it looks.  \bind was 
much more straight-forward than that, as it can feed on a single call
of PQsendQueryParams() after saving a set of parameters.  An \exec
would not completely do that.

Attaching one of the scripts I've played with, in a very rusty java
with no classes or such, for future reference.  Just update CLASSPATH
to point to a copy of the JDBC driver, run it with a java command, and
then look at rows, query in pg_stat_statements.
--
Michael

import java.sql.*;
//import System.out.format;
//import org.postgresql.Driver;

public class Test {
    static final String DB_URL = "jdbc:postgresql://localhost/mydb";

    public static void main(String[] args) {
        Connection conn = null;
        // Open a connection
        try {
            conn = DriverManager.getConnection(DB_URL);
            conn.setAutoCommit(false);
			Integer increment = 0;

			// SELECT query
            PreparedStatement statement = conn.prepareStatement("select * from pg_class");
			statement.setFetchSize(100);
            ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				increment++;
			}
            conn.commit();
			statement.close();
			System.out.format("SELECT increment " + increment + "\n");

			//INSERT RETURNING
			//CREATE TABLE aa (a int);
			increment = 0;
            PreparedStatement insertStmt = conn.prepareStatement("insert into aa select oid from pg_class returning a");
			insertStmt.setFetchSize(100);
            ResultSet insertRs = insertStmt.executeQuery();
			while (insertRs.next()) {
				increment++;
			}
            conn.commit();
			insertStmt.close();
			System.out.format("INSERT RETURNING increment " + increment + "\n");

			//INSERT RETURNING
			//CREATE TABLE bb (a int);
			increment = 0;
            PreparedStatement withStmt = conn.prepareStatement("with insert_stmt AS (insert into bb select oid from pg_class returning a) SELECT r1.a, r2.a from insert_stmt as r1, aa as r2;");
			withStmt.setFetchSize(100);
            ResultSet withRs = withStmt.executeQuery();
			while (withRs.next()) {
				increment++;
			}
            conn.commit();
			insertStmt.close();
			System.out.format("INSERT WITH increment " + increment + "\n");

			increment = 0;
            PreparedStatement withStmt2 = conn.prepareStatement("with select_stmt AS (select oid from pg_class) SELECT r1.oid, r2.a from select_stmt as r1, aa as r2;");
			withStmt2.setFetchSize(100000);
            ResultSet withRs2 = withStmt2.executeQuery();
			while (withRs2.next()) {
				increment++;
			}
            conn.commit();
			insertStmt.close();
			System.out.format("SELECT WITH increment " + increment + "\n");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ee) {
                ee.printStackTrace();
            }
        }
    }
}

signature.asc
Description: PGP signature