Page History

Invalidating results cache entries

One issue with saving query results in a cache is that over time the entries in the results cache can become stale, if the underlying tables from the query are updated or dropped. A solution for this is to have a mechanism to invalidate the entries saved in the results cache. Some possible mechanisms for this:

1. Allow entries in the results cache to be valid for only a configured length of time. This is a simpler implementation which does not have to rely having to detect when the underlying tables of a cached query result have been changed. The disadvantage is that when the underlying tables are changed, any stale results would be served by the results cache until the cached result expires.

2. Add a command to clear the results cache. This can give the user a chance to prevent stale results from being served by the cache if they know that a table has been updated. Disadvantage is this requires manual intervention by the user to prevent stale results from being served.

3. Expire results cache entries if there are updates to the underlying tables. It may be possible to to reuse the same mechanism that Materialized Views use to determine if any of the underlying tables of a cached query have been modified.