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

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