sql – Postgres trigger refreshing a materialized view not working after some time – Code Utility

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