Postgresql – Trigger to update a materialized view once after relevant transaction

fast-refreshmaterialized-viewpostgresqlpostgresql-9.6

I'm using PostgreSQL 9.6.

I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched.

I'm trying to find a good strategy to run REFRESH MATERIALIZED VIEW post_search.

The ideal solution would be a trigger like this: at the end of (or after) any transaction that modifies posts, tags and/or posts_tags (join table), refresh the materialized view exactly once.

How could I accomplish this?

Best Answer

An OK solution using triggers

This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.

(In the future, we're thinking of using an actual table for search instead of a materialized view, and updating individual relevant rows via triggers instead of refreshing an entire matview.)

Create a function to refresh the materialized view concurrently:

  CREATE OR REPLACE FUNCTION refresh_post_search()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
  BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY post_search;
  RETURN NULL;
  END $$;

And create a trigger for each of the underlying tables:

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts_tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();
Related Question