Re: Weird behavior in transaction handling (Possible bug ?) -- commit fails silently

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.

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 ?

> 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 succeed
since 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).

Best regards,

--j

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