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

I don't like the locking... take a look at Ex 37-1 at the end of
http://lnk.nu/postgresql.org/fhe.html for a better way (though, the
comment below about going into an infinite loop is a good observantion,
but I think perhaps after some number of fast tries it should start
putting a sleep in the loop, rather than just arbitrarily bombing after
10 tries.

Also, I remember discussion on -performance about this from folks using
it in the real world... the problem they ran into is that doing the
updates in the cache/mview table directly bloated it too much... they
found it was better to just insert changes into an interim table, and
then periodically batch-process that table.

On Wed, Aug 15, 2007 at 08:01:24AM +0200, Pavel Stehule wrote:
> 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();
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)