>Bruce Momjian > Simon Riggs wrote: > > >Tom Lane > > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > > Most importantly, other references I have state that: the ANSI > > SQL-99 > > > > specification does require that if a statement errors then only that > > > > statement's changes are rolled back. > > > > > > ...if anybody has a copy of the actual spec could they check on > > this, so > > we can at least document carefully the current behaviour. > > > > > > > > No. The spec says > > > > > > The execution of a <rollback statement> may be initiated > > > implicitly by an SQL-implementation when it detects unrecoverable > > errors. > > > > > > and leaves it up to the implementation to define what is > > "unrecoverable". > > > Currently Postgres treats all errors as "unrecoverable". This is > > > certainly not ideal, but it is within the letter of the spec. > > > > Thanks for checking back to the spec, it's the only way. > > > > Improving on "not ideal" would be good, and would get even closer to > > full Oracle/SQLServer migration/compatibility. However, since I've never > > looked at that section of code, I couldn't comment on any particular > > approach nor implement such a change, so I'll shut up and be patient. > > Imagine this: > > BEGIN WORK; > LOCK oldtab; > CREATE_X TABLE newtab AS SELECT * FROM oldtab; > DELETE oldtab; > COMMIT > > In this case, you would want the database to abort on a syntax error, > right?
I'm happy to discuss this further, though I do want to reiterate my very first position, which is "its not a bug" and that I agree with Tom that the transaction semantics are defensible as they stand. With that said, please forgive the rather long winded explanation which I think is necessary to go through this topic in required detail. Overall, the database must end every transaction by either committing all changes made during it, or rolling back all changes. That is part of the ACID properties of a transaction. That part is not under discussion. The transaction semantics *during* a transaction can be implemented in a number of ways, yet in the end arrive at one of those two states. In the example above, consider what will occur if the first and second statements succeed and then the third statement fails: In one style of transaction semantics, the third statement can fail but the transaction does not abort (yet), control is returned to the application to decide what to do. If the error is a "retryable" error, such as those produced by a deadlock, then the application could decide to retry the statement and if it works commit the transaction - no re-execution of the first and second statement is required. In the second style of transaction semantics, the failure of the third statement causes the whole transaction, including all statements previously executed to rollback, with no option to retry and continue. In both cases, the transaction either commits or rollback occurs. No other option is possible at the end of the transaction, but in the first style of transaction semantics you get a "mid-way" decision point. This only refers to retryable errors, since errors like access rights violations and many other system errors aren't retryable. In the example you give regarding a syntax error, that's non-retryable, so yes I definitely do want the whole transaction rolled back. For reference only, Oracle, SQLServer and DB2 implement the first style - they give the option to retry. This is because historically, all of these RDBMSs were prone to deadlock because they originally implemented block or table level locking, before moving to their current level of function. Since deadlocks were frequent when using block level locking with OLTP style applications, it was important to conserve resources by not requiring the whole transaction to be retried. PostgreSQL uses the second style of transaction semantics. Teradata also originally implemented only the second style, though now implements both - which is how come I know this fairly obscure technical stuff. (Teradata refers to the first style as "ANSI" transaction semantics, though I am happy with Tom's reading of the standard.) Anyone reading this who is worried now about PostgreSQL should not be - transactions are very definitely watertight, no question. PostgreSQL's transaction semantics are fine since with MVCC, very few deadlock situations exist that aren't directly avoidable by good application coding. The need for "retryable" statements is much reduced. The functionality is not a bug, just the way it has been decided to implement transaction semantics. The only reason I have said PostgreSQL's behaviour is not ideal is that it is different from the main commercial RDBMS and could cause some porting annoyances in the error handling code of SQL applications - not too much of a problem, as long as you know about this and are willing to make some changes, hence the need for docs. My wish is to get close to 100% "application portability" in as many areas as possible (which includes functionality such as PITR, which I know you are aware of my interest in) - I do particularly appreciate the "it just works" approach of PostgreSQL with significantly easier automated facilities and advanced functionality. I'll write up some man page notes as you suggest, though without the long winded comparison of implementation techniques... Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
