Re: [PERFORM] Incr/Decr Integer

On Thursday 16 July 2009 23:20:34 William Scott Jordan wrote:
> 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!
You need to make sure *all* your locking access happens in the same order.
Then you will possibly have one transaction waiting for the other, but not
deadlock:

The formerly described Scenario now works:

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 = 1;
Wait.

Session 1:
UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2;
Fine

Session 2:
Still waiting

Session 1:
commit

Session 2:
waiting ends.

UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2;
commit;

Sensible? Works?

Andres