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

On Aug 15, 2007, at 11:14 AM, Pavel Stehule wrote:

> 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.

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(at)myemma(dot)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