From: | kovert(at)omniscient(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | instead of triggers refreshing materialized views |
Date: | 2016-04-01 17:06:29 |
Message-ID: | 201604011706.u31H6TJg022343@guinness.omniscient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Brahim EL ALLIOUI | 2016-04-01 17:44:37 | how to regenerate pg_control file ? |
Previous Message | Adrian Klaver | 2016-04-01 16:10:25 | Re: Please advise on this trigger function |