2007/8/15, Erik Jones <erik@xxxxxxxxxx>: > On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote: > > > I write sample about triggers and i have question. is my solution > > correct and exists better solution? > > > > Regards > > Pavel Stehule > > > > DROP SCHEMA safecache CASCADE; > > > > CREATE SCHEMA safecache; > > > > CREATE TABLE safecache.source_tbl(category int, int_value int); > > > > CREATE TABLE safecache.cache(category int, sum_val int); > > > > CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce() > > RETURNS trigger AS > > $$ > > BEGIN > > IF TG_OP = 'INSERT' THEN > > -- row cannot exists in cache -- complication > > -- I would to finish these transaction without conflict > > IF NOT EXISTS(SELECT category > > FROM safecache.cache > > WHERE category = NEW.category) THEN > > LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE; > > -- I have to repeat test > > IF NOT EXISTS(SELECT category > > FROM safecache.cache > > WHERE category = NEW.category) THEN > > INSERT INTO safecache.cache > > VALUES(NEW.category, NEW.int_value); > > END IF; > > ELSE > > -- simple > > UPDATE safecache.cache > > SET sum_val = sum_val + NEW.int_value > > WHERE category = NEW.category; > > END IF; > > ELSEIF TG_OP = 'UPDATE' THEN > > -- if category is without change simple > > IF NEW.category = OLD.category THEN > > UPDATE safecache.cache > > SET sum_val = sum_val + (NEW.int_value - OLD.int_value) > > WHERE category = OLD.category; > > ELSE > > -- old category has to exists > > UPDATE safecache.cache > > SET sum_val = sum_val - OLD.int_value > > WHERE category = OLD.category; > > -- new category is maybe problem > > IF NOT EXISTS(SELECT category > > FROM safecache.cache > > WHERE category = NEW.category) THEN > > LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE; > > -- I have to repeat test > > IF NOT EXISTS(SELECT category > > FROM safecache.cache > > WHERE category = NEW.category) THEN > > INSERT INTO safecache.cache > > VALUES(NEW.category, NEW.int_value); > > END IF; > > ELSE > > -- simple, new category exists > > UPDATE safecache.cache > > SET sum_val = sum_val + OLD.int_value > > WHERE category = NEW.category; > > END IF; > > END IF; > > ELSE -- DELETE > > -- value have to exist in cache, simple > > UPDATE safecache.cache > > SET sum_val = sum_val - OLD.int_value > > WHERE category = OLD.category; > > END IF; > > RETURN NEW; > > END > > $$ LANGUAGE plpgsql; > > > > CREATE TRIGGER actualise_cache > > AFTER INSERT OR UPDATE OR DELETE > > ON safecache.source_tbl > > FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce(); > > From what I can tell from your example it's "correct" and should > work under light loads. However, if that trigger will fire a lot, > you might see those updates "stacking" due to the necessary locking > (both your explicit locks and those take out by the updates). What > I've done in that case (this is actually a pretty standard setup), is > to have the trigger just make inserts into another table of the > category that needs to be updated and by how much. The you have some > other (probably user-land) process periodically sweep that table, > aggregate the updates to the cache table, then delete the interim > entries just processed. Oh yeah, you could simplify that function a > lot by simply initializing your cache table with a row for each > category with sum_val = 0. Then it's all updates and you don't need > those locks to determine if the category exists there. I know it, but I don't know all posible category numbers, and others. I sent sample with minimum (one) pk attributies. > > Erik Jones I have 98% of SELECTs and 2% of INSERTs and UPDATE ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
