Re: [OT] Re: installing Oracle on Debian AMD64

[Adam Skutt]

Javier Bertoli wrote:
	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.
No, that's not true.  We still want our operation to be performed all at 
once, or not at all..  That's what a transaction does.



 I think that's wrong, because
transactions are there to provide, among other things, "a snapshot" of 
the DB to an atomic operation.

That is correct.

 And as such, you are not supposed to know

what other transactions are doing at the same time that yours.
No, that's not true.  That's only true if you're running in isolation 
level SERIALIZED.  In any other transaction level, that's not a safe 
assumption to make.


It's just a reality of concurrent processing.

You're attempting to make the argument because two threads of execution 
are making an update against a "snapshot" of the the same set of data, 
the left hand doesn't need to know what the right hand is doing.


That's only true iff either of these assumptions are true:
1. The threads don't really run concurrently.
2. The snapshots are never combined back into the same datasource.

Neither is normally true for a SQL RDMBS.  However, the fact neither is 
true doesn't allow you to turn around and conclude that transactions are 
evil and/or unnecessary here.  In fact, you didn't even attempt to, just 
stated that they are, so I really think you're actually in agreement 
with me, you just may not realize it. ;)


If the update to the postcount fails, I still want the inserted post 
removed.  I still want an atomic context, I just have to be aware of the 
fact other atomic contexts are accessing the data in a parallel fashion.


That doesn't mean I shouldn't take the context away.



	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.
Yes, I probably would.  That wasn't the point of the example, and not 
every case where such an update is being done can be resolved by 
triggers, nor can a trigger be always used: perhaps the update is needed 
within the scope of the transaction, then a trigger is completely 
inappropriate.


Adam


--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]

[OT] Re: installing Oracle on Debian AMD64

[Javier Bertoli]

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]