Обсуждение: connection/statement becomes unstable following SQLException
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
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
>
>
>
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
>
--- Barry Lind <blind@xythos.com> wrote: > 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. > Thank you. This problem was in fact caused by not doing a rollback to recover from an "Excepted" transaction. Silly me. I figured that if the transaction was atomic, then the rollback would be implicit when if the transaction failed But after considering that a single insert into an indexed table cannot be "atomic", I now see the error of my ways. Thanks to all who responded. -Rob __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com