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