Re: question about rollback and SQLException

[ 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