Re: undefined behaviour for sub-transactions?

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(at)crankycanuck(dot)ca
> 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