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