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?