Re: is this trigger safe and efective? - locking (caching via triiggers)

2007/8/15, Erik Jones <erik(at)myemma(dot)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