Skip to content

Instantly share code, notes, and snippets.

@kmuthukk
Last active November 24, 2020 22:23
Show Gist options
  • Save kmuthukk/76d4bab47eb27d96ea6b835f2f226cf4 to your computer and use it in GitHub Desktop.
Save kmuthukk/76d4bab47eb27d96ea6b835f2f226cf4 to your computer and use it in GitHub Desktop.
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());
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment