Enterprise Rails

Triggered Refreshes and Invalidations

So far we have built a snapshot of our view at a given point in time, and we have created stored procedures that can be used to invalidate and refresh rows in the materialized view snapshot. Now it’s time to build triggers that will refresh or invalidate rows automatically as changes are made to underlying tables.

In general, triggers follow these steps:

1. Determine if any change to the materialized view is necessary, and quit early if not.

2. Determine which rows, by primary key, need to be refreshed or invalidated.

3. Call the refresh or invalidate function on those primary keys.

Writing these triggers can be a tedious process because we need to account for inserts, updates, and deletes on all tables that make up the view. In this case, nearly all of our tables—six—are involved in the view in some way. With three functions per table for each insert, update, and delete, this could mean we need to write 18 trigger functions. Luckily, with some proper analysis, we can eliminate the need for more than half of these.

To facilitate this analysis, we create a reference table as shown in Table 12-1. We list each table involved in the view. For each table, we determine its relationship to the view: 1:1, 1:N, or N:1. Then, for each operation on the table, we first determine whether any action is needed at all, and if so, we choose whether we will refresh immediately, or defer the refresh by performing an invalidation operation. We’ll examine each table in turn to ...