Thread: Re: undefined behaviour for sub-transactions?
Tim Bunce <Tim.Bunce@pobox.com> wrote:
> I'll guess that what you're really after is to be able to call begin_work
> again whilst an earlier begin_work is in effect and have the DBI keep a
> counter of how deeply nested the begin_work calls are. Then commit would
> decrement the counter and only commit at the outer most level.
>
> If you really want that then it's straightforward to implement via a
> subclass.
This has been done. I'm only using it in two other packages so far,
but both those and it's own unit tests seem to work well.
http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
I've got some ideas for enhancements too, but those are a bit more
vauge. One of them is that there's differences in transaction behaviour
across drivers when a query within a transaction fails. eg; under
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.
There's gotta be some way to wrap this behaviour cleanly so that
your application can expect the same behaviour regardless of the underlying
database layer... but this leads to another question :-)
Are all database drivers expected to supply one method to execute a
query? Eg; do "do", "execute", etc. all always funnel into one core method
that returns success, error, or exception (if RaiseError) is turned on? Or
if I wanted to create this functionality and expect it to work under
multiple database drivers, should I override multiple methods?
Thanks,
Tyler
On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: > Tim Bunce <Tim.Bunce@pobox.com> wrote: > > I'll guess that what you're really after is to be able to call begin_work > > again whilst an earlier begin_work is in effect and have the DBI keep a > > counter of how deeply nested the begin_work calls are. Then commit would > > decrement the counter and only commit at the outer most level. > > > > If you really want that then it's straightforward to implement via a > > subclass. > > This has been done. I'm only using it in two other packages so far, > but both those and it's own unit tests seem to work well. > > http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/ > > I've got some ideas for enhancements too, but those are a bit more > vauge. One of them is that there's differences in transaction behaviour > across drivers when a query within a transaction fails. eg; under > 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. > There's gotta be some way to wrap this behaviour cleanly so that > your application can expect the same behaviour regardless of the underlying > database layer... There isn't, as far as I know, except to accept the 'lowest common denominator'. In this case that means forcing a rollback if any statement fails. > but this leads to another question :-) > > Are all database drivers expected to supply one method to execute a > query? Eg; do "do", "execute", etc. all always funnel into one core method > that returns success, error, or exception (if RaiseError) is turned on? Or > if I wanted to create this functionality and expect it to work under > multiple database drivers, should I override multiple methods? execute() is sufficient if the driver doesn't also supply it's own do() because DBI's default do() calls execute(). But some drivers do supply their own do() method (for good reasons). Tim.
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. 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@crankycanuck.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
Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> 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. 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.
Either way the end result is that some database drivers poison a
transaction if there's any error, others are selective about which errors
are fatal and which are not, and still others just don't care at all.
The end goal of DBIx::Transaction is to hide these differences from
the application so that transactions behave in a consistent way despite what
driver or driver options you're using, so on that note I've uploaded
DBIx-Transaction-0.002 to PAUSE, which will take the "lowest common
denominator", having any erronious query poison the entire transaction.
> 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.
Nifty! :)
Cheers,
Tyler
On 11/30/05, Tyler MacDonald <tyler@yi.org> wrote: > Andrew Sullivan <ajs@crankycanuck.ca> wrote: > > 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. 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. > > Either way the end result is that some database drivers poison a > transaction if there's any error, others are selective about which errors > are fatal and which are not, and still others just don't care at all. > that is a mis-conception... a transaction *must* be atomic (all or nothing)... the reason some databases act that bad is because they don't support savepoints, and because postgres does it doesn't need that awfulness... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote: > 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. Shouldn't that be 8.0 and later? That's when savepoints were introduced. Or are you referring to something else? -- Michael Fuhr
Jaime Casanova <systemguards@gmail.com> wrote:
> > Either way the end result is that some database drivers poison a
> > transaction if there's any error, others are selective about which errors
> > are fatal and which are not, and still others just don't care at all.
> that is a mis-conception... a transaction *must* be atomic (all or nothing)...
> the reason some databases act that bad is because they don't support
> savepoints, and because postgres does it doesn't need that
> awfulness...
OK, maybe I should have s/poison/behave properly with/. :-)
- Tyler
On Wed, Nov 30, 2005 at 02:58:15PM -0700, Michael Fuhr wrote:
>
> Shouldn't that be 8.0 and later? That's when savepoints were
> introduced. Or are you referring to something else?
Doh. Indeed. I was _thinking_ os something else, but not referring
to something else.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
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@crankycanuck.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
Tim Bunce wrote: > No doubt someone will quote the relevant parts. (And no doubt the > relevant parts will say "it depends" :) I believe, the "no doubt" part is showing your age, aka experience. :-)
Jaime Casanova <systemguards@gmail.com> writes: > that is a mis-conception... a transaction *must* be atomic (all or nothing)... > the reason some databases act that bad is because they don't support > savepoints, and because postgres does it doesn't need that > awfulness... Well it's not as bad as all that. It's still "atomic" in that an interruption cannot leave half of the transaction committed and half undone. In other words "all" is just "all of the actions that didn't produce an error". It's up to the client whether to commit the transaction after an error has occurred. It's great that Postgres follows the standard here, but don't go overboard on the criticism of other databases either. Where is Postgres at with psql using savepoints implicitly to wrap every client command btw? My single biggest pet peeve with Postgres is that setting autocommit off in psql is basically unusable because any typo forces you to start your transaction all over again. -- greg
>Where is Postgres at with psql using savepoints implicitly to wrap every >client command btw? My single biggest pet peeve with Postgres is that setting >autocommit off in psql is basically unusable because any typo forces you to >start your transaction all over again. > > Going to have to disagree with you here. I use it all day long ;). If I need a savepoint, I define one. Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
On Thu, Dec 01, 2005 at 01:04:52PM -0500, Greg Stark wrote:
> Where is Postgres at with psql using savepoints implicitly to wrap every
> client command btw? My single biggest pet peeve with Postgres is that setting
> autocommit off in psql is basically unusable because any typo forces you to
> start your transaction all over again.
Are you looking for 8.1's ON_ERROR_ROLLBACK?
test=> \set ON_ERROR_ROLLBACK interactive
test=> begin;
BEGIN
test=> create table foo (x integer);
CREATE TABLE
test=> roeiuqrepuqw;
ERROR: syntax error at or near "roeiuqrepuqw" at character 1
LINE 1: roeiuqrepuqw;
^
test=> insert into foo values (123);
INSERT 0 1
test=> commit;
COMMIT
test=> select * from foo;
x
-----
123
(1 row)
--
Michael Fuhr
Greg Stark <gsstark@mit.edu> writes:
> Where is Postgres at with psql using savepoints implicitly to wrap every
> client command btw?
I think that 8.1 psql can be told to do that.
regards, tom lane
Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
> > Where is Postgres at with psql using savepoints implicitly to wrap every
> > client command btw?
>
> I think that 8.1 psql can be told to do that.
Right:
\set ON_ERROR_ROLLBACK interactive
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073