I recently had to help add function to existing code to enable results to be published in a more real time fashion. The change involved adding a piece of code to perform a database checkpoint when the code completed. The checkpoint was to ensure changes propagated to a Data Guard standby database the was opened read-only.
The third-party vendor of the code preferred to have the checkpoint creation executed by a trigger when their code wrote to a log table. No problem, right? My only concern was that if the log table was used to store ‘failure’ messages as well as ‘success’ messages. Adding some additional code in the trigger could check for and fire only on ‘success’ messages, though. Then I heard that the log table only stores ‘success’ messages which started me wondering. Finally, I was asked to put a delay of two minutes in the trigger before the body of the trigger was executed. I started getting suspicious because there is something wrong when you have to forcibly delay the execution of code. When I asked why, I was told that the log table record was inserted at the start of the process and not the end and the delay was needed to ensure the process completed before the trigger executed the checkpoint. It then became clear why they wanted to have a solution that did not involve them modifying their code. To recap, this process goes to insert a ‘successful completion’ message as the first step of the process, then waits for two minutes before committing the insert to make sure the process completes, regardless of the process outcome.
The thinking that went into writing this code is what I refer to as ‘planning for the best’. This is the mindset that if the code is written right, it can’t possibly fail. Code writers that have been in the business for more that a few years know you cannot plan for everything, so they tend to write code with the ‘plan for the worst’ mindset. This involves writing exceptions and error handlers into the code. As such, the code is more robust, adaptable, and less prone to failure or having to add tweaks such as delays to make it work well. I almost got whiplash from all the head shaking I did on this one. Just saying.