Re: Weird behavior in transaction handling (Possible bug ?)

j.random.programmer wrote:

Dave:
 

Actually, reviewing your original post. Yes the   

commit fails  

silently. However the insert does not fail silently   

and should throw  

an error! Do you check for errors here ?   

I was simple catching the exception but not rolling
back since
I presumed the rest of the transaction would succeed
(and
commit() didn't complain). It's only after playing
around that I
realized that the transaction was failing because of
the earlier
error.
 

So correct me if I'm wrong.
even in psql you are getting no error message from
the commit statement?   

Here's a psql session
-----------------------------------------------
g=# create table foo (id int primary key, words text);
g=# begin;
g=# insert into foo values (1, 'hello');
g=# insert into foo values (1, 'hello');
ERROR:  duplicate key violates unique constraint
"foo_pkey"
g=# end;       
COMMIT
g=# select * from foo;
+----+-------+
| id | words |
+----+-------+
+----+-------+
(0 rows)
------------------------------------------------

Note, the first insert failed silently too. 

The first insert does not fail, it is the duplicate key of the second insert that is the error.

So yeah, this looks like a postgres database specific
thing. But postgres is *better* than that -- the above
behavior is expected from myql BUT NOT postgres, right
?

So maybe, the database folks can do something about
this in version 8.0. Maybe you can also forward this
message
to the core postgres folks ? 

They read this and will probably not respond because they do not consider this an error.

The driver can't possibly know when something is
going to fail.   

It can, since it gets an error back from the database
and "knows" the internal postgresql behavior. 

Specifically and in the MEANTIME,  why can't you do
the 
follwing in the JDBC driver ?

-----------  JDBC driver code ------------------

boolean sawExceptionInConnection = false;
String  errorMessageInConnection;
....
if an error is thrown back from the database
then sawExceptionInConnection = true;/*the error message that was actually recieved saved
here*/errorMessageInConnection =   "ERROR:  duplicate key violates unique constraint";
....
in the commit() method implementation

if (sawExceptionInConnection)
throw new SQLException("postgres will not allow this commit() to succeedsince an error was recieved from the database.The error = " + errorMessageInConnection);
----------------------------------------------			
Is there any technical reason why the above
cannot/should not
be implemented ? It would be the RIGHT thing to do
since
it would get rid of SILENT failure (which is
absolutely, utterly
wrong in any database).  

This is not a silent failure. The insert errored out and gave you a message. commit is basically just end transaction here.
The user is supposed to handle the errors, not the driver.

Dave

Best regards,

--j


	
__________________________________ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

 

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561