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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
