Patch Applied question about rollback and SQLException - Mailing list pgsql-patches
From | Dave Cramer |
---|---|
Subject | Patch Applied question about rollback and SQLException |
Date | |
Msg-id | 1044357456.1121.19.camel@inspiron.cramers Whole thread Raw |
In response to | Re: [JDBC] question about rollback and SQLException (Kris Jurka <books@ejurka.com>) |
List | pgsql-patches |
Kris, This has been applied to HEAD, and 7.2.3 Dave On Mon, 2003-01-27 at 13:29, Kris Jurka wrote: > The following patch fixes this problem. The QueryExecutor was detecting > this error halfway through the sendQuery process without resetting the > query state. I have moved this check to before any processing occurs. > > Kris Jurka > > > > > On Fri, 24 Jan 2003, Andy Kriger wrote: > > > Here you go - sample code. You'll need to set the url/usr/pass/query to make > > sense for your db (a dummy table with 3 int cols will do the trick). > > > > The key is the setInt methods. One is commented out, this will trigger the > > exceptions. You can comment any of them out and get the same exception (it > > doesn't just have to be the last one). > > > > No value specified for parameter 2 > > at > > org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:148) > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:70) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:505) > > at > > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j > > ava:320) > > at > > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j > > ava:48) > > at > > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State > > ment.java:197) > > at DriverBug.main(DriverBug.java:26) > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:482) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:461) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > n.java:1031) > > at DriverBug.main(DriverBug.java:31) > > > > ---TEST CODE--- > > > > import java.sql.*; > > public class DriverBug > > { > > public static void main(String[] args) > > { > > String url = ""; > > String user = ""; > > String pass = ""; > > String query = "insert into table (col1,col2,col3) values (?, ?, ?)"; > > > > try { > > Class.forName("org.postgresql.Driver"); > > } catch(Exception e) { > > e.printStackTrace(); > > System.exit(1); > > } > > > > Connection cnx = null; > > PreparedStatement stmt = null; > > ResultSet rs = null; > > try { > > cnx = DriverManager.getConnection(url,user,pass); > > cnx.setAutoCommit(false); > > System.out.println("connection obtained"); > > stmt = cnx.prepareStatement(query); > > stmt.setInt(1,3); > > //stmt.setInt(2,53); > > stmt.setInt(3,10); > > System.out.println("statement initialized"); > > stmt.executeUpdate(); > > System.out.println("update done"); > > } catch(SQLException se) { > > try { > > se.printStackTrace(); > > cnx.rollback(); > > } catch(Exception e) { > > e.printStackTrace(); > > } > > } finally { > > try { > > if(rs != null) rs.close(); > > if(stmt != null) stmt.close(); > > if(cnx != null) cnx.close(); > > } catch(Exception e) { > > e.printStackTrace(); > > } > > } > > System.out.println("FIN"); > > } > > } > > > > -----Original Message----- > > From: Dave Cramer [mailto:Dave@micro-automation.net] > > Sent: Friday, January 24, 2003 11:44 > > To: Barry Lind > > Cc: Andy Kriger; Pgsql-Jdbc; Kevin Tung > > Subject: Re: [JDBC] question about rollback and SQLException > > > > > > Barry, > > > > I haven't looked at the code, but it appears that the driver is not > > dealing well with a ill formed prepared statement. > > > > There are 3 variables, and 4 ? marks. > > > > Dave > > On Fri, 2003-01-24 at 00:45, Barry Lind wrote: > > > Andy, > > > > > > Now that you have a reproducable test case, can you send a code sample > > > that shows the problem. After reading this thread, I am still not sure > > > when the error message is being given. A code example would help a lot. > > > > > > Also, what version of the driver are you using? > > > > > > --Barry > > > > > > Andy Kriger wrote: > > > > [ moving this off the Resin mailing list as it is not about Resin ] > > > > > > > > I think I figured out what is going on. Below is the pgsql log (at level > > 2) > > > > You can see the problem in line 2: the query is being mangled. > > > > > > > > The original query was > > > > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?); > > > > > > > > I realize this query is bad since there aren't as many columns as > > values. It > > > > was only meant to trigger a SQLException so I could verify rollback. > > > > However, my PreparedStatment was setting only 3 of the 4 values. And > > doing > > > > that caused the mangling. If I set all 4 values, I get a SQLException > > > > (INSERT has more expressions than target columns) followed by a > > successful > > > > rollback. If I use the same number of values and columns but don't set > > the > > > > last value, I get a SQLException (No value specified for parameter 3) > > > > followed by an unsucessful rollback (the original problem). > > > > > > > > So, it looks like the JDBC driver is not handling the error condition > > where > > > > the last value is not set. And this is mangling the rollback query > > (though > > > > I'm guessing it would mangle the next query regardless of what it was). > > I > > > > was not able to isolate it to whether you will get the same problem if > > any > > > > value is not set (the 1st or 2nd, for example). > > > > > > > > Hopefully, one of the JDBC driver developers can take it from here. > > > > > > > > -a > > > > > > > > === PGSQL log === > > > > > > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: query: insert into purchase > > > > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin; > > > > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback" > > > > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction > > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: query: rollback; begin; > > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: query: end > > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end > > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > > > 2003-01-23 13:52:43 DEBUG: proc_exit(0) > > > > 2003-01-23 13:52:43 DEBUG: shmem_exit(0) > > > > 2003-01-23 13:52:43 DEBUG: exit(0) > > > > 2003-01-23 13:52:43 DEBUG: reaping dead processes > > > > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit > > code > > > > 0 > > > > > > > > -----Original Message----- > > > > From: pgsql-jdbc-owner@postgresql.org > > > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer > > > > Sent: Thursday, January 23, 2003 13:06 > > > > To: Andy Kriger > > > > Cc: Resin-Interest; Pgsql-Jdbc > > > > Subject: Re: [JDBC] question about rollback and SQLException > > > > > > > > > > > > Andy, > > > > > > > > The logs from the server would be good, I just tested rollback and it > > > > works, but if there was something some how left in the query buffer, > > > > this would be a problem > > > > > > > > DAve > > > > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote: > > > > > > > >>I don't really have something I can easily reduce out of my code. > > > >> > > > >>I started a transaction with Connection.setAutoCommit(false) > > > >>Ran some SQL queries using PreparedStatements > > > >>Ran a query that I knew would fail, throwing a SQLException > > > >>Then I rolledback the transaction (just to be sure) with > > > >>Connection.rollback() > > > >> > > > >>It was during Connection.rollback() that I received the exception that's > > > > > > > > in > > > > > > > >>my first email. The stack trace picks up from my code as it enters the > > > > > > > > Resin > > > > > > > >>& driver code. > > > >> > > > >>-----Original Message----- > > > >>From: Dave Cramer [mailto:Dave@micro-automation.net] > > > >>Sent: Thursday, January 23, 2003 12:32 > > > >>To: Andy Kriger > > > >>Cc: Resin-Interest; Pgsql-Jdbc > > > >>Subject: Re: [JDBC] question about rollback and SQLException > > > >> > > > >> > > > >>can you send us logs from the server? > > > >> > > > >>or a test case which demonstrates this? > > > >> > > > >>Dave > > > >> > > > >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > > > >> > > > >>>I received this exception when trying to rollback a transaction through > > > >> > > > >>the > > > >> > > > >>>Postgres JDBC driver (build106) used by a webapp running in Resin > > > > > > > > (2.0.6). > > > > > > > >>I > > > >> > > > >>>have tried setting up the connection pool in my web.xml to use both > > > >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this > > > >>>exception: > > > >>> > > > >>>java.sql.SQLException: ERROR: parser: parse error at or near > > > > > > > > "qrollback" > > > > > > > >>> at > > > >> > > > >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > > > >> > > > >>> at > > > >>> > > > >> > > > > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > > > > > > >>>.java:482) > > > >>> at > > > >>> > > > >> > > > > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > > > > > > >>>.java:461) > > > >>> at > > > >>> > > > >> > > > > > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > > > > > > >>>n.java:1031) > > > >>> at > > > >>> > > > >> > > > > > > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > > > > > > > >>>) > > > >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > > >>> > > > >>>Any idea what that means and how I can prevent it from occuring? > > > >>> > > > >>>thx > > > >>>andy kriger > > > >>> > > > >>> > > > >>> > > > >>>---------------------------(end of > > broadcast)--------------------------- > > > >>>TIP 6: Have you searched our list archives? > > > >>> > > > >>>http://archives.postgresql.org > > > >> > > > >>-- > > > >>Dave Cramer <Dave@micro-automation.net> > > > >> > > > >> > > > >> > > > >>---------------------------(end of broadcast)--------------------------- > > > >>TIP 3: if posting/reading through Usenet, please send an appropriate > > > >>subscribe-nomail command to majordomo@postgresql.org so that your > > > >>message can get through to the mailing list cleanly > > > > > > > > -- > > > > Dave Cramer <Dave@micro-automation.net> > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that your > > > message can get through to the mailing list cleanly > > -- > > Dave Cramer <Dave@micro-automation.net> > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ______________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
pgsql-patches by date: