Triggers, again.. ;-) - Mailing list pgsql-general

Hello,

I have asked about this before, but I just haven't been able to get
anywhere with it yet.. I'm hoping someone can help me?


Here is my original function and trigger:

CREATE OR REPLACE VIEW items_category AS select count(*) AS count
,b.category,nlevel(b.category) AS level,
  subpath(b.category,0,nlevel(b.category)-1) as parent,
  b.head_title,b.cat_title,b.subcat_title FROM items a,category b
WHERE b.category @> a.category
  AND a.status = 'open'
GROUP BY b.category,b.head_title, b.cat_title, b.subcat_title
ORDER BY b.category;
-----------
CREATE OR REPLACE FUNCTION category_mv_refresh_row(ltree[]) RETURNS VOID
SECURITY DEFINER
LANGUAGE 'plpgsql' AS '
BEGIN
  DELETE FROM category_mv WHERE category @> $1;
  INSERT INTO category_mv SELECT * FROM items_category WHERE category @> $1;
  RETURN;
END
';
-----------
CREATE OR REPLACE FUNCTION update_ut() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
  IF OLD.category = NEW.category THEN
    PERFORM category_mv_refresh_row(NEW.category);
  ELSE
    PERFORM category_mv_refresh_row(OLD.category);
    PERFORM category_mv_refresh_row(NEW.category);
  END IF;
  RETURN NULL;
END
';
-----------
CREATE TRIGGER category_mv_ut AFTER UPDATE ON items
  FOR EACH ROW EXECUTE PROCEDURE update_ut();



Now what I need is a way for category_mv_refresh_row to be made optional
during the execution of update_ut, or somewhere.  I thought about
changing update_ut to something like:

CREATE OR REPLACE FUNCTION update_ut() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS '
BEGIN
  IF skip_update IS NOT NULL THEN
    IF OLD.category = NEW.category THEN
      PERFORM category_mv_refresh_row(NEW.category);
    ELSE
      PERFORM category_mv_refresh_row(OLD.category);
      PERFORM category_mv_refresh_row(NEW.category);
    END IF;
  END IF;
  RETURN NULL;
END
';


.. then somehow setting "skip_update" during my transaction.. Something like:
BEGIN
skip_update boolean := 't';
... insert rows
... update materialized view table
COMMIT;


But, I can't set skip_update like that I guess.  Does anyone have any
idea how I might go about doing this?.. The reason is, I have a function
that updates the *entire* materialized view that takes about 15 seconds
to execute, but calling category_mv_refresh_row takes about 2 seconds.
When I am inserting thousands of rows, this results in an enormous
additional load on the database server (and takes far longer to execute).

Out of curiosity, is "DROP TRIGGER" transaction safe?... I mean, could I do:

BEGIN
DROP TRIGGER category_mv_ut;
... insert rows
... update materialized view table
CREATE TRIGGER category_mv_ut AFTER UPDATE ON items
  FOR EACH ROW EXECUTE PROCEDURE update_ut();
COMMIT;

.. without other sessions being affected?

I hope I've explained this well enough!

Thanks as always!

- Greg