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

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