Обсуждение: is this trigger safe and efective? - locking (caching via triiggers)
Hello, I am sorry, this mail had to be send only to pgsql-general nice a day Pavel Stehule ---------- Forwarded message ---------- From: Pavel Stehule <pavel.stehule@gmail.com> Date: 15.8.2007 8:01 Subject: is this trigger safe and efective? - locking (caching via triiggers) To: PostgreSQL Hackers <pgsql-hackers@postgresql.org> Hello 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();
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. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
2007/8/15, Erik Jones <erik@myemma.com>: > 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
On Aug 15, 2007, at 11:14 AM, Pavel Stehule wrote: > 2007/8/15, Erik Jones <erik@myemma.com>: >> 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. One workaround is to make an on insert trigger that fires before this one and checks to see if this is a new category and sets up the row with value 0 in the cache table. >> >> Erik Jones > > I have 98% of SELECTs and 2% of INSERTs and UPDATE Sounds like you should be ok then and you may not need to go with the suggestions I've outlined. However, be sure to keep a close eye on pg_locks when you push that trigger into production. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com