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

j.random.programmer wrote:

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

It does fail since the commit() failed silently. There
is nothing in the database at all. 

What I meant to say it is not the cause of the error, only the second is the cause of the error
now as I said before because one of the statements in the transaction had an error none of the statements in the transaction will commit
a transaction is a unit of work, and it is atomic

http://www.postgresql.org/docs/7.4/interactive/tutorial-transactions.html

as the docs referred to above suggest.

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

But it's contrary to what oracle, db2, sybase (I
think) and 
mysql/innodb do.

It's not documented in the postgresql documentation
either
as far as I can tell.
 

This is not a silent failure. The insert errored out
and gave you a 
message. commit is basically just end transaction
here.   

But why should a insert error mean that I have to
abort
the entire transaction ? And if so, then the driver
can
warn about that, no ? (especially since the same JDBC
code
runs fine on oracle, db2, mysql/innodb and sybase).
It's
a  postgres specific non-documented thing, so
shouldn't
the JDBC driver warn us as to what's going on ?

Or are you saying that I need to special case JDBC
code
of postgres ?
 

Because transactions in postgresql are ATOMIC. It's all or nothing.

How do you handle the case of moving money from one bank account to another ( for example) and the second update fails.

ie
update bank1 set balance=balance-20;
update bank2 set balance=balance+20;

Does the money just dissappear ?  What if the program can't "fix" the problem. ie the disk is full.

I understand you have an investment in your code, but this is the way postgres works, no amount of emails are going to change that fact.

The user is supposed to handle the errors, not the
driver.   

But the error is postgresql specific. There is no
error, only
a insert within a transaction failed. Why should that
affect
the rest of the transaction, if *I* as the user don't
want it to?
 

How does postgres know which parts you don't care about ?

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