instead of triggers refreshing materialized views
От | kovert@omniscient.com |
---|---|
Тема | instead of triggers refreshing materialized views |
Дата | |
Msg-id | 201604011706.u31H6TJg022343@guinness.omniscient.com обсуждение исходный текст |
Список | pgsql-general |
Hi There, This is with postgresql 9.5. For a combination of compatibility/speed issues, I have an updatable view that selects from a bunch of tables, views and a materialized view. I would really like updates to the base table (or the updatabale view) to somehow trigger a refresh of the materialized view. When I do this via trigger, I get something like: psql:mvtest.sql:30: ERROR: cannot REFRESH MATERIALIZED VIEW "bar" because it is being used by active queries in this session I get what's happening but I am wondering if there is some way I can accomplish what I'm after without either going to a pgnotify-like solution with external maintenance of the view or just abandoning the materialized view and having a table that acts like a materialized view. The below code is a greatly simplified version of what I am trying to do that illustrates the issue. help? thanks, -Todd ---<snip>--- create table foo ( id serial, primary key (id), thing text ); create function upd_foo() returns trigger as $$ begin refresh materialized view bar; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER trigger_upd_foo AFTER UPDATE ON foo EXECUTE PROCEDURE upd_foo(); create materialized view bar AS select * from foo; create view baz AS select * from bar; create function upd_baz() returns trigger AS $$ BEGIN UPDATE foo set thing = NEW.thing, id = NEW.id WHERE id = OLD.id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER trigger_upd_baz INSTEAD OF UPDATE ON baz FOR EACH ROW EXECUTE PROCEDURE upd_baz(); insert into foo (id, thing) values (1, 'test'); refresh materialized view bar; update baz set thing = 'test2' where id = 1;
В списке pgsql-general по дате отправления: