Enterprise Rails

Refresh and Invalidation Functions

The next piece of the puzzle is the refresh function. It takes as its argument the primary key of the materialized view. In this case, that key corresponds to the primary key of the movie_showtimes table. Whenever we detect that a row in our view is invalid, we run the refresh function on that row.

Example 12-4 shows our first pass at a refresh function. It accepts an integer parameter, the primary key of the materialized view. First, it deletes the old row keyed on that id. Then, it reselects the row with the same id from the unmaterialized view—which is real time and thus guaranteed to be accurate—and inserts it back into the materialized view. It also replaces the values in the dirty and expiry columns.

Example 12-4. A simple refresh function for a materialized view

create or replace function movie_showtimes_refresh_row(
  id integer
) returns void
security definer
language 'plpgsql' as $$
begin
  delete
    from movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry ms
   where ms.id = id;
  insert into movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry
  select *, false, null
    from movie_showtimes_with_current_and_sold_out_unmaterialized ms
   where ms.id = id;
end
$$;

Remember that the materialized view is just a table. You can modify it, thus invalidating the contents, and then run the refresh function on the modified rows to test that it sets them back to the correct values. Example 12-5 shows just that. We first find the movie name ...