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

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();