Triggers, again.. ;-)
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
Import Notes
Reply to msg id not found: 20050219235509.D233A5AF7C4@svr4.postgresql.orgReference msg id not found: 20050219235509.D233A5AF7C4@svr4.postgresql.org
Greg wrote:
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?
This is exactly what I do to avoid matview bulk-update performance issues.
--Phil.
Import Notes
Resolved by subject fallback
Greg wrote:
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?
This is exactly what I do to avoid matview bulk-update performance issues.
--Phil.
Hello,
Are there any issues I should know about with this method?.... Will
other transactions be able to take place during this?... Any blocking
issues?..... I've never attempted anything like this and it seems sort-of
scary to me (at the very least, applying an awfully big hammer to the
problem).
Thanks for your help!
- Greg
Import Notes
Reply to msg id not found: 20050220220234.AFA9B5AFA11@svr4.postgresql.orgReference msg id not found: 20050220220234.AFA9B5AFA11@svr4.postgresql.org | Resolved by subject fallback
Greg asked:
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 replied:
This is exactly what I do to avoid matview bulk-update
performance issues.
Greg then asked:
Are there any issues I should know about with this method?.... Will
other transactions be able to take place during this?... Any blocking
issues?..... I've never attempted anything like this and it seems
sort-of scary to me (at the very least, applying an awfully big hammer
to the problem).
I am not an expert, but I use this technique. Maybe other users will
have some observations. But as I perceive it, the triggers currently in
force are recorded in a (system) table somewhere and that table has the
same well-behaved transactional semantics as other tables. So, as far
as other transactions are concerned, the triggers are unchanged and this
is entirely safe. My experience suggests that it is not inefficient.
As for locking, my guess is that another transaction that was also
trying to create or drop triggers could block especially if it was
trying to change the same triggers, but other operations will be fine.
It seems less scary when you think of metadata as just being the content
of more tables, rather than something special.
Hopefully someone will correct me if it is worse than this!
--Phil.
Import Notes
Resolved by subject fallback
Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
It seems less scary when you think of metadata as just being the content
of more tables, rather than something special.
PG does just fine with handling metadata changes transactionally.
However, most operations that affect a table's schema at all will take
an exclusive lock on the table, thereby blocking out other operations
on the table until the schema-altering operation commits. This could be
pretty annoying if you have lots of concurrent activity that needs to
keep going --- in particular the proposed approach would lock out access
to the underlying table for as long as it takes to update the
materialized view, since the DROP TRIGGER would take that exclusive lock
and it'd be held till end of transaction. If that's OK then there's
nothing wrong with doing it that way.
regards, tom lane
Tom Lane wrote:
Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
It seems less scary when you think of metadata as just being the content
of more tables, rather than something special.PG does just fine with handling metadata changes transactionally.
However, most operations that affect a table's schema at all will take
an exclusive lock on the table, thereby blocking out other operations
on the table until the schema-altering operation commits. This could be
pretty annoying if you have lots of concurrent activity that needs to
keep going --- in particular the proposed approach would lock out access
to the underlying table for as long as it takes to update the
materialized view, since the DROP TRIGGER would take that exclusive lock
and it'd be held till end of transaction. If that's OK then there's
nothing wrong with doing it that way.
Hi Tom,
I was hoping that my positive-sounding message would flush out any
problems...
I would understand this if I were doing an "ALTER TABLE", for example.
But does adding or removing a trigger really count as "schema-altering"?
--Phil.
Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
I would understand this if I were doing an "ALTER TABLE", for example.
But does adding or removing a trigger really count as "schema-altering"?
[ shrug... ] Hard to say. Probably depends a lot on what the trigger
does. I suppose we could at least reduce the lock from AccessExclusive
to Exclusive, which would allow concurrent readers (since SELECT by
definition doesn't fire any triggers).
No one's really gone through and taken a hard look at whether every
single DDL operation needs the maximum lock ...
regards, tom lane
Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
I would understand this if I were doing an "ALTER TABLE", for example.
But does adding or removing a trigger really count as "schema-altering"?[ shrug... ] Hard to say. Probably depends a lot on what the trigger
does. I suppose we could at least reduce the lock from AccessExclusive
to Exclusive, which would allow concurrent readers (since SELECT by
definition doesn't fire any triggers).No one's really gone through and taken a hard look at whether every
single DDL operation needs the maximum lock ...regards, tom lane
FYI, I did experience locking issues (my attempt to drop the trigger
resulted in other sessions blocking)...
In any event, I found a solution which at first seemed stupid, but works
so great I thought I'd share it...
All I did was added an extra column to my table "batch_process". Then in
the trigger do something like:
IF NEW.batch_process THEN
NEW.batch_process := NULL;
RETURN NULL;
END IF;
.. whatever the rest of transaction is
Then when doing an insert, just:
INSERT INTO table (..., batch_process) VALUES (..., 't') when you want the
trigger not to fire...
What do you guys think?.. Is this dumb?....
- Greg
Import Notes
Reply to msg id not found: 20050222201836.CD63C5AF8B7@svr4.postgresql.orgReference msg id not found: 20050222201836.CD63C5AF8B7@svr4.postgresql.org | Resolved by subject fallback