Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

> From: "Keith G. Murphy" <keithmur(at)mindspring(dot)com>
>
> 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.

I agree

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

It is not sufficient that the statement in error has done nothing -
the postmaster in the general case cannot know what relationships
should exist between the non-key data. It is quite possible that not
having a record inserted could make the database fundamentally
unusable. Of course, in my original example and in yours, error is
trapped and the situation is (hopefully) fixed by the subsequent
update. Thus, in my post I suggested that postgres could provide some
sort of mechanism to explicitly 'sanitize' the transaction and allow
it to commit.

In otherwords, I think we are basically proposing the same thing.

> 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.)

>From what I gather, extending postgresql this way is planned anyway -
it may not happen tomorrow, but notheing in here seems like a very new
concept to the development team.

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

Yes, the table in limbo is certainly a problem/bug. But even this
bug, in my estimation, is better than allowing a transaction with an
error in it to commit without explicitly clearing the error status.
The bug is a pain in the neck, but it apparently has been fixed in
6.5.3 -- so why not upgrade, no dumps are required. But even with the
bug, it can save you from unknowingly foisting inaccurate data on your
customers which is still a good thing.

As for whether postgress should implicitly roll back, I don't think it
should - remember that the frontend, which is very likely operating in
robot mode, is still firing queries at the database. An inpmlicit
rollback means starting a new transaction. And that could lead to a
data integrity problem as well.

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