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



Tue, 22 Feb 2000 12:47:44 +0100
Jose Soares <jose@sferacarta.com>
TRANSACTIONS

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 ...

Tue, 22 Feb 2000 15:13:12 +0300 (MSK)
Dmitry Samersoff <dms@wplus.net>
RE: [HACKERS] TRANSACTIONS
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

Tue, 22 Feb 2000 11:32:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] TRANSACTIONS 
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.

Tue, 22 Feb 2000 10:47:16 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] TRANSACTIONS 
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.


Wed, 23 Feb 2000 11:50:26 +0800
Lincoln Yeoh <lylyeoh@mecomb.com>
Re: [GENERAL] TRANSACTIONS


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



Wed, 23 Feb 2000 14:22:04 +0100
Jose Soares <jose@sferacarta.com>
Re: [HACKERS] TRANSACTIONS
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



Wed, 23 Feb 2000 14:40:53 +0100
Jose Soares <jose@sferacarta.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
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



Wed, 23 Feb 2000 14:46:54 +0100
Jose Soares <jose@sferacarta.com>
Re: [HACKERS] TRANSACTIONS

>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, if it 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 <commit statement> 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 Transactions
	An SQL-transaction
         is terminated by a <commit statement> or a <rollback statement>.
         If an SQL-transaction is terminated by successful execution of a
         <commit statement>, then all changes made to SQL-data or schemas by
         that SQL-transaction are made persistent and accessible to all con-
         current and subsequent SQL-transactions. If an SQL-transaction is
         terminated by a <rollback statement> or unsuccessful execution of
         a <commit statement>, then all changes made to SQL-data or schemas
         by that SQL-transaction are canceled. Committed changes cannot be
         canceled. If execution of a <commit statement> is attempted, but
         certain exception conditions are raised, it is unknown whether or
         not the changes made to SQL-data or schemas by that SQL-transaction
         are canceled or made persistent.

10.6 <constraint name definition> and <constraint attributes>
         4) When a constraint is effectively checked, if the constraint 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 is not
            set to integrity constraint violation, but is set to transaction
            rollback-integrity constraint violation (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 by the current SQL-transaction
              are canceled and an exception condition is raised: transac-
              tion rollback-integrity constraint violation.

            b) If any other error preventing commitment of the SQL-
              transaction has occurred, then any changes to SQL-data or
              schemas that were made by the current SQL-transaction are
              canceled and an exception condition 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 are made 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

Wed, 23 Feb 2000 09:32:10 -0500
sszabo@bigpanda.com
Re: [HACKERS] TRANSACTIONS 

>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

Wed, 23 Feb 2000 10:16:06 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
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.

Thu, 24 Feb 2000 12:16:04 -0600
"Keith G. Murphy" <keithmur@mindspring.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

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

Thu, 24 Feb 2000 14:16:05 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
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.


Fri, 25 Feb 2000 14:41:32 +0800
Lincoln Yeoh <lylyeoh@mecomb.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

>>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

Fri, 25 Feb 2000 14:26:48 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
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?

Fri, 25 Feb 2000 14:18:24 -0600
"Keith G. Murphy" <keithmur@mindspring.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS


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? 


Fri, 25 Feb 2000 14:49:19 -0600 (CST)
<kaiq@realtyideas.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

>   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

Fri, 25 Feb 2000 16:58:42 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

>   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

Fri, 25 Feb 2000 17:24:57 -0500
Karl DeBisschop <kdebisschop@range.infoplease.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS


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?


Fri, 25 Feb 2000 17:40:09 -0600 (CST)
<kaiq@realtyideas.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
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!

Mon, 28 Feb 2000 10:44:29 -0600
"Keith G. Murphy" <keithmur@mindspring.com>
Re: [GENERAL] Re: [HACKERS] TRANSACTIONS