[SQL] transaction processing after error in statement
Dear PostgreSQL Gurus, I have been using PostgreSQL for quite a while and always relied on its handling of transaction and concurrency. But recently I discovered a behaviour that deviates quite a lot from how I understand transactions and how things are handled by other databases: HP Allbase, Oracle 8 and .mdb-files (Access). Here's the description: Whenever an error occurs within the transaction, PostgreSQL puts the whole transaction in an *ABORT* state, so that there is no difference at all between COMMITing or ROLLBACKing it. Even commands successfully carried out before the error ocurred are rolled back, even if I COMMIT the transaction, where no error message whatsoever is shown. Example: begin; insert into table1 values (1, 'hello'); --> success! select no from table1; ERROR: Attribute 'no' not found commit; --> success! Why should the insert statement fail, just because there was a typo in the following select statement? I was already carried out successfully, albeit only visible to the current transaction. I found this behaviour to be the same across all 7.x versions of PostgreSQL. Unfortunately, I haven't been able to find an explanation why PostgreSQL behaves like this and why all other RDBMS I tried behave differently. In this case the others make more sense to me. Additionally, I have discovered that phantom reads occur in PostgreSQL even if isolation mode serializable is used. Also not so nice! Sincerely, Holger -- Holger Jakobs * D-51469 Bergisch Gladbach Telefon +49-2202-59991 * Mobilfon +49-177-792-2466 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] transaction processing after error in statement
Hi Stephan, On 9 Nov, Stephan Szabo wrote: > On Fri, 7 Nov 2003 [EMAIL PROTECTED] wrote: > >> Whenever an error occurs within the transaction, PostgreSQL puts the >> whole transaction in an *ABORT* state, so that there is no difference >> at all between COMMITing or ROLLBACKing it. Even commands >> successfully carried out before the error ocurred are rolled back, >> even if I COMMIT the transaction, where no error message whatsoever >> is shown. > > In PostgreSQL all errors are currently considered unrecoverable, and > all statements in a transaction must commit or rollback together as a > single unit. In the future an implementation of nested transactions or > savepoints would presumably relax this limitation to only the > successfully committed subtransactions or statements that were not > separately rolled back to a previous savepoint. What I meant was not subtransactions or savepoints, but the funny behaviour that operations already successfully carried out never will be committed, just because some other operation later within the same transaction fails. This is far different from the behaviour of all other DMBS I know. Why not: begin work; insert into x values (1, 'hi'); --> success insert into x values (1, 'there'); --> failure due to primary key violation insert into x values (2, 'foo'); --> success commit work; and have two new tuples in the table? Why do _all_ of these operations have to be rolled back? I just don't get it that this has anything to do with savepoints or so. I don't see any problem with an error being recoverable, because the second insert failed and does not have to be recovered while the first and the third worked fine and does not have to be recovered either. When committing a transaction the effects of all operations that did not fail will be made permanent. This is how transaction processing is described in the literature. If a programmer wants the whole transaction to fail because one part failed, (s)he can always program a rollback in case of at least one error. But there should always be a difference between a rollback and a commit, after at least one statement changing data has reported a success. Hopefully this can be cleared and perhaps improved within PostgreSQL. Otherwise, PostgreSQL always claims to be close to standards. Sincerely, Holger -- [EMAIL PROTECTED], Bergisch Gladbach, Germany Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] transaction processing after error in statement
> > Why is that "funny behaviour" for you? By putting the statements into > a transaction block you told the data management system "I want this > group of statements to be atomic". Atomic means all or nothing. It > might not be exactly what you intended to say, and you have a point > if you conclude that PostgreSQL is limited because it doesn't let you > say anything in between all or nothing. But after all, thus far it is > only doing what you asked for. > It is "funny behaviour", because I expect those operations of the transaction, which executed successfully, to be performed in an atomic way. It is obvious that I cannot expect an operation which reported an error to have any effect. "Atomic" means that all operations (whether successful or not) will be carried out all together or none of them - but only the successful ones will have had an effect. Again: Why not make a difference between "commit" and "rollback" in a transaction in this case? Why not let the user decide which parts should be commited? The practical reason is that programming would become a lot more convenient. (if there is a practical reason it does not necessarily need a technical reason, I believe.) Sincerely, Holger -- [EMAIL PROTECTED], Bergisch Gladbach, Germany Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] transaction processing after error in statement
On 11 Nov, Jan Wieck wrote: > As long as we talk in an SQL context, can you please stick to SQL > terms? I don't know exactly what you mean with "operation". If for > example the statement > > DELETE FROM order_line WHERE ol_ordernum = 4711; > > has 12 matching rows in order_line, is an operation the removal of one > single order line or do the actions performed by the triggers fired > due to their removal count as separate operations for you? And if > there is one that cannot be deleted because a row in another table > with a foreign key references it, do you delete none of them or the > remaining 11? And if you decide to delete none, how do you magically > undo the work of the BEFORE triggers if you hit the foreign key after > successfully processing 5 rows? Is there an SQL return code for > "partial success"? OK, let's say "statement" instead of "operation". No, there is no partial success. Either a statement delivers an "OK" or it doesn't. Actually, you will have to undo anything the statement did before the first error occurs. This may mean that you need some kind of savepoint. If so, the necessity to implent this shows and should be given a high priority. I don't know how the other RDMBS do it, but they do. I am talking from the perspective of an RDBMS user, not as an implementor. Calling a procedure is a statement as well, and it includes all other procedures called from this procedure. So the statement level is always the statements that were carried out directly in the transaction. If anything within one statement fails, the statement was not carried out and must not have any effect. It is not important whether the procedure was fired by a trigger or called by another procedure. Are there any Open Source RDBMS which behave like Oracle, Access and Allbase? If so, one might look into their code to find out how they have implented it. Coming back to Standards, here is a quote from the ANSI document: 4.28 SQL-transactions The execution of a may be initiated implicitly by an implementation when it detects unrecoverable errors. When such an error occurs, an exception condition is raised: transaction rollback with an implementation-defined subclass code. This means that a rollback does not have to be initiated if an unrecoverable error occurs, it only _may_ happen. Since it is impractical, it should not. AN EXAMPLE: Let's have two tables, employees and doctors in a hospital. create table emp ( empno integer primary key, name varchar(40) ); create table doctor ( empno integer primary key references emp, beepernumber integer unique ); Now let a user enter the data of a doctor. First the data of the employee part are sent to the database: insert into emp values (1, 'Fred'); --> success Second the doctor-special data are sent to the database: insert into doctor values (1, 34); -->error, beepernumber already present, unique key violation Since there was an error, we let the user key in a different beeper number for the doctor and send the data to the database: insert into doctor (1, 45); -->should be successful (if 45 is not already there) and it _is_ in Allbase, Oracle, Access -->Postgres tells you something about and *ABORT* state We commit the transaction: commit work; Effect in all other databases: a successfully entered doctor Effect in PostgreSQL: nothing! To get the same effect in PostgreSQL, we would have to rollback (or commit, no difference) the transaction after the attempt of inserting the non-unique beeper number and then re-insert all data into all tables. WHY? It only makes programming more complex, thus error-prone. I would appreciate if most of the difficult tasks could be done within the database system - that's what it's there fore. The first reason is that the implementors usually are far more advanced programmers than the programmers of applications. The second reason is that this isolates the tricky programming from the already complex logic of the application. Additionally, whether one might personally think that this behaviour is important or not, it is what most commercial RDBMS do. So it would make porting of applications from e. g. Oracle to PostgreSQL dramatically easier. Until now, this has been one of the pitfalls. That's why I brought this up in the first place. Sincerely, Holger -- [EMAIL PROTECTED], Bergisch Gladbach, Germany Telefon (0 22 02) 5 99 91 oder (01 77) 7 92 24 66 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
