Re: [GENERAL] 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(at)alert(dot)infoplease(dot)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