Thread
-
BUG #5058: [jdbc] Silent failure with executeUpdate()
Joseph Shraibman <jks@selectacast.net> — 2009-09-15T19:26:51Z
The following bug has been logged online: Bug reference: 5058 Logged by: Joseph Shraibman Email address: jks@selectacast.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."); } } -
Re: BUG #5058: [jdbc] Silent failure with executeUpdate()
Robert Haas <robertmhaas@gmail.com> — 2009-09-16T00:54:41Z
On Tue, Sep 15, 2009 at 3:26 PM, Joseph Shraibman <jks@selectacast.net> wrote: > > The following bug has been logged online: > > Bug reference: 5058 > Logged by: Joseph Shraibman > Email address: jks@selectacast.net > PostgreSQL version: 8.2.14 > Operating system: Linux > Description: [jdbc] Silent failure with executeUpdate() > Details: > Based on the lack of response to previous JDBC/ODBC bugs on this list, I'm guessing you should try posting this to pgsql-jdbc rather than pgsql-bugs. ...Robert
-
Re: BUG #5058: [jdbc] Silent failure with executeUpdate()
Craig Ringer <craig@postnewspapers.com.au> — 2009-09-16T02:10:35Z
On Tue, 2009-09-15 at 19:26 +0000, Joseph Shraibman wrote: > The following bug has been logged online: > > Bug reference: 5058 > Logged by: Joseph Shraibman > Email address: jks@selectacast.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. > While the test case is appreciated, it's not really that clear what's expected to happen vs what happens, at what point things go wrong and under what circumstances, etc. Looking at your code and your post, I can't tell: - If the code you've attached is before or after the change described - Where the change is/was made - Why - What it actually does There are also a couple of randomly commented-out bits of code that seem odd, some code seems like unnecessary wrapper code in such a simple example ( eg getStatement(...) ) and some of the code is downright hairy. In particular: private static StringBuilder toString(Object o, StringBuilder sb) cries out "use parameterized queries!". You're making life unnecessarily hard for yourself, increasing SQL injection risk dramatically, and giving yourself more to debug than you need by building queries as strings including all parameters. Perhaps you could pare down the code to that required to demonstrate the issue and document the issue a little? Cut out unnecessary or duplicate code (why are there two doQuery() implementations with mostly the same code), drop any result-printing code etc if it doesn't help demonstrate the bug you're reporting, remove commented out code and empty blocks, and generally tidy things up so that the reader can read it to follow your problem report, not puzzle out why your code does things how it does. Above all else, make sure to provide an explanation of where the problem you are reporting arises, what actually happens, what you expect to happen instead, and what exactly you change to "fix" it. It might help to turn detailed error logging on in the server and capture the server error log for your "silent" query failure, too. -- Craig Ringer
-
Re: BUG #5058: [jdbc] Silent failure with executeUpdate()
Joseph Shraibman <jks@selectacast.net> — 2009-09-17T18:27:20Z
Craig Ringer wrote: > On Tue, 2009-09-15 at 19:26 +0000, Joseph Shraibman wrote: >> The following bug has been logged online: > While the test case is appreciated, it's not really that clear what's > expected to happen vs what happens, at what point things go wrong and > under what circumstances, etc. Looking at your code and your post, I > can't tell: > > - If the code you've attached is before or after the change described > - Where the change is/was made > - Why > - What it actually does Look at the main method. I run 3 queries that should return the same thing, but 1 time out of 3 it doesn't. > > There are also a couple of randomly commented-out bits of code that seem > odd, some code seems like unnecessary wrapper code in such a simple > example ( eg getStatement(...) ) and some of the code is downright > hairy. I started writing this example program based on my actual codebase in an attempt to distill down to code that demonstrates the bug. That's why there was some extra stuff left in there. > In particular: > > private static StringBuilder toString(Object o, StringBuilder sb) > > cries out "use parameterized queries!". You're making life unnecessarily > hard for yourself, increasing SQL injection risk dramatically, and > giving yourself more to debug than you need by building queries as > strings including all parameters. This is supposed to be a simple example that demonstrates the bug, not production code. The toString() method above is used for printing out the results, not constructing sql. > > > Perhaps you could pare down the code to that required to demonstrate the > issue and document the issue a little? Cut out unnecessary or duplicate > code (why are there two doQuery() implementations with mostly the same > code), To demonstrate the bug. One works, one has a problem. drop any result-printing code etc if it doesn't help demonstrate > the bug you're reporting, remove commented out code and empty blocks, > and generally tidy things up so that the reader can read it to follow > your problem report, not puzzle out why your code does things how it > does. > Here is a simplified version. I've gotten rid of some of the dead code and a doQuery() method and added some comments. PgBug.java : -------------------------------------------------------------------- import java.sql.*; public class PgBug { private Connection conn = null; private static int verbose = 0; 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()); } /** Converts this Object to a String. If o is an array recursively calls for all elements of the array */ 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.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){} } /** Convert a ResultSet into Object[][] */ 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(); 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{ Statement st = conn.createStatement(); int ans = st.executeUpdate(line); printAnyWarnings(st,line); st.close(); return ans; } /** @param eu If true use executeUpdate() for the pre_line, if false use st.execute() */ 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."); Statement st = getStatement(conn); if (pre_line != null || post_line != null){ if (pre_line != null){ //Here is where the bug is. Originally I was calling st.executeUpdate(pre_line). No Exceptions //happened and I assumed everything was dandy until I realized that my results were wrong. //The problem is that my temp table is not set up properly when I do this. 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)); if (post_line != null){ st.executeUpdate(post_line); printAnyWarnings(st,post_line); } st.close(); return ans; } //Used by setup() below 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{ //Create a temp table, and use insert_to_japlcu_table() to populate it with data from the joa table String pre = " BEGIN; CREATE TEMP TABLE japlc (jobid int, uid int); SELECT insert_to_japlcu_table(uids, jobid) FROM joa; ANALYZE japlc;"; //You can replace this sql with SELECT * to see the contents of the temp table Object[][] oaa = doQuery(pre, "SELECT count(*) FROM japlc;", "ABORT;", eu); System.out.print(toString(oaa, null)); } void teardown()throws SQLException{ doUpdate("DROP TABLE joa;"); } void setServerLogLevel(String s)throws SQLException{ doUpdate("set client_min_messages = "+s); doUpdate("set log_min_messages = "+s); } 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."); } for(int i = 0; i < args.length ; i++){ if(args[i].equals("--serverloglevel")) bug.setServerLogLevel(args[++i]); else if(args[i].equals("--verbose")) verbose = Integer.parseInt(args[++i]); } System.out.print("setting up..."); bug.setup(); System.out.println("done."); System.out.print("running test with executeUpdate(pre)..."); bug.doTest1(true); System.out.println("done."); System.out.print("running test with execute(pre)..."); bug.doTest1(false); System.out.println("done."); System.out.print("tearing down..."); bug.teardown(); System.out.println("done."); bug.conn.close(); } } --------------------------------------------------------------------------------------- The server log when run with --serverloglevel debug5. You can see in the ANALYZE output the different sizes of the temp table. Other than that I don't see anything interesting. LOG: execute <unnamed>: DROP TABLE joa ERROR: table "joa" does not exist STATEMENT: DROP TABLE joa LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[]) LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0]) LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1]) LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2]) LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3]) LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4]) LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5]) LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6]) LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7]) LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8]) LOG: execute <unnamed>: INSERT INTO joa VALUES(10, ARRAY[0,1,2,3,4,5,6,7,8,9]) LOG: execute <unnamed>: INSERT INTO joa VALUES(11, ARRAY[0,1,2,3,4,5,6,7,8,9,10]) LOG: execute <unnamed>: INSERT INTO joa VALUES(12, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11]) LOG: execute <unnamed>: INSERT INTO joa VALUES(13, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12]) LOG: execute <unnamed>: INSERT INTO joa VALUES(14, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13]) LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$ BEGIN FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' || thearr[i] || ');'; END LOOP; END; $$ language 'plpgsql' VOLATILE LOG: execute <unnamed>: BEGIN LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa LOG: execute <unnamed>: ANALYZE japlc LOG: execute <unnamed>: SELECT count(*) FROM japlc LOG: execute <unnamed>: ABORT LOG: execute <unnamed>: BEGIN LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa LOG: execute <unnamed>: ANALYZE japlc LOG: execute <unnamed>: SELECT count(*) FROM japlc LOG: execute <unnamed>: ABORT LOG: execute <unnamed>: DROP TABLE joa LOG: execute <unnamed>: select logtime from eventlog order by id desc limit 1 LOG: execute <unnamed>: DROP TABLE joa ERROR: table "joa" does not exist STATEMENT: DROP TABLE joa LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[]) LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0]) LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1]) LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2]) LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3]) LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4]) LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5]) LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6]) LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7]) LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8]) LOG: execute <unnamed>: INSERT INTO joa VALUES(10, ARRAY[0,1,2,3,4,5,6,7,8,9]) LOG: execute <unnamed>: INSERT INTO joa VALUES(11, ARRAY[0,1,2,3,4,5,6,7,8,9,10]) LOG: execute <unnamed>: INSERT INTO joa VALUES(12, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11]) LOG: execute <unnamed>: INSERT INTO joa VALUES(13, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12]) LOG: execute <unnamed>: INSERT INTO joa VALUES(14, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13]) LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$ BEGIN FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' || thearr[i] || ');'; END LOOP; END; $$ language 'plpgsql' VOLATILE LOG: execute <unnamed>: BEGIN LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa LOG: execute <unnamed>: ANALYZE japlc LOG: execute <unnamed>: SELECT count(*) FROM japlc LOG: execute <unnamed>: ABORT LOG: execute <unnamed>: BEGIN LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa LOG: execute <unnamed>: ANALYZE japlc LOG: execute <unnamed>: SELECT count(*) FROM japlc LOG: execute <unnamed>: ABORT LOG: execute <unnamed>: DROP TABLE joa LOG: execute <unnamed>: DROP TABLE joa ERROR: table "joa" does not exist STATEMENT: DROP TABLE joa LOG: execute <unnamed>: set client_min_messages = debug5 LOG: execute <unnamed>: set log_min_messages = debug5 DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125327/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: CREATE TABLE joa (jobid int, uids int[]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125328/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[]) DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125328/1/8, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125329/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125329/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125330/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125330/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125331/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125331/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125332/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125332/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125333/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125333/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125334/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125334/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125335/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125335/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125336/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125336/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125337/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125337/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(10, ARRAY[0,1,2,3,4,5,6,7,8,9]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125338/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(10, ARRAY[0,1,2,3,4,5,6,7,8,9]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125338/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(11, ARRAY[0,1,2,3,4,5,6,7,8,9,10]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125339/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(11, ARRAY[0,1,2,3,4,5,6,7,8,9,10]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125339/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(12, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125340/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(12, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125340/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(13, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125341/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(13, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125341/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: INSERT INTO joa VALUES(14, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13]) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125342/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: INSERT INTO joa VALUES(14, ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13]) DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125342/1/2, nestlvl: 1, children: <> DEBUG: parse <unnamed>: CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$ BEGIN FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' || thearr[i] || ');'; END LOOP; END; $$ language 'plpgsql' VOLATILE DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125343/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$ BEGIN FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' || thearr[i] || ');'; END LOOP; END; $$ language 'plpgsql' VOLATILE DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125343/1/3, nestlvl: 1, children: <> DEBUG: parse <unnamed>: BEGIN DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125344/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: BEGIN DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) DEBUG: ProcessUtility DEBUG: parse <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa DEBUG: parse <unnamed>: ANALYZE japlc DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: ANALYZE japlc DEBUG: ProcessUtility DEBUG: analyzing "pg_temp_12.japlc" DEBUG: "japlc": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: SELECT count(*) FROM japlc DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: SELECT count(*) FROM japlc DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: ABORT DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: ABORT DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: BEGIN DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125345/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: BEGIN DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int) DEBUG: ProcessUtility DEBUG: parse <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid) FROM joa DEBUG: parse <unnamed>: ANALYZE japlc DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: ANALYZE japlc DEBUG: ProcessUtility DEBUG: analyzing "pg_temp_12.japlc" DEBUG: "japlc": scanned 1 of 1 pages, containing 105 live rows and 0 dead rows; 105 rows in sample, 105 estimated total rows DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: SELECT count(*) FROM japlc DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: SELECT count(*) FROM japlc DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: ABORT DEBUG: StartTransactionCommand DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: ABORT DEBUG: ProcessUtility DEBUG: CommitTransactionCommand DEBUG: parse <unnamed>: DROP TABLE joa DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 26125346/1/0, nestlvl: 1, children: <> DEBUG: bind <unnamed> to <unnamed> LOG: execute <unnamed>: DROP TABLE joa DEBUG: ProcessUtility DEBUG: drop auto-cascades to toast table pg_toast.pg_toast_1214500 DEBUG: drop auto-cascades to type pg_toast.pg_toast_1214500 DEBUG: drop auto-cascades to index pg_toast.pg_toast_1214500_index DEBUG: drop auto-cascades to type joa DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 26125346/1/12, nestlvl: 1, children: <> DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) -
Re: [JDBC] BUG #5058: [jdbc] Silent failure with executeUpdate()
Kris Jurka <books@ejurka.com> — 2009-09-26T14:32:19Z
On Thu, 17 Sep 2009, Joseph Shraibman wrote: > [when passing a multi-statement sql string to executeUpdate, if a > statement after the first is a select, it is silently not fully > executed.] Running queries in executeUpdate is not allowed. If you pass a plain select to executeUpdate it complains: stmt.executeUpdate("SELECT 1"); org.postgresql.util.PSQLException: A result was returned when none was expected. The problem here is that we don't complain if it's not the first part of a multi-part statement. stmt.executeUpdate("/* */; SELECT 1") does not produce an error, but should, which I believe is the bug in this case. Since the JDBC driver knows it's going to complain if it sees a query result during executeUpdate, it wants to avoid the possibility of the user issuing a query which returns a giant dataset and having to process that just to error out anyway. So it passes the statements to the server with an additional instruction to say, "I just want the first row back at this time". That way if it is a big query result we'll only get one row instead of the whole thing. The server can implement this by either holding the whole resultset on the server or, as in this case, partially executing the query and returning control to the driver to see if it wants to continue executing it. So your select is only partially executed, getting run for only one row of the "joa" table rather than the whole thing as you are expecting. I intend to change the driver to error out in this case. Kris Jurka