DeferredEndTest.java

import java.sql.*;

public class DeferredEndTest {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        String user = "postgres";
        String pass = "";

        try (Connection conn = DriverManager.getConnection(url, user, pass)) {
            conn.setAutoCommit(false);

            // Reset stats
            try (Statement st = conn.createStatement()) {
                st.execute("SELECT pg_stat_statements_reset()");
            }
            conn.commit();
            conn.setAutoCommit(false);

            // Portal A
            PreparedStatement stmtA = conn.prepareStatement(
                "SELECT id, pg_sleep(0.1), data FROM big_table ORDER BY id");
            stmtA.setFetchSize(5);

            // Portal B
            PreparedStatement stmtB = conn.prepareStatement(
                "SELECT id, pg_sleep(0.2), data FROM big_table ORDER BY id DESC");
            stmtB.setFetchSize(5);

            System.out.println("=== Portal A ===");
            System.out.println("Time: " + new Timestamp(System.currentTimeMillis()));
            ResultSet rsA = stmtA.executeQuery();

            // Fetch a few rows from A
            for (int i = 0; i < 3 && rsA.next(); i++) {
                System.out.println("  A row: id=" + rsA.getInt(1));
            }

            System.out.println("\n--- sleeping 3 seconds ---\n");
            Thread.sleep(3000);

            System.out.println("=== Portal B ===");
            System.out.println("Time: " + new Timestamp(System.currentTimeMillis()));
            ResultSet rsB = stmtB.executeQuery();

            for (int i = 0; i < 3 && rsB.next(); i++) {
                System.out.println("  B row: id=" + rsB.getInt(1));
            }

            // Close A first
            System.out.println("\n=== Closing Portal A (ExecutorEnd A) ===");
            System.out.println("Time: " + new Timestamp(System.currentTimeMillis()));
            rsA.close();
            stmtA.close();

            // Then close B
            System.out.println("\n=== Closing Portal B (ExecutorEnd B) ===");
            System.out.println("Time: " + new Timestamp(System.currentTimeMillis()));
            rsB.close();
            stmtB.close();

            conn.commit();

            conn.setAutoCommit(true);
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(
                     "SELECT last_execution_start, total_exec_time, " +
                     "substr(query, 1, 80) AS query " +
                     "FROM pg_stat_statements " +
                     "WHERE query LIKE '%big_table%' " +
                     "ORDER BY last_execution_start")) {
                System.out.println("\n=== pg_stat_statements results ===");
                while (rs.next()) {
                    System.out.printf("last_execution_start=%s  exec_time=%.2fms  query=%s%n",
                        rs.getTimestamp(1), rs.getDouble(2), rs.getString(3));
                }
            }
        }
    }
}