CODEWITHSUNDEEP
postgresqlmaterialized-views
guerda
guerda

Reputation: 24049

How to monitor refreshing of materialized views in PostgreSQL?

We have automated refresh mechanisms for our materialized views in PostgreSQL. If data issues occur and the refresh fails, we would like to get notified about this.

Is there an option to monitor the refresh logs or have a direct callback in an error case?

Upvotes: 0

Views: 1609

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

You could use an event trigger to log the event in a table:

CREATE TABLE log (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   refresh_time timestamp with time zone DEFAULT current_timestamp NOT NULL,
   view_name text NOT NULL
);

CREATE FUNCTION log_refresh() RETURNS event_trigger
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO log (view_name)
   SELECT objid::regclass::text
   FROM pg_event_trigger_ddl_commands()
   WHERE classid = 'pg_class'::regclass
     AND object_type = 'materialized view';
END;$$;

CREATE EVENT TRIGGER log_refresh ON ddl_command_end
   WHEN TAG IN ('REFRESH MATERIALIZED VIEW')
   EXECUTE FUNCTION log_refresh();

Instead of logging the event, you could do other things, like NOTIFY a listener.

Upvotes: 1

Related Questions