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