[
I have a materialized view created and a trigger to refresh it after any update on the source table.
Everything seems to work oke, but after a while seems that the trigger stops working since the materialized view remains with stale data.
At some point after I recreated the trigger the functionality started to work again.
The postgres version used in 11.
CREATE OR REPLACE FUNCTION refresh_some_view()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
REFRESH MATERIALIZED VIEW some_view;
RETURN NEW;
END;
$$;
CREATE TRIGGER on_source_table_update
AFTER UPDATE OR INSERT OR DELETE
ON source_table
FOR EACH ROW
EXECUTE PROCEDURE refresh_some_view();
,
Use FOR EACH STATEMENT
instead of FOR EACH ROW
. This will prevent the view to be refreshed after every single row of your insert statements, which most likely isn’t what you want.
CREATE TRIGGER on_source_table_update
AFTER UPDATE OR INSERT OR DELETE ON source_table
FOR EACH STATEMENT EXECUTE PROCEDURE refresh_some_view();
You can check it in the query planner. If you use FOR EACH STATEMENT
the refresh in the function will be called only once – see number of calls at Trigger on_source_table_update
. This query plan shows what happens with an insert containing 1000 rows – see the number rows
at ProjectSet
:
QUERY PLAN
------------------------------------------------------------------------------
Insert on source_table (actual time=7.097..7.140 rows=0 loops=1)
-> ProjectSet (actual time=0.005..0.398 rows=1000 loops=1)
-> Result (actual time=0.002..0.044 rows=1 loops=1)
Planning Time: 0.264 ms
Trigger on_source_table_update: time=11.628 calls=1
Execution Time: 19.011 ms
.. but if you stick to FOR EACH ROW
things get much slower, as the trigger calls the function once per row (1000 times!):
QUERY PLAN
------------------------------------------------------------------------------
Insert on source_table (actual time=4.187..4.188 rows=0 loops=1)
-> ProjectSet (actual time=0.004..0.204 rows=1000 loops=1)
-> Result (actual time=0.002..0.002 rows=1 loops=1)
Planning Time: 0.122 ms
Trigger on_source_table_update: time=13815.499 calls=1000
Execution Time: 13820.529 ms
Demo: db<>fiddle
]