Patch Applied question about rollback and SQLException

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(at)micro-automation(dot)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(at)postgresql(dot)org
> > > > [mailto:pgsql-jdbc-owner(at)postgresql(dot)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(at)micro-automation(dot)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(at)micro-automation(dot)net>
> > > >>
> > > >>
> > > >>
> > > >>---------------------------(end of broadcast)---------------------------
> > > >>TIP 3: if posting/reading through Usenet, please send an appropriate
> > > >>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > >>message can get through to the mailing list cleanly
> > > >
> > > > --
> > > > Dave Cramer <Dave(at)micro-automation(dot)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(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
> > --
> > Dave Cramer <Dave(at)micro-automation(dot)net>
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
>
> ______________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Dave Cramer <Dave(at)micro-automation(dot)net>