Refresh a materialized view automatically using a rule or notify

by

You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2.

create or replace function refresh_mat_view()
returns trigger language plpgsql
as $$
begin
    refresh materialized view mat_view;
    return null;
end $$;

create trigger refresh_mat_view
after insert or update or delete or truncate
on table1 for each statement 
execute procedure refresh_mat_view();

create trigger refresh_mat_view
after insert or update or delete or truncate
on table2 for each statement 
execute procedure refresh_mat_view();

In this way your materialized view is always up to date. This simple solution might be hard to accept with frequent inserts/updates and sporadic selects.
In your case (seldom changes about twice a day) it ideally fits your needs.

To realize deferred refresh of a materialized view you need one of the following features:

  • asynchronous trigger
  • trigger before select
  • rule on select before

Postgres has none of them, so it seems that there is no clear postgres solution.

Taking this into account I would consider a wrapper function for selects on mat_view, e.g.

CREATE OR REPLACE FUNCTION select_from_mat_view(where_clause text)
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY EXECUTE FORMAT ('SELECT * FROM mat_view %s', where_clause);
END;
$body$ LANGUAGE plpgsql;

If it is acceptable in practice depends on particulars I do not know about.

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Passing user id to PostgreSQL triggers
  3. How to convert primary key from integer to serial?
  4. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  5. Is there a way to disable function overloading in Postgres
  6. Why can I create a table with PRIMARY KEY on a nullable column?
  7. Dynamic pivot query using PostgreSQL 9.3
  8. Postgresql insert trigger to set value
  9. ERROR: unterminated quoted string at or near
  10. Is there a way to disable updates/deletes but still allow triggers to perform them?
  11. How can I ensure that a materialized view is always up to date?
  12. PostgreSQL – set a default cell value according to another cell value
  13. How do I temporarily disable triggers in PostgreSQL?
  14. How to reset postgres’ primary key sequence when it falls out of sync?
  15. How to import CSV file data into a PostgreSQL table
  16. Calculating and saving space in PostgreSQL
  17. The forgotten assignment operator “=” and the commonplace “:=”
  18. Equivalent to unpivot() in PostgreSQL
  19. Do nullable columns occupy additional space in PostgreSQL?
  20. How do I efficiently select the previous non-null value?
  21. Copy a few of the columns of a csv file into a table
  22. Postgres query to check a string is a number
  23. Using pyspark to connect to PostgreSQL
  24. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  25. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  26. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  27. Update timestamp when row is updated in PostgreSQL
  28. How to add a conditional unique index on PostgreSQL
  29. Select from a table variable
  30. Postgres default sort by id – worldship

Leave a Comment