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.