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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
