In trying to explain versioning to other data modelers and DBAs, we have found that many of them are worried about the cascade update implications for versioned tables. In general, the concern is that if a new version of an object is inserted into a version table, then all foreign key references to the version it replaced must be updated to point to the new version. Furthermore, if those updates take place in tables which are themselves versioned tables, then those updates create additional new versions, and all foreign keys that point to them must also be updated. And so a single versioned update can cascade out to affect many other versioned tables, ending only when a cascade chain reaches either a) a non-versioned table or b) a versioned table which does not itself contain any foreign keys.
These concerned modelers and DBAs correctly point out that this cascade effect can make versioned updates very expensive. Furthermore, if there are any circular references, the cascade will become an infinite loop. For example, consider this pair of tables: a Client Version table and our Policy Version table. The Policy Version table contains a foreign key to the Client Version table, and each row in the Client Version table contains a count of the total number of versioned updates across all policies for that client. The infinite loop happens like this:
- A new version of a policy is inserted. Call the old version P12 and the new version P13.
- This triggers code that updates the policy version count in the related client, resulting in a new version of that client. Call the old version C25 and the new version C26.
- At this point, we must cascade update the policy version. P13's foreign key still points to client version C25, and it must be updated to point to C26. This creates a new version of that policy, P14.
- But now the trigger executes once again, updating the policy version count in the related client. This loops us back to step 2, where we update that count, creating client version C27.
- But once again, we must cascade update the policy version. P14's client foreign key still points to C26. This update creates a new version of that policy, whose client foreign key points to C27. This new version of the policy is P15.
- And so on, and so on, to either the last syllable of recorded time, or to a frustrated machine operator who cancels the process.
Also, notice that so far, we have talked only about cascade updates. There are also, of course, cascade deletes. As we will see later on, the semantic constraints surrounding cascade deletes in versioned tables are not identical to those for cascade deletes of non-versioned tables.
But for now, we will concentrate on the cascade update problem.
Chart of Version Patterns
Before continuing, we insert here the current chart of version patterns. References back to earlier patterns are happening pretty frequently now, and we hope this will help the reader recall what those prior version patterns are.
Figure 1: Chart of Version Patterns
Chart of This Series
Before continuing, we insert here a chart listing the installments to date in this series and a brief description of what each one is about. As this series grows longer, the back references to earlier installments will only increase, and we think that having a chart like this at hand will be helpful.
Figure 2: Chart of Installments to Date in this Series
What to Do About the Cascade Update Problem for Versioned Tables
There are only three possible responses to the versioned table cascade update problem (or to any problem). They are: a) ignore the problem, b) avoid it or c) fix it.
Ignore it. The first response is to ignore the problem. If we take this approach, it means that in many if not most cases, a single versioned update will trigger any number of additional versioned updates. Perhaps the attitude about multiple updates is: "DASD is cheap; CPU cycles are cheap. So just let it happen." But then what about the possibility of an infinite loop? Perhaps the attitude about infinite loops is: "Design your databases so there aren't any loops."
In some specific cases, this may be an acceptable response. But if our objective is to develop an enterprise solution (which, indeed, is our objective), then this response just won't do. We need a response that works in all possible cases, not just in special individual cases. We can't just ignore the issue of cascade updates in versioned tables.
Avoid it. The second response is to avoid the problem. This means to turn off referential integrity checking in the DBMS (which would stop the loop in the client to policy direction). If we can accept cascade updating, and are only concerned to avoid the infinite loop, an alternative is to turn off the developer-written code which updates counts (which would stop the loop in the policy to client direction).
Avoiding the problem is not as cavalier as it sounds. Standards committees are at loggerheads over how to introduce temporal management functions into relational DBMSs, and as a result, DBMS vendors are stymied. Consequently, there is no adequate support for temporal database management in current relational DBMSs. So we shouldn't be surprised that there is also no DBMS-implemented referential integrity that works for temporal (versioned) tables. Until there is such support, avoiding the problem by turning off referential integrity may be a very reasonable response.
