BUG #5058: [jdbc] Silent failure with executeUpdate()

The following bug has been logged online:

Bug reference: 5058
Logged by: Joseph Shraibman
Email address: jks(at)selectacast(dot)net
PostgreSQL version: 8.2.14
Operating system: Linux
Description: [jdbc] Silent failure with executeUpdate()
Details:

I was trying to figure out why I was getting different results with sql I
was running through jdbc and running through psql. I was able to fix the
bug by changing an executeUpdate() to an execute(). See attached code.

I ran this code with the latest 8.2 and 8.4 jdbc drivers against an 8.2.14
database.

--------------------------------------------
public class PgBug {

transient protected Connection conn = null;
static int verbose = 3;

public PgBug() throws SQLException, ClassNotFoundException{
// Load the driver
Class.forName("org.postgresql.Driver");

conn = DriverManager.getConnection("jdbc:postgresql:playpen",
"postgres", "");;
System.out.println("jdbc version:
"+org.postgresql.Driver.getVersion());
}

private static StringBuilder toString(Object o, StringBuilder sb){
if (sb == null)
sb = new StringBuilder();
if (o == null)
sb.append("null");
else if (o.getClass().isArray()){
Class c = o.getClass().getComponentType();
if (c.isPrimitive()){
int len = java.lang.reflect.Array.getLength(o);
sb.append("[");
for(int i = 0; i < len ; i++){
sb.append(java.lang.reflect.Array.get(o, i));
if (i+1 < len)
sb.append(',');
}
sb.append("]");
}else{
Object[] arr = (Object[])o;
sb.append("{");
for(int i = 0; i < arr.length ; i++){
toString(arr[i],sb);
if (i+1 < arr.length)
sb.append(',');
}
sb.append("}");
}
}else
sb.append(o);
return sb;
}
static java.sql.Statement getStatement(java.sql.Connection conn)throws
java.sql.SQLException{
return
conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
}
static int getRsSize(java.sql.ResultSet rs)throws java.sql.SQLException{
//if ( ! rs.isBeforeFirst())
// throw new IllegalStateException("only call getRsSize on new
ResultSets");
if (!rs.last()) return 0;
int ans = rs.getRow();
rs.first(); rs.previous(); //reset pointer to before first row
if ( ! rs.isBeforeFirst())
throw new java.sql.SQLException(" getRsSize(): Could not reset
pointer");
return ans;
}

private static void printAnyWarnings(java.sql.Statement st,CharSequence
line){
try{
java.sql.SQLWarning warn = st.getWarnings();
if (warn != null)
System.err.println("SQLWarning: "+warn.getMessage()+" from
sql: "+line);
}catch(java.sql.SQLException e){}
}
public static Object[][] getResults(ResultSet rs)throws SQLException{
final int num_rows = getRsSize(rs);
if (num_rows == 0) return new Object[0][0];
final int cols = rs.getMetaData().getColumnCount();
if (false){
String m = "getRes(): cols is "+cols;
System.out.println(m);
}
Object answer[][] = new Object[num_rows][cols] ;

for (int i = 0 ; rs.next() ; i++ ) {
for (int j = 0 ; j < cols ; j++) {
answer[i][j] = rs.getObject(j+1);
if (verbose >= 3)
System.out.println("getResults() answer["+i+"]["+j+"] =
"+answer[i][j]);
if (answer[i][j] instanceof java.sql.Array)
answer[i][j] =
((java.sql.Array)answer[i][j]).getArray();
else if (answer[i][j] instanceof
org.postgresql.util.PGobject)
answer[i][j] = answer[i][j].toString();

}
}
return answer;
}

public int doUpdate(String line)throws SQLException{
if (line == null)
throw new SQLException (": line is null.");

Statement st = conn.createStatement();
int ans = st.executeUpdate(line);
printAnyWarnings(st,line);
st.close();
return ans;
}
public Object[][] doQuery(String pre_line, String line, String
post_line, boolean eu)throws SQLException{
if (line == null)
throw new SQLException ("doQuery: line is null.");

boolean get_field_names = false;
Statement st = null;
if (pre_line != null || post_line != null){
st = getStatement(conn);
if (pre_line != null){
if (eu)
st.executeUpdate(pre_line);
else
st.execute(pre_line);
printAnyWarnings(st,pre_line);
}
}
ResultSet rs = st.executeQuery(line);
Object[][] ans = getResults(rs);
if (verbose >= 1)
System.out.println("doQuery(): ans is: "+toString(ans, null));
String[] fa = null;
if (get_field_names){
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
fa = new String[cols];
for(int i = 1; i <= cols ; i++)
fa[i-1] = meta.getColumnLabel(i);
}
if (post_line != null){
st.executeUpdate(post_line);
printAnyWarnings(st,post_line);
}
if (st != null)
st.close();
if (get_field_names){

}
return ans;
}
public Object[][] doQuery(String[] pre_line, String line, String
post_line)throws SQLException{
if (line == null)
throw new SQLException ("doQuery: line is null.");

boolean get_field_names = false;
Statement st = null;
if (pre_line != null || post_line != null){
st = getStatement(conn);
if (pre_line != null){
for(int i = 0; i < pre_line.length ; i++){
st.execute(pre_line[i]);
printAnyWarnings(st,pre_line[i]);
}
}
}
ResultSet rs = st.executeQuery(line);
Object[][] ans = getResults(rs);
if (verbose >= 1)
System.out.println("doQuery(): ans is: "+toString(ans, null));
String[] fa = null;
if (get_field_names){
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
fa = new String[cols];
for(int i = 1; i <= cols ; i++)
fa[i-1] = meta.getColumnLabel(i);
}
if (post_line != null){
st.executeUpdate(post_line);
printAnyWarnings(st,post_line);
}
if (st != null)
st.close();
if (get_field_names){

}
return ans;
}
private String getSeq(int num){
StringBuilder sb = new StringBuilder();
for(int i = 0; i < num ; i++){
sb.append(i);
if (i+1 < num)
sb.append(',');
}
return sb.toString();
}
void setup()throws SQLException{
doUpdate("CREATE TABLE joa (jobid int, uids int[])");
for(int i = 1; i < 15 ; i++){
doUpdate("INSERT INTO joa VALUES("+i+",
ARRAY["+getSeq(i)+"])");
}
String q = "CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr
int[], jobid int) returns void AS $$\n"+
"BEGIN\n"+
" FOR i IN array_lower(thearr,1)..array_upper(thearr,1)
LOOP\n"+
"execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid ||
',' || thearr[i] || ');';\n"+
"END LOOP;\n"+
"END;\n"+
"$$ language 'plpgsql' VOLATILE ;";
doUpdate(q);
}
void doTest1(boolean eu)throws SQLException{
String pre = " BEGIN; CREATE TEMP TABLE japlc (jobid int, uid int);
SELECT insert_to_japlcu_table(uids, jobid) FROM joa; ANALYZE japlc;";

Object[][] oaa = doQuery(pre, "SELECT count(*) FROM japlc;",
"ABORT;", eu);
System.out.print(toString(oaa, null));

}
void doTest2()throws SQLException{
String[] pre = {" BEGIN;","CREATE TEMP TABLE japlc (jobid int, uid
int);",
" SELECT insert_to_japlcu_table(uids, jobid) FROM
joa;","ANALYZE japlc;"};
Object[][] oaa = doQuery(pre, "SELECT count(*) FROM japlc;",
"ABORT;");
System.out.print(toString(oaa, null));

}
void teardown()throws SQLException{
doUpdate("DROP TABLE joa;");
}

public static final void main(final String[] args)throws SQLException,
ClassNotFoundException {
PgBug bug = new PgBug();

try{
System.out.print("tearing down (in case of previous bad
exit)...");
bug.teardown();
System.out.println("done.");
}catch(SQLException e){
System.out.println("nothing to do.");
}

System.out.print("setting up...");
bug.setup();
System.out.println("done.");

System.out.print("running test 1a...");
bug.doTest1(true);
System.out.println("done.");

System.out.print("running test 1b...");
bug.doTest1(false);
System.out.println("done.");

System.out.print("running test 2...");
bug.doTest2();
System.out.println("done.");

System.out.print("tearing down...");
bug.teardown();
System.out.println("done.");
}

}