Refresh a materialized view automatically using a rule or notify
materialized-views, postgresql, postgresql-9.3, triggers
Solution
PostgreSQL 9.4 added `REFRESH CONCURRENTLY` to Materialized Views.
This may be what you're looking for when you describe trying to setup an asynchronous update of the materialized view.
Users selecting from the materialized view will see incorrect data until the refresh finishes, but in many scenarios that use a materialized view, this is an acceptable tradeoff.
Use a statement level trigger that watches the underlying tables for any changes and then refreshes the materialized view concurrently.
Problem
I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly. Here is what I was thinking about so far: There is a materialized view `mat_view` which gets its data from tables `table1` and `table2` using some join statement. Whenever something in `table1` or `table2` changes, I already have a trigger which updates a little configuration table `config` consisting of ``` table_name | mat_view_name | need_update -----------+---------------+------------ table1 | mat_view | TRUE/FALSE table2 | mat_view | TRUE/FALSE ``` So if anything in `table1` changes (there's a trigger on UPDATE and on DELETE for every statement), the field `need_update` in the first row is set to `TRUE`. The same goes for `table2` and the second row. Obviously, if `need_update` is TRUE, then the materialized view must be refreshed. UPDATE: Since materialized views do not support rules (as @pozs mentioned in a comment below), I would go one step further. I'd create a dummy view `v_mat_view` with the definition "`SELECT * FROM mat_view`". When the user does a SELECT on this view, I need to create a rule ON SELECT which does the following: - check whether `mat_view` should be updated (`SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE`) - reset the `need_update` flag with `UPDATE config SET need_update=FALSE where mat_view_name='mat_view'` - `REFRESH MATERIALIZED VIEW mat_view` - and at last do the original SELECT statement but with `mat_view` as the target. UPDATE2: I tried creating the steps above: Create a function that handles the four points mentioned above: ``` CREATE OR REPLACE FUNCTION mat_view_selector() RETURNS SETOF mat_view AS $body$ BEGIN -- here is checking whether to refresh the mat_view -- then return the select: RETURN QUERY SELECT * FROM mat_view; END; $body$ LANGUAGE plpgsql; ``` Create the view `v_mat_view` which really selects from the function `mat_view_selector`: ``` CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1; DELETE FROM v_mat_view; CREATE RULE "_RETURN" AS ON SELECT TO v_mat_view DO INSTEAD SELECT * FROM mat_view_selector(); -- this also converts the empty table 'v_mat_view' into a view. ``` The result is unsatisfying: ``` # explain analyze select field1 from v_mat_view where field2 = 44; QUERY PLAN Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4) (actual time=15.457..18.048 rows=1 loops=1) Filter: (field2 = 44) Rows Removed by Filter: 20021 Total runtime: 31.753 ms ``` in comparison to selecting from the mat_view itself: ``` # explain analyze select field1 from mat_view where field2 = 44; QUERY PLAN Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: (field2 = 44) Total runtime: 0.036 ms ``` So essentially it DOES work, but performance might be an issue. Anyone have better ideas? If not, then I would have to implement it somehow in the application logic or worse: run a simple cronjob that runs every minute or so.