Thread: TRANSACTIONS
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
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 ...
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
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.
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
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
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
>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.
I'm not sure what Date says about this, but reading the spec I see
where the other way of looking at the commit is... I'm sure I
missed something, but here's the relevant parts from a draft that I see:
4.10.1 Checking of constraints When a constraint is checked other than at the end of an SQL- transaction,
ifit is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to
be checked has no effect other than entering the exception information into the diagnostics area. When a
<commitstatement> is executed, all constraints are effectively checked and, if any constraint is not
satisfied,then an exception condition is raised and the transaction is terminated by an implicit <rollback
statement>.
4.28 SQL TransactionsAn SQL-transaction is terminated by a <commit statement> or a <rollback statement>.
Ifan SQL-transaction is terminated by successful execution of a <commit statement>, then all changes made to
SQL-dataor schemas by that SQL-transaction are made persistent and accessible to all con- current and
subsequentSQL-transactions. If an SQL-transaction is terminated by a <rollback statement> or unsuccessful
executionof a <commit statement>, then all changes made to SQL-data or schemas by that SQL-transaction
arecanceled. Committed changes cannot be canceled. If execution of a <commit statement> is attempted, but
certainexception conditions are raised, it is unknown whether or not the changes made to SQL-data or schemas by
thatSQL-transaction are canceled or made persistent.
10.6 <constraint name definition> and <constraint attributes> 4) When a constraint is effectively checked, if
theconstraint is not satisfied, then an exception condition is raised: integrity constraint
violation.If this exception condition is raised as a result of executing a <commit statement>, then SQLSTATE
isnot set to integrity constraint violation, but is set to transaction rollback-integrity
constraintviolation (see the General Rules of Subclause 14.3, "<commit statement>").
14.3 <commit statement> 5) Case:
a) If any constraint is not satisfied, then any changes to SQL- data or schemas that were made
bythe current SQL-transaction are canceled and an exception condition is raised: transac- tion
rollback-integrityconstraint violation.
b) If any other error preventing commitment of the SQL- transaction has occurred, then any
changesto SQL-data or schemas that were made by the current SQL-transaction are canceled and an
exceptioncondition is raised: transaction rollback with an implementation-defined subclass value.
c) Otherwise, any changes to SQL-data or schemas that were made by the current SQL-transaction
aremade accessible to all concurrent and subsequent SQL-transactions.
---> Although I think that the current postgresql behavior is *better* than
the behavior as shown by the other databases, I think a case could be
made that 14.3 General Rule 5.a refers only to exceptions thrown by the
commit statement itself (any constraints that are checked at that time)
given the section of 4.10.1 and 10.6. This wouldn't be inconsistant
by type of exception, but would mean that immediate constraints and
deferred ones play by different rules for determining how a commit
works.
I'm not entirely sure I like that behavior though. It makes the
database less responsible for being in a reasonable state. For example,
if you've got a parent and two children, but one of the children fails
due to say an overflow exception, you really want to roll it all back,
but the database won't do that unless the overflow is checked
at commit time (ugh!?!).
Stephan
>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
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.
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
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.
>>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