Re: [GENERAL] undefined behaviour for sub-transactions?
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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 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 +, 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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] undefined behaviour for sub-transactions?
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
Re: [GENERAL] undefined behaviour for sub-transactions?
Jaime Casanova [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] undefined behaviour for sub-transactions?
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] undefined behaviour for sub-transactions?
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] undefined behaviour for sub-transactions?
Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] undefined behaviour for sub-transactions?
Tom Lane wrote: Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] undefined behaviour for sub-transactions?
On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: Tim Bunce [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] undefined behaviour for sub-transactions?
On Tue, Nov 29, 2005 at 07:44:05PM +, 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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] undefined behaviour for sub-transactions?
Andrew Sullivan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] undefined behaviour for sub-transactions?
On 11/30/05, Tyler MacDonald [EMAIL PROTECTED] wrote: Andrew Sullivan [EMAIL PROTECTED] 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 ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] undefined behaviour for sub-transactions?
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] undefined behaviour for sub-transactions?
Jaime Casanova [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] undefined behaviour for sub-transactions?
Tim Bunce [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
