On Mon, 31 Oct 2005, in a new attempt to enlighten us, Adam Skutt wrote: AS> Sven Mueller wrote: [etc., etc.]
Hi, Leaving the all-times classical "My RDBMS is better than yours" war aside (hey!, I have my preferences too ;)... Correct me if I'm wrong, but... AS> Consider the simple case of a forum, and a column that indicates posts per AS> thread. That must be updated on every post, and the code might look AS> something like this (psuedo code): AS> BEGIN AS> VAR pc; AS> INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text'); AS> SELECT INTO pc post_count FROM threads WHERE tid = 22; AS> UPDATE threads SET post_count = pc+1 WHERE tid=22; AS> COMMIT; [etc., etc.] AS> But they say nothing about other transactions. As such, it's possible for AS> two transactions to run at the same time, see the same value of pc, and AS> update it to the same value. That's a bug. If you need to update a value in threads.post_count, that NEEDS to take in consideration what OTHER transactions do on table posts, then that code shouldn't be inside the transaction. I think that's wrong, because transactions are there to provide, among other things, "a snapshot" of the DB to an atomic operation. And as such, you are not supposed to know what other transactions are doing at the same time that yours. If threads.post_count needs to be updated CONSIDERING what ALL the transactions on table posts are doing, I think you should be using a POST INSERT TRIGGER on posts, that takes care of the issue of updating threads.post_count. Assuming the first insert column of posts is named tid (you didn't gave the column names), something like should work //---------------- CREATE TRIGGER updateThreads AFTER INSERT ON posts FOR EACH ROW EXECUTE PROCEDURE updateThreads(); //---------------- CREATE FUNCTION updateThreads() RETURNS "trigger" AS ' DECLARE pc threads.post_count%TYPE; BEGIN SELECT count(*) +1 FROM threads WHERE tid = NEW.tid INTO pc; UPDATE threads SET post_count = pc WHERE tid=NEW.tid; RETURN NEW; END; ' LANGUAGE plpgsql; //---------------- Then, you don't even need a transaction ;) INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text'); will fire the post insert trigger if the insert operation on post was succesful, and threads.post_count will be updated to the correct value. As I said, correct me if I'm wrong. Just don't shout me :)))) Regards Javier -- "It is only as we develop others that we permanently succeed." (Harvey S. Firestone) -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]