Hello,
I am sorry, this mail had to be send only to pgsql-general
nice a day
Pavel Stehule
---------- Forwarded message ----------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 15.8.2007 8:01
Subject: is this trigger safe and efective? - locking (caching via triiggers)
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
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();
