Re: transaction error handling

Hi,
Oracle does not save the data, in mid transaction, in the sense of a commit.
It keeps the association of the memory address related to the error free changes to the transaction and allows you – the developer to capture the error on that single incorrect change, and then continue with the subsequent sql statements that are part of that long transaction.
While in that state, the changes pertaining to that transaction are not written to any logs and are not written to file, you can still roll back the entire transaction.
Only when a commit occurs, does the transaction get written to SGA, archiving, file etc…

With Postgres that is not the case, if the 50th sql statement in a long transaction incurs an error, the whole transaction is rolled back for you automatically, you the developer have no say in that unless you bracket each statement with a savepoint creation and destruction, just to be able to capture the potential error that may arise on that 50th sql statement.

Sincerely,
Kasia
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Bèrto ëd Sèra
Sent: Wednesday, November 30, 2011 12:49 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] transaction error handling

On 29 November 2011 21:34, Rob Richardson <RDRichardson(at)rad-con(dot)com<mailto:RDRichardson(at)rad-con(dot)com>> wrote:
If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction?

It sure enough kills Atomicity. I can see a use for this on importing data from external sources that may violate existing unique keys, so illegal inserts are ignored, but you still are left without any knowledge of what rows where silently dropped. Since when is Oracle doing this, FMI? (It's been a long while since I used it for anything serious)

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.