Search Postgresql Archives

Re: [PERFORM] Incr/Decr Integer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux