Tim Bunce <Tim.Bunce (AT) pobox (DOT) comwrote:
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/~
I've got some ideas for enhancements too, but those are a bit more
vauge. 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?
if I wanted to create this functionality and expect it to work under
multiple database drivers, should I override multiple methods?
Thanks,
Tyler
(end of broadcast)
TIP 4: Have you searched our list archives?
http://archives.postgresql.org

Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
Tim Bunce <Tim.Bunce (AT) pobox (DOT) comwrote:
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/~

I've got some ideas for enhancements too, but those are a bit more
vauge. 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?
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.

(end of broadcast)
TIP 5: don't forget to increase your free space map settings

Andrew Sullivan <ajs (AT) crankycanuck (DOT) cawrote:
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

(end of broadcast)
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jaime Casanova <systemguards (AT) gmail (DOT) comwrote:
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

K, maybe I should have s/poison/behave properly with/. :-)
- Tyler

(end of broadcast)
TIP 3: Have you checked our extensive FAQ?

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. :-)
(end of broadcast)
TIP 6: explain analyze is your friend

Greg Stark <gsstark (AT) mit (DOT) eduwrites:
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

(end of broadcast)
TIP 3: Have you checked our extensive FAQ?