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

Rob,

There are a couple of things going on here.  First is the way postgres
works.  One postgres gets an error, all subsequent sql statements issued
  in that transaction will also error.  So to get things back to a
normal state you need to rollback the transaction after any error
occurs.  So in general in postgres you can't trap an error and continue
processing without first rolling back the transaction that errored.

Also what version of the driver and database are you using?  Have you
tried the latest version from the jdbc.postgresql.org website?

Finally, if you turned sql statement tracing on on the server you would
be able to see exactly what was going on by looking at the server log files.

This may be a jdbc driver bug, but it also could be a number of other
things as well.

thanks,
--Barry



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
>