On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote: > On Tue, Nov 29, 2005 at 07:44:05PM +0000, Tim Bunce wrote: > > On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: > > > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of > > > the transaction, whereas under MySQL and SQLite2 the transaction is allowed > > > to continue. > > > > PostgreSQL is non-standard (and inconvenient) in this respect. > > The inconvenience I'll grant, but the non-standard claim I think > needs some justification. When the database encounters an error in a > transaction, it is supposed to report an error. An error in a > transaction causes the whole transaction to fail: that's what the > atomicity rule of ACID means, I think. The fact that an individual statement has failed and returned an error (such as a duplicate key on insert) does not mean that the whole transaction has failed (ie been implicitly rolled back). The application may choose to explicitly rollback after it is informed that a statement has failed, or it could try an alternative action. I believe that's the case for Oracle, DB2, and Ingres (showing my age) but I don't have standards docs to hand - nor the time to read them :) No doubt someone will quote the relevant parts. (And no doubt the relevant parts will say "it depends" :) Tim. > I actually am sort of > unconvinced that SQLite's transactions are real ones -- I just did > some playing around with it, and it seems that any error allows you > to commit anyway. Certainly, MySQL's support of transactions is > occasionally pretty dodgy, unless you use the strict mode. > > But it's worth knowing that in Pg 8.1 and later, you can wrap such > things in a subtransaction and get out of it that way. > > A > > -- > Andrew Sullivan | ajs@xxxxxxxxxxxxxxx > I remember when computers were frustrating because they *did* exactly what > you told them to. That actually seems sort of quaint now. > --J.D. Baldwin
