Enterprise Rails

The Target Table

A materialized view is created by taking an initial snapshot of the data in the unmaterialized view. Later we’ll add triggers to monitor all of the tables that make up the view and update the view whenever there is a change. In this way, our materialized view always stays up-to-date.

To create the initial materialized view, we execute the following SQL:

create table movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry as
select *,
       false as dirty,
       null::timestamp with time zone as expiry
  from movie_showtimes_with_current_and_sold_out_unmaterialized;

This statement creates a new table called movie_showtimes_with_current_and_sold_out_and_dirty_and_expiry that is prefilled with all of the data from our view. Two columns have been added: dirty and expiry. The dirty column will be used to implement deferred refresh via the invalidation trigger. The expiry column will be used to deal with special cases where we can’t count on a database event to trigger a refresh. How to use both of these columns will be explained in detail, but for now you can ignore them and think of the target table as a plain old table that happens to contain the result of our view. Example 12-3 shows the table described from a psql prompt.

Example 12-3. The physical table definition of our materialized view

movies_development=# \d movie_showtimes_with_current_and_sold_out_with_dirty_and_expiry Table "public.movie_showtimes_with_current_and_sold_out" Column | Type | Modifiers -------------------------+--------------------------+----------- ...