by kiru (09.05.2023)

PostgreSQL: Does Removing Materialized View Remove Triggers?

I did not find an easy answer via Google for the question in title, so I did a small experiment. In short: NO, you have to remove the triggers by hand. Read further to see how.

Setup materialized view

Let’s start with a simple table setup

CREATE TABLE nobilis(
  now TIMESTAMP
);

INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);
INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);
INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);

SELECT * FROM nobilis;

At this point, have we a taable called nobilis with three vlaues:

2023-05-09 09:52:32.367404
2023-05-09 09:52:33.940290
2023-05-09 09:52:34.913388

We need a materialized view, which juts selects all elements:

CREATE MATERIALIZED VIEW mv_nobilis 
  AS SELECT now FROM nobilis ;

Since the view is not updated automatically in PostgreSQL, we have configure a trigger on the nobilis table to do so:

CREATE OR REPLACE FUNCTION mv_refresh()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_nobilis;
    RETURN NULL;
END;
$$;

CREATE TRIGGER refresh_mv_nobilis
    AFTER UPDATE OR INSERT OR DELETE ON nobilis
    FOR EACH STATEMENT EXECUTE PROCEDURE mv_refresh();
``

In summary, we aded a function which allows us to updated the materialized view. 
The function is called for each statement which involves the `nobilis` table.


To verify that everything works fine, we will add two new entries to our original table: 

```sql
INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);
INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);
SELECT * FROM mv_nobilis;

If everything works fine, you should see a refrshed view:

2023-05-09 09:52:32.367404
2023-05-09 09:52:33.940290
2023-05-09 09:52:34.913388
2023-05-09 09:57:15.965973
2023-05-09 09:57:19.764480

Try to delete the materialied view

We want to know if removing the view removes the triggers automatically:

DROP MATERIALIZED VIEW mv_nobilis;

This works fine, without any additional errors. What if we insert a new entry:


INSERT INTO nobilis VALUES (CURRENT_TIMESTAMP);

Finally, we get an error:

[2023-05-09 12:04:36] [42P01] ERROR: relation "mv_now" does not exist
[2023-05-09 12:04:36] Where: SQL statement "REFRESH MATERIALIZED VIEW mv_now"
[2023-05-09 12:04:36] PL/pgSQL function mv_refresh() line 3 at SQL statement

Remove View and triggers

In order to fix this, we have to remove the triggers by hand:

DROP TRIGGER refresh_mv_nobilis ON nobilis;

We also don’t need the function anymore:

DROP FUNCTION mv_refresh()

In summary, we can answer our initial question with NO!