Re: connection/statement becomes unstable following SQLException - Mailing list pgsql-jdbc

I guess the most obvious question is do you call Connection.rollback() after
the failed statement?  It would be helpful to see the transaction
context code.

Rob Prowel wrote:

>consider the following:
>
>table lookup_industry(industry varchar(20) not null
>unique);
>
>and the following code:
>
>------------------------------------------------------------
>public static void main(String [] arg) {
>
>        lookupIndustry conn=new lookupIndustry();
>        try {
>                conn=new lookupIndustry((new
>jobDBconnection()).conn); }
>        catch(Exception e) {
>                System.exit(1); }
>                // connect to the db
>
>        System.out.println("---------------------");
>        for (int i=0; i<arg.length; i++) {
>                try {
>                        System.out.println("adding
>industry("+arg[i]+")");
>                        conn.addIndustryList(arg[i]);
>}
>                        // attempt to add a new record
>                catch (Exception e) {
>
>System.out.println(arg[i]+":"+Integer.toString(i)+
>                                " "+e.toString()); }
>                finally {
>System.out.println("try>"+Integer.toString(i)); }
>
>System.out.println("loop>"+Integer.toString(i));
>                }
>                // should add new records and print
>alerts for each record that
>                // already exists
>
>        System.out.println("---------------------");
>        System.exit(0);
>        }
>
>--------------------------------------------------------------------
>
>And the following output:
>
>$ jdbcrun the geese fly high
>---------------------
>adding industry(the)
>the:0 java.sql.SQLException: ERROR:  Cannot insert a
>duplicate key into unique index
>lookup_industry_industry_key
>
>try>0
>loop>0
>adding industry(geese)
>insert rv=1
>try>1
>loop>1
>adding industry(fly)
>fly:2 java.sql.SQLException: ERROR:  Cannot insert a
>duplicate key into unique index
>lookup_industry_industry_key
>
>try>2
>loop>2
>adding industry(high)
>insert rv=1
>try>3
>loop>3
>---------------------
>
>----------------------------------------------------
>
>notice that the only two iterations that generate an
>exception are the first and thrid ones...even though
>(the,geese,fly,high) are already in the database and
>the values are constrained to be unique in the table
>definition.
>
>It seems that there is a bug in the Connection or
>Statement logic in the JDBC driver that isn't
>resetting things immediately after an exception
>occurs.
>
>I have one Connection open and addIndustryList()
>creates a new Statement object for every insert that
>it does to the table.  If the entry already exists in
>the table then addIndustryList() throws an
>SQLException object that I SHOULD be able to catch and
>ignore, just moving on to add the next record.  Only
>other thing I should mention is that I turn autocommit
>off and commit each transaction as it is entered.
>
>I believe I've uncovered a bug and would like someone
>who owns or is very familiar with the code to reply.
>I can then send them more information such as my
>complete classes and the psql script to create the
>table in question.
>
>Finally, here's my platform information:
>
>i586 Linux 2.4.21
>PostgreSQL 7.2.1
>pgjdbc3.jar
>J2SE 1.4.2
>
>Thanks.
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
>http://calendar.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>
>