1

I have a spatial table in a Postgres database from which I create three separate materialized views (each based on specific spatial queries). I want to create trigger functions to refresh each of the materialized views on updates, inserts, and deletes.

I have created three separate functions and triggers, but the performance (as to be expected) is horrendous. If I run a single trigger on update, insert, or delete, it performs fine. Below is a sample function and trigger I am using:

CREATE OR REPLACE FUNCTION refresh_mvw_taxa_hex5km()   
RETURNS trigger 
AS $BODY$ 
  BEGIN 
    refresh materialized view mvw_taxa_hex5km; 
    return new; 
  END; 
$BODY$ LANGUAGE plpgsql; 


CREATE TRIGGER refresh_mvw_taxa_hex5km   
AFTER INSERT OR UPDATE OR DELETE ON occurrence   
FOR EACH STATEMENT   
  EXECUTE PROCEDURE refresh_mvw_taxa_hex5km();

Is there a more efficient way to do this? I considered running scheduled tasks, but I really need the refresh on changes to the table. I have read a little about "concurrently," but not sure if this is the answer.

JGH
  • 15,928
  • 4
  • 31
  • 48
Todd Fagin
  • 31
  • 1
  • Refreshing materialized view is something like `truncate table foo; insert into foo `. So when you changes every single row, you then refreshes whole data in the materialized view each time. Are you sure that you are using materialized views in the right way? – Abelisto Jun 08 '19 at 13:34
  • Well, the short answer is no, I am not sure I am using materialized views in the right way. Based on my reading, though, a materialized view is what I need for my workflow. – Todd Fagin Jun 10 '19 at 14:08
  • You may be interested in [Refresh a materialized view automatically using a rule or notify.](https://stackoverflow.com/q/23906977/1995738) – klin Jun 13 '19 at 14:07
  • Thank you, I will explore this further. – Todd Fagin Jun 14 '19 at 18:20

0 Answers0