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.