Thread
-
TRANSACTIONS
Jose Soares <jose@sferacarta.com> — 2000-02-22T11:47:44Z
Hi all, The transactions should be the way to distinguish a relational database from others no-relational databases, (MySQL is the right example). We are very proud of PostgreSQL transactions but seems that it doesn't work in the right way. It shoud be important to be sure that PostgreSQL is compliant with SQL92. I need absolutely to use transactions but until now I could not use it, in my case it is completely unusable. I tried transactions in other databases and I compared it with PostgreSQL and no one of which I tried has the same PostgreSQL behavior. I tried the following script: ------------------------------------------------------- PostgreSQL: ------------------------------------------------------- begin transaction; create table tmp(a int); insert into tmp values (1); insert into tmp values (1000000000000000000000000000000000); ERROR: pg_atoi: error reading "1000000000000000000000000000000000": Numerical result out of range commit; select * from tmp; ERROR: tmp: Table does not exist. ------------------------------------------------------- Interbase, Oracle,Informix,Solid,Ms-Access,DB2: ------------------------------------------------------- connect hygea.gdb; create table temp(a int); insert into temp values (1); insert into temp values (1000000000000000000000000000000000); commit; select * from temp; arithmetic exception, numeric overflow, or string truncation A =========== 1 I would like to know what the Standard says and who is in the rigth path PostgreSQL or the others, considering the two examples reported below. Comments? -- Jose' Soares Bologna, Italy Jose@sferacarta.com -
RE: [HACKERS] TRANSACTIONS
Dmitry Samersoff <dms@wplus.net> — 2000-02-22T12:13:12Z
On 22-Feb-2000 Jose Soares wrote: > begin transaction; > create table tmp(a int); > insert into tmp values (1); > insert into tmp values (1000000000000000000000000000000000); > ERROR: pg_atoi: error reading "1000000000000000000000000000000000": > Numerical result out of range > commit; > select * from tmp; > ERROR: tmp: Table does not exist. > ------------------------------------------------------- > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: ^^^^^^^^^ AFAIK, MS Access have no transactions inside it, Informix (at least old versions I worked with) always perform create,drop, alter object outside transaction but IMHO it's not right behavior. I believe postgres's behavior more meaningful, but IMHO, this example is quite far from real life. -- Dmitry Samersoff, dms@wplus.net, ICQ:3161705 http://devnull.wplus.net * There will come soft rains ... -
Re: [HACKERS] TRANSACTIONS
Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-22T16:32:51Z
Jose Soares <jose@sferacarta.com> writes: > ------------------------------------------------------- > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > ------------------------------------------------------- > connect hygea.gdb; > create table temp(a int); > insert into temp values (1); > insert into temp values (1000000000000000000000000000000000); > commit; > select * from temp; > arithmetic exception, numeric overflow, or string truncation > A > =========== > 1 > I would like to know what the Standard says and who is in the rigth path > PostgreSQL or the others, considering the two examples reported below. I think those other guys are unquestionably failing to conform to SQL92. 6.10 general rule 3.a says a) If SD is exact numeric or approximate numeric, then Case: i) If there is a representation of SV in the data type TD that does not lose any leading significant digits after rounding or truncating if necessary, then TV is that rep- resentation. The choice of whether to round or truncate is implementation-defined. ii) Otherwise, an exception condition is raised: data exception- numeric value out of range. and 3.3.4.1 says The phrase "an exception condition is raised:", followed by the name of a condition, is used in General Rules and elsewhere to indicate that the execution of a statement is unsuccessful, ap- plication of General Rules, other than those of Subclause 12.3, "<procedure>", and Subclause 20.1, "<direct SQL statement>", may be terminated, diagnostic information is to be made available, and execution of the statement is to have no effect on SQL-data or schemas. The effect on <target specification>s and SQL descriptor areas of an SQL-statement that terminates with an exception condi- tion, unless explicitly defined by this International Standard, is implementation-dependent. I see no way that allowing the transaction to commit after an overflow can be called consistent with the spec. regards, tom lane -
Re: [HACKERS] TRANSACTIONS
Don Baccus <dhogaza@pacifier.com> — 2000-02-22T18:47:16Z
At 11:32 AM 2/22/00 -0500, Tom Lane wrote: >I see no way that allowing the transaction to commit after an overflow >can be called consistent with the spec. You are absolutely right. The whole point is that either a) everything commits or b) nothing commits. Having some kinds of exceptions allow a partial commit while other exceptions rollback the transaction seems like a very error-prone programming environment to me. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
-
Re: [GENERAL] TRANSACTIONS
Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-02-23T03:50:26Z
At 12:47 PM 22-02-2000 +0100, Jose Soares wrote: >begin transaction; >create table tmp(a int); >insert into tmp values (1); >insert into tmp values (1000000000000000000000000000000000); >ERROR: pg_atoi: error reading "1000000000000000000000000000000000": >Numerical result out of range >commit; >select * from tmp; >ERROR: tmp: Table does not exist. >------------------------------------------------------- >Interbase, Oracle,Informix,Solid,Ms-Access,DB2: >------------------------------------------------------- >connect hygea.gdb; >create table temp(a int); >insert into temp values (1); >insert into temp values (1000000000000000000000000000000000); >commit; >select * from temp; > >arithmetic exception, numeric overflow, or string truncation > > A >=========== > 1 Stuff done in a transaction cannot be committed if there is an error. So looks like Postgres is right and the rest are wrong ;). Also I believe Oracle does a commit behind your back whenever you do a create table or stuff like that. However I did have problems rolling back a create table in Postgres before- after rolling back I could not recreate a table of the same name. I had to manually unlink the table at filesystem level. Not sure if that has been fixed. On a different note I wonder if there could be layers of transactions (without having to create two separate connections).. Begin transaction A Try to do transaction B Depending on whether B succeeds or fails we do the following stuff differently blahblahblah If blahblablah fails then rollback the whole thingy, including nested transaction B (even if "committed") commit transaction A Sounds like a headache to implement tho (performance hits etc), and probably more an academic feature than anything. So I'm just wondering just for the sake of wondering ;). If we go that way lots of people will have a new toy to play with (to sell as well) and things will get even more complex.. <grin>. Cheerio, Link.
-
Re: [HACKERS] TRANSACTIONS
Jose Soares <jose@sferacarta.com> — 2000-02-23T13:22:04Z
Dmitry Samersoff wrote: > On 22-Feb-2000 Jose Soares wrote: > > begin transaction; > > create table tmp(a int); > > insert into tmp values (1); > > insert into tmp values (1000000000000000000000000000000000); > > ERROR: pg_atoi: error reading "1000000000000000000000000000000000": > > Numerical result out of range > > commit; > > select * from tmp; > > ERROR: tmp: Table does not exist. > > ------------------------------------------------------- > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > ^^^^^^^^^ > AFAIK, MS Access have no transactions inside it, > Informix (at least old versions I worked with) always > perform create,drop, alter object outside transaction > but IMHO it's not right behavior. I don't know and I don't care about old software, I'm talking about Ms_Access97 and Informix 8. -- Jose' Soares Bologna, Italy Jose@sferacarta.com
-
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Jose Soares <jose@sferacarta.com> — 2000-02-23T13:40:53Z
Sorry for my english, Tom, but the point is another, I'm talking about transactions not about error messages. This is only a stupid example how to abort a transaction, PostgreSQL aborts automatically transactions if an error occurs, even an warning or a syntax error. I can believe that all other databases are wrong and only we (PostgreSQL) are right, but please try to understand me. This is not easy to believe anyway. I'm looking for another database with a behavior like PostgreSQL but I can't find it, and I tried a lot of them until now. Do you know some database with transactions like PostgreSQL? Tom Lane wrote: > Jose Soares <jose@sferacarta.com> writes: > > ------------------------------------------------------- > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > > ------------------------------------------------------- > > connect hygea.gdb; > > create table temp(a int); > > insert into temp values (1); > > insert into temp values (1000000000000000000000000000000000); > > commit; > > select * from temp; > > > arithmetic exception, numeric overflow, or string truncation > > > A > > =========== > > 1 > > > I would like to know what the Standard says and who is in the rigth path > > PostgreSQL or the others, considering the two examples reported below. > > I think those other guys are unquestionably failing to conform to SQL92. > 6.10 general rule 3.a says > > a) If SD is exact numeric or approximate numeric, then > > Case: > > i) If there is a representation of SV in the data type TD > that does not lose any leading significant digits after > rounding or truncating if necessary, then TV is that rep- > resentation. The choice of whether to round or truncate is > implementation-defined. > > ii) Otherwise, an exception condition is raised: data exception- > numeric value out of range. > > and 3.3.4.1 says > > The phrase "an exception condition is raised:", followed by the > name of a condition, is used in General Rules and elsewhere to > indicate that the execution of a statement is unsuccessful, ap- > plication of General Rules, other than those of Subclause 12.3, > "<procedure>", and Subclause 20.1, "<direct SQL statement>", may > be terminated, diagnostic information is to be made available, > and execution of the statement is to have no effect on SQL-data or > schemas. The effect on <target specification>s and SQL descriptor > areas of an SQL-statement that terminates with an exception condi- > tion, unless explicitly defined by this International Standard, is > implementation-dependent. > > I see no way that allowing the transaction to commit after an overflow > can be called consistent with the spec. > > regards, tom lane > > ************ -- Jose' Soares Bologna, Italy Jose@sferacarta.com
-
Re: [HACKERS] TRANSACTIONS
Jose Soares <jose@sferacarta.com> — 2000-02-23T13:46:54Z
Don Baccus wrote: > At 11:32 AM 2/22/00 -0500, Tom Lane wrote: > > >I see no way that allowing the transaction to commit after an overflow > >can be called consistent with the spec. > > You are absolutely right. The whole point is that either a) everything > commits or b) nothing commits. > > Having some kinds of exceptions allow a partial commit while other > exceptions rollback the transaction seems like a very error-prone > programming environment to me. > It is hard to believe all world is wrong and only we are right. Isn't it ? ;) > > - Don Baccus, Portland OR <dhogaza@pacifier.com> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. > > ************ -- Jose' Soares Bologna, Italy Jose@sferacarta.com
-
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-23T15:16:06Z
>Sorry for my english, Tom, but the point is another, I'm talking >about transactions not about error messages. > >This is only a stupid example how to abort a transaction, PostgreSQL >aborts automatically transactions if an error occurs, even an warning >or a syntax error. > >I can believe that all other databases are wrong and only we >(PostgreSQL) are right, but please try to understand me. This is not >easy to believe anyway. > >I'm looking for another database with a behavior like PostgreSQL but >I can't find it, and I tried a lot of them until now. > >Do you know some database with transactions like PostgreSQL? I personally don't feel qualified to interpret the standard. But I would like to pipe in a little on the issue of what is desirable. By default, as a developer, I would be quite unhappy with the behavior of those other databases (allowing a commit after an insert has failed). If I do a bulk copy into an existing database, and one copy fails, that sort of behavior could concievably render my database unusable with not possibility of recovery. So in that sense, from the point of view of desirability I think postgres got it right. But then I thought about if from a programming language point of view. Consider the following code (I use perl/DBI as an example). ========================= example ========================= $dbh->{AutoCommit} = 0; $dbh->do("CREATE TABLE tmp (a int unique,b int)"); while (<>){ if (/([0-9]+) ([0-9]+)/) { $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; } } $dbh->commit; $dbh->disconnect; ========================= end ============================ This incorporates a very common idiom within a transaction block. Of course, this fails. As far as I can tell from the preceding discussion, there is no way to "sanitize" the transaction once you have fixed the error. IMHO, it would be EXTREMELY useful to be able to implement the above transaction. But not by default. I'm not sure what a resonable syntax would be - several come to mind. You could have "SANITIZE TRANSACTION" or "\unset warning", whatever, the exact syntax matters little to me. But without this sort of capability, people who do programatic error checking and correction (which seems like a good thing) are essentially penalized because they cannot effectively use transactions. Apologies if it is already possible to do this. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Keith G. Murphy <keithmur@mindspring.com> — 2000-02-24T18:16:04Z
Karl DeBisschop wrote: > > >Sorry for my english, Tom, but the point is another, I'm talking > >about transactions not about error messages. > > > >This is only a stupid example how to abort a transaction, PostgreSQL > >aborts automatically transactions if an error occurs, even an warning > >or a syntax error. > > > >I can believe that all other databases are wrong and only we > >(PostgreSQL) are right, but please try to understand me. This is not > >easy to believe anyway. > > > >I'm looking for another database with a behavior like PostgreSQL but > >I can't find it, and I tried a lot of them until now. > > > >Do you know some database with transactions like PostgreSQL? > > I personally don't feel qualified to interpret the standard. But I > would like to pipe in a little on the issue of what is desirable. > > By default, as a developer, I would be quite unhappy with the behavior > of those other databases (allowing a commit after an insert has > failed). If I do a bulk copy into an existing database, and one copy > fails, that sort of behavior could concievably render my database > unusable with not possibility of recovery. So in that sense, from the > point of view of desirability I think postgres got it right. > > But then I thought about if from a programming language point of > view. Consider the following code (I use perl/DBI as an example). > > ========================= example ========================= > > $dbh->{AutoCommit} = 0; > $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > while (<>){ > if (/([0-9]+) ([0-9]+)/) { > $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > } > } > $dbh->commit; > $dbh->disconnect; > > ========================= end ============================ > > This incorporates a very common idiom within a transaction block. Of > course, this fails. As far as I can tell from the preceding > discussion, there is no way to "sanitize" the transaction once you > have fixed the error. IMHO, it would be EXTREMELY useful to be able to > implement the above transaction. But not by default. > > I'm not sure what a resonable syntax would be - several come to mind. > You could have "SANITIZE TRANSACTION" or "\unset warning", whatever, > the exact syntax matters little to me. But without this sort of > capability, people who do programatic error checking and correction > (which seems like a good thing) are essentially penalized because they > cannot effectively use transactions. > To continue with your example, this should work: > $dbh->{AutoCommit} = 0; > $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > while (<>){ > if (/([0-9]+) ([0-9]+)/) { > eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")}; > if ($@) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > } > } > $dbh->commit; > $dbh->disconnect; Sadly, it does not, as far as I can tell. In fact, it seems to corrupt the database to where you can't create the table tmp anymore, on my system. I certainly never get a table. What's the rationale behind having the database blow out eval's error trapping? Can't see where letting a program recover from an error in a statement compromises atomicity. > Apologies if it is already possible to do this. > Likewise. -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-24T19:16:05Z
To summarize, I stated that the following does not work with postgresql: > $dbh->{AutoCommit} = 0; > $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > while (<>){ > if (/([0-9]+) ([0-9]+)/) { > $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > } > } > $dbh->commit; > $dbh->disconnect; I further said that regardless of what the SQL standard gurus decide, I felt that postgresql currently gives desirable behavior - once a transaction is started, it's either all or nothing. But then I qualified that by saying I'd like somehow to be able to "sanitize" the transaction so that the common idiom above could be made to work. >From my examination, the difference between our two examples is Original: KD> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); Modified: KM> eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")}; >From the point of view if the DBMS, i believe these are identical - in both cases the query is issued to the DMBS and the overall transaction becomes "contaminated". And as I said before, this is exactly what I'd like to have happen in the default case. It's not that eval's error trapping is blown out - it's that the transaction defined by the AutoCommit cannot complete because a part of it cannot complete -- that's what atomicity means. At least that's the way it looks to me. But as I started out saying, I don't feel qualified to interpret the standard - I might be wrong, plain and simple. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-02-25T06:41:32Z
At 02:16 PM 24-02-2000 -0500, Karl DeBisschop wrote: > >To summarize, I stated that the following does not work with >postgresql: > >> $dbh->{AutoCommit} = 0; >> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); >> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); >> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; >> $dbh->commit; >> $dbh->disconnect; > >It's not that eval's error trapping is blown out - it's that the >transaction defined by the AutoCommit cannot complete because a part >of it cannot complete -- that's what atomicity means. Maybe I don't understand the situation. But it doesn't seem to be a big problem. With postgres you have ensure that your application filters the data properly before sticking it into the database. Then if the insert fails, it's probably a serious database problem and in that case it's best that the whole transaction is aborted anyway. It indeed is a problem if the database engine is expected to parse the data. For example - if you send in a date value, and the database engine chokes on it. With the nonpostgresql behaviour you can still insert a NULL instead for "Bad date/ Unknown date". But from the security point of view it is best to reduce the amount of parsing done by the database engine. Make sure the app sanitises and massages everything so that the database has no problems with the data. It can be a pain sometimes to figure out what the database can take (which is why I've been asking for the limits for Postgresql fields and such- so the app can keep everything within bounds or grumble to the user/vandal). Once everything is set up nicely, if the database grumbles then the app screwed up somehow (the vandal got through) and it's best to rollback everything (we're lucky if the database just grumbled). Cheerio, Link. -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-25T19:26:48Z
>>To summarize, I stated that the following does not work with >>postgresql: >> >>> $dbh->{AutoCommit} = 0; >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; >>> $dbh->commit; >>> $dbh->disconnect; >> >>It's not that eval's error trapping is blown out - it's that the >>transaction defined by the AutoCommit cannot complete because a part >>of it cannot complete -- that's what atomicity means. > >Maybe I don't understand the situation. But it doesn't seem to be a big >problem. > >With postgres you have ensure that your application filters the data >properly before sticking it into the database. Then if the insert fails, >it's probably a serious database problem and in that case it's best that >the whole transaction is aborted anyway. This reason this idiom is used has nothing to do with validation. I agree that the application has the resopnsibility to cehck for valid data. The usefulness of the idion is that in a mutli-user environment, this is a basic way to update data that may or may not already have a key in the table. You can't do a "SELECT COUNT" because in the time between when you SELECT and INSERT (assuming the key is not already there) someone may have done a separate insert. The only other way I know to do this is to lock the entire table against INSERTs which has obvious performance effects. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Keith G. Murphy <keithmur@mindspring.com> — 2000-02-25T20:18:24Z
Karl DeBisschop wrote: > > To summarize, I stated that the following does not work with > postgresql: > > > $dbh->{AutoCommit} = 0; > > $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > > while (<>){ > > if (/([0-9]+) ([0-9]+)/) { > > $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > > } > > } > > $dbh->commit; > > $dbh->disconnect; > > I further said that regardless of what the SQL standard gurus decide, > I felt that postgresql currently gives desirable behavior - once a > transaction is started, it's either all or nothing. But then I > qualified that by saying I'd like somehow to be able to "sanitize" the > transaction so that the common idiom above could be made to work. > > >From my examination, the difference between our two examples is > > Original: > KD> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > Modified: > KM> eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")}; > > >From the point of view if the DBMS, i believe these are identical - in > both cases the query is issued to the DMBS and the overall transaction > becomes "contaminated". And as I said before, this is exactly what > I'd like to have happen in the default case. > > It's not that eval's error trapping is blown out - it's that the > transaction defined by the AutoCommit cannot complete because a part > of it cannot complete -- that's what atomicity means. I don't have the SQL92 standard with me, so I can't speak to how it defines atomicity. Seems to me it's a means to an end, though, the end being that all of the statements in the sequence are performed, or none. But if the program traps an error, then does something to recover, you could argue that it's changed the sequence. As long as the program has to explicitly Commit, why not? It seems desirable to me that if one statement causes an error, it doesn't affect the database, and the error is returned to the client. If the client has RaiseError on, which he should, and doesn't do anything to explicitly trap, it's going to blow out the program and thus the transaction should be rolled back, which is a good thing. But if he does explicitly trap, as I do above, why not let him stay within the transaction, since the statement in error has not done anything? I agree that do get Postgresql to do this might be a lot to expect (nested transactions are required, I guess). I'm just not sure that it's a *wrong*, or non-conformant, thing to expect. (By the way, I know VB/Access does it this way. My production code, however, never takes advantage of this, to my knowledge.) Addressing Lincoln Yeoh's point in another post, to take the approach that all your data should conform to all database requirements before you enter a transaction seems to me to lead to redundancy: the program code checks and the database checks. Should you have to synchronize all relevant code every time a field requirement is changed? I agree that to simply continue without error and let the program blindly commit, which some folks claim other databases do, is wrong and screws atomicity. What is also wrong is to allow you to do a Commit when the database is in an error state, so that you have (in this case) a table in limbo that can't be created or seen, behavior that Jose Soares and I both saw with Postgresql (6.5.1 in my case). Why shouldn't Postgresql just implicitly Rollback at this point, since you can't do anything (constructive) to the database within the transaction anyway? -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
kaiq@realtyideas.com — 2000-02-25T20:49:19Z
On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > >>To summarize, I stated that the following does not work with > >>postgresql: > >> > >>> $dbh->{AutoCommit} = 0; > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > >>> $dbh->commit; > >>> $dbh->disconnect; > >> > > The usefulness of the idion is that in a mutli-user environment, this > is a basic way to update data that may or may not already have a key > in the table. You can't do a "SELECT COUNT" because in the time > between when you SELECT and INSERT (assuming the key is not already > there) someone may have done a separate insert. The only other way I > know to do this is to lock the entire table against INSERTs which has > obvious performance effects. sounds right, but ;-) why you use the transaction in the first place? -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-25T21:58:42Z
> From: <kaiq@realtyideas.com> > On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > > > > >>To summarize, I stated that the following does not work with > > >>postgresql: > > >> > > >>> $dbh->{AutoCommit} = 0; > > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > > >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > > >>> $dbh->commit; > > >>> $dbh->disconnect; > > >> > > > > The usefulness of the idion is that in a mutli-user environment, this > > is a basic way to update data that may or may not already have a key > > in the table. You can't do a "SELECT COUNT" because in the time > > between when you SELECT and INSERT (assuming the key is not already > > there) someone may have done a separate insert. The only other way I > > know to do this is to lock the entire table against INSERTs which has > > obvious performance effects. > sounds right, but ;-) why you use the transaction in the first place? Rememeber that this is just an example to illustrate what sort of behaviour one user would find useful in tranasctions, so it is a little simplistic. Not overly simplistic, though, I think. I'd want a transaction because I'm doing a bulk insert into this live database - say syncing in a bunch of data from a slave server while the master is still running. If one (or more) insert(s) fail, I want to revert back to the starting pint so I can fix the cause of the failed insert and try again with the database in a known state. (there may, for instance, be relationships beteewn the b field such that if only part of the bulk insert suceeds, the database is rendered corrupt). -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Karl DeBisschop <kdebisschop@range.infoplease.com> — 2000-02-25T22:24:57Z
> From: "Keith G. Murphy" <keithmur@mindspring.com> > > Karl DeBisschop wrote: > > > > To summarize, I stated that the following does not work with > > postgresql: > > > > > $dbh->{AutoCommit} = 0; > > > $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > > > while (<>){ > > > if (/([0-9]+) ([0-9]+)/) { > > > $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > > if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > > > } > > > } > > > $dbh->commit; > > > $dbh->disconnect; > > > > I further said that regardless of what the SQL standard gurus decide, > > I felt that postgresql currently gives desirable behavior - once a > > transaction is started, it's either all or nothing. But then I > > qualified that by saying I'd like somehow to be able to "sanitize" the > > transaction so that the common idiom above could be made to work. > > > > >From my examination, the difference between our two examples is > > > > Original: > > KD> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > > > Modified: > > KM> eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")}; > > > > >From the point of view if the DBMS, i believe these are identical - in > > both cases the query is issued to the DMBS and the overall transaction > > becomes "contaminated". And as I said before, this is exactly what > > I'd like to have happen in the default case. > > > > It's not that eval's error trapping is blown out - it's that the > > transaction defined by the AutoCommit cannot complete because a part > > of it cannot complete -- that's what atomicity means. > > I don't have the SQL92 standard with me, so I can't speak to how it > defines atomicity. Seems to me it's a means to an end, though, the end > being that all of the statements in the sequence are performed, or > none. But if the program traps an error, then does something to > recover, you could argue that it's changed the sequence. I agree > As long as the program has to explicitly Commit, why not? It seems > desirable to me that if one statement causes an error, it doesn't affect > the database, and the error is returned to the client. If the client > has RaiseError on, which he should, and doesn't do anything to > explicitly trap, it's going to blow out the program and thus the > transaction should be rolled back, which is a good thing. But if he > does explicitly trap, as I do above, why not let him stay within the > transaction, since the statement in error has not done anything? It is not sufficient that the statement in error has done nothing - the postmaster in the general case cannot know what relationships should exist between the non-key data. It is quite possible that not having a record inserted could make the database fundamentally unusable. Of course, in my original example and in yours, error is trapped and the situation is (hopefully) fixed by the subsequent update. Thus, in my post I suggested that postgres could provide some sort of mechanism to explicitly 'sanitize' the transaction and allow it to commit. In otherwords, I think we are basically proposing the same thing. > I agree that do get Postgresql to do this might be a lot to expect > (nested transactions are required, I guess). I'm just not sure that > it's a *wrong*, or non-conformant, thing to expect. > > (By the way, I know VB/Access does it this way. My production code, > however, never takes advantage of this, to my knowledge.) >From what I gather, extending postgresql this way is planned anyway - it may not happen tomorrow, but notheing in here seems like a very new concept to the development team. > Addressing Lincoln Yeoh's point in another post, to take the approach > that all your data should conform to all database requirements before > you enter a transaction seems to me to lead to redundancy: the program > code checks and the database checks. Should you have to synchronize all > relevant code every time a field requirement is changed? > > I agree that to simply continue without error and let the program > blindly commit, which some folks claim other databases do, is wrong and > screws atomicity. > > What is also wrong is to allow you to do a Commit when the database is > in an error state, so that you have (in this case) a table in limbo that > can't be created or seen, behavior that Jose Soares and I both saw with > Postgresql (6.5.1 in my case). Why shouldn't Postgresql just implicitly > Rollback at this point, since you can't do anything (constructive) to > the database within the transaction anyway? Yes, the table in limbo is certainly a problem/bug. But even this bug, in my estimation, is better than allowing a transaction with an error in it to commit without explicitly clearing the error status. The bug is a pain in the neck, but it apparently has been fixed in 6.5.3 -- so why not upgrade, no dumps are required. But even with the bug, it can save you from unknowingly foisting inaccurate data on your customers which is still a good thing. As for whether postgress should implicitly roll back, I don't think it should - remember that the frontend, which is very likely operating in robot mode, is still firing queries at the database. An inpmlicit rollback means starting a new transaction. And that could lead to a data integrity problem as well. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
kaiq@realtyideas.com — 2000-02-25T23:40:09Z
On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > > From: <kaiq@realtyideas.com> > > On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > > > > > > > >>To summarize, I stated that the following does not work with > > > >>postgresql: > > > >> > > > >>> $dbh->{AutoCommit} = 0; > > > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > > > >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > > >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > > > >>> $dbh->commit; > > > >>> $dbh->disconnect; > > > >> > > > > > > The usefulness of the idion is that in a mutli-user environment, this > > > is a basic way to update data that may or may not already have a key > > > in the table. You can't do a "SELECT COUNT" because in the time > > > between when you SELECT and INSERT (assuming the key is not already > > > there) someone may have done a separate insert. The only other way I > > > know to do this is to lock the entire table against INSERTs which has > > > obvious performance effects. > > > sounds right, but ;-) why you use the transaction in the first place? > > Rememeber that this is just an example to illustrate what sort of > behaviour one user would find useful in tranasctions, so it is a > little simplistic. Not overly simplistic, though, I think. > > I'd want a transaction because I'm doing a bulk insert into this live > database - say syncing in a bunch of data from a slave server while > the master is still running. If one (or more) insert(s) fail, I want > to revert back to the starting pint so I can fix the cause of the > failed insert and try again with the database in a known state. > (there may, for instance, be relationships beteewn the b field such > that if only part of the bulk insert suceeds, the database is rendered > corrupt). > thanks. I'm on your side now ;-) -- it is a useful senario. the question are: 1) can nested transaction be typically interpreted to handle this situation? If is is, then, it should be handled by that "advanced feature", not plain transaction ; 2) on the other hand, can sql92's (plain) transaction be interpreted in the way that above behavior is legitimate? -
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Keith G. Murphy <keithmur@mindspring.com> — 2000-02-28T16:44:29Z
kaiq@realtyideas.com wrote: > > On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > > > > > From: <kaiq@realtyideas.com> > > > On Fri, 25 Feb 2000, Karl DeBisschop wrote: > > > > > > > > > > > >>To summarize, I stated that the following does not work with > > > > >>postgresql: > > > > >> > > > > >>> $dbh->{AutoCommit} = 0; > > > > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)"); > > > > >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); > > > > >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; > > > > >>> $dbh->commit; > > > > >>> $dbh->disconnect; > > > > >> > > > > > > > > The usefulness of the idion is that in a mutli-user environment, this > > > > is a basic way to update data that may or may not already have a key > > > > in the table. You can't do a "SELECT COUNT" because in the time > > > > between when you SELECT and INSERT (assuming the key is not already > > > > there) someone may have done a separate insert. The only other way I > > > > know to do this is to lock the entire table against INSERTs which has > > > > obvious performance effects. > > > > > sounds right, but ;-) why you use the transaction in the first place? > > > > Rememeber that this is just an example to illustrate what sort of > > behaviour one user would find useful in tranasctions, so it is a > > little simplistic. Not overly simplistic, though, I think. > > > > I'd want a transaction because I'm doing a bulk insert into this live > > database - say syncing in a bunch of data from a slave server while > > the master is still running. If one (or more) insert(s) fail, I want > > to revert back to the starting pint so I can fix the cause of the > > failed insert and try again with the database in a known state. > > (there may, for instance, be relationships beteewn the b field such > > that if only part of the bulk insert suceeds, the database is rendered > > corrupt). > > > thanks. I'm on your side now ;-) -- it is a useful senario. > the question are: 1) can nested transaction be typically interpreted > to handle this situation? If is is, then, it should be handled by that > "advanced feature", not plain transaction ; I guess like this (got rid of AutoCommit, because that looks funny nested): $dbh->RaiseError = 1; $dbh->StartTransaction; eval { $dbh->do("CREATE TABLE tmp (a int unique,b int)"); while (blahblahblah) { $dbh->StartTransaction; eval { $dbh->do("INSERT INTO tmp VALUES ($1,$2)"); }; if ($@) { $dbh->Rollback; {$dbh->do("UPDATE tmp SET b=$2 where a=$1")}; } else { $dbh->Commit; } } } if ($@) { $dbh->rollback; } else { $dbh->commit; } $dbh->disconnect; I.e., try the INSERT within the inner transaction; if it fails, roll it back and do the UPDATE; if that fails, blow out the whole outer transaction. You could do the whole thing checking a return value as in the original example, but the eval and RaiseError are canonical, according the the docs. > 2) on the other hand, can sql92's (plain) transaction be interpreted > in the way that above behavior is legitimate? > Well, I'm not sure of the necessity of nested transactions in the case of continuing a transaction after a single-row insert has failed, but that's implementation details I'm not familiar with... i.e., I'm not having to code the danged thing!