DeferrableScan.java

package com.yugabyte.sample.apps;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class DeferrableScan {

    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        Class.forName("org.postgresql.Driver");
        try {
            // RF=1 cluster; 2.3.3.0 - problem reproduces
            // Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5433/yugabyte", "yugabyte", "yugabyte");

            // RF=1 cluster; 2.5.0.0 build 2 revision ff3d49f69e7a42ab28d3a529ac49b8aa7bbd7ad6 (problem reproduces)
            Connection conn = DriverManager.getConnection("jdbc:postgresql://172.151.28.148:5433/yugabyte", "yugabyte", "yugabyte");

            // RF=3 cluster; 2.5.0.0 build 2 revision ff3d49f69e7a42ab28d3a529ac49b8aa7bbd7ad6
            // Problem doesn't reproduce.
            // Connection conn = DriverManager.getConnection("jdbc:postgresql://172.151.24.196:5433/yugabyte", "yugabyte", "yugabyte");

            Statement stmt = conn.createStatement();

            System.out.println("Connected to the PostgreSQL server successfully.");

            String dropTableQuery = "DROP TABLE IF EXISTS employee";
            stmt.executeUpdate(dropTableQuery);
            System.out.println("Dropped table employee");

            String createTableQuery = "CREATE TABLE IF NOT EXISTS employee" +
                                        "(id int primary key, name varchar, age int, language text)";
            stmt.executeUpdate(createTableQuery);
            System.out.println("Created table employee");

            PreparedStatement st = conn.prepareStatement("INSERT INTO employee " +
                                                         "(id, name, age, language) VALUES (?, ?, ?, ?)");
            for(int idx=0; idx < 1000; idx++) {
                st.setInt(1, idx);
                st.setString(2, "Name-" + String.valueOf(idx));
                st.setInt(3, 20 + (idx % 50));
                st.setString(4, "Language-" + String.valueOf(idx));
                st.executeUpdate();
            }
            System.out.println("Inserted 1000 rows");

            // Additionally, for long running scans, with concurrent writes, set READ ONLY, DEFERRABLE to
            // avoid read-restarts.
            stmt.executeUpdate("BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE");
            Statement selectStmt = conn.createStatement();
            ResultSet rs = selectStmt.executeQuery("select * from employee");
            int rows = 0;
            while (rs.next()) {
                rows++;
                // Print every 100 rows.
                if ((rows %  100) == 0) {
                    System.out.println("Query returned: "+
                                       "name=" + rs.getString(2) +
                                       ", age=" + rs.getString(3) +
                                       ", language=" + rs.getString(4));
                }
            }
            rs.close();
            conn.close();
            System.out.println("Rows = " + rows);
            System.out.println("Closing cursor");

        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}