[OT] Re: installing Oracle on Debian AMD64

[Javier Bertoli <javier@brtl.com.ar>]

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)