From: | "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Triggers, again.. ;-) |
Date: | 2005-02-20 02:18:37 |
Message-ID: | 20050220021837.19468@mail.net-virtual.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-02-20 05:04:56 | Re: quoting internal variable names |
Previous Message | Doug McNaught | 2005-02-20 01:29:01 | Re: PGSQL 8.0.1 Win 2K Installation Problem |