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