Re: [PERFORM] Incr/Decr Integer

Hi Andrew,

That's a very good guess.  We are in fact updating this table multiple
times within the same triggered function, which is being called on an
INSERT.  Essentially, we're using this to keep a running total of the
number of rows being held in another table.  The function we're using
currently looks something like this:

---
CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger"
     AS $$
BEGIN;
    UPDATE the_table
    SET first_column = first_column + 1
    WHERE first_id = NEW.first_id ;

    UPDATE the_table
    SET second_column = second_column + 1
    WHERE second_id = NEW.second_id ;

    UPDATE the_table
    SET third_column = third_column + 1
    WHERE third_id = NEW.third_id ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
---

For something like this, would it make more sense to break out the three
different parts into three different functions, each being triggered on
INSERT?  Or would all three functions still be considered a single
transaction, since they're all being called from the same insert?

Any suggestions would be appreciated!

-William


Andres Freund wrote:
> On Thursday 16 July 2009 19:56:47 William Scott Jordan wrote:
>> Hey all!
>>
>> Is there a better way to increase or decrease the value of an integer
>> than doing something like:
>>
>> ---
>> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 123 ;
>> ---
>>
>> We seem to be getting a lot of deadlocks using this method under heavy
>> load.  Just wondering if we should be doing something different.
> Is this the only statement in your transaction? Or are you issuing multiple
> such update statements in one transactions?
> I am quite sure its not the increment of that value causing the problem.
>
> If you issue multiple such statements you have to be carefull. Example:
>
> Session 1:
> BEGIN;
> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1;
>
> Session 2:
> BEGIN
> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2;
>
> Fine so far.
>
> Session 1:
> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2 ;
> Waits for lock.
>
> Session 2:
> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1;
> Deadlock.
>
>
> Andres
>
> PS: Moved to pgsql-general, seems more appropriate