Moving System Time Timestamping in MPP Active Data Warehouse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Timestamping in temporal databases, particularly in active data warehouses, is challenging due to the use of fixed transaction times that fail to accurately reflect the timing of modifications, leading to inconsistencies when dealing with multiple changes within the same transaction or across different transactions.
Innovation Solution
Implementing a method that uses the moving system time maintained by each unit of parallelism in a massively parallel processing system to timestamp rows at the time of modification, allowing for finer granularity and accurate tracking of changes within the ADW, even if modifications occur within the same transaction or across transactions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If fixed transaction time (begin time or commit time) is used for timestamping, then timestamping is simple to implement, but multiple modifications within the same transaction cannot be distinguished and timing accuracy is lost
Solution Approach 1:
The patent transitions from static fixed-time timestamping (using begin time or commit time for all modifications in a transaction) to dynamic timestamping where each modification receives a unique timestamp based on the actual time it was applied. This allows multiple modifications within the same transaction to be distinguished while maintaining implementation feasibility through the use of moving system time maintained by each unit of parallelism.
2Measurement precision
If moving system time from individual units of parallelism is used for timestamping, then modification timing accuracy is improved, but system time synchronization complexity increases
Solution Approach 1:
The patent divides the system into multiple units of parallelism, with each unit maintaining its own moving system time independently. This segmentation allows each unit to timestamp modifications accurately without requiring complex cross-unit time synchronization, as each unit operates with its own time reference while maintaining consistency through the temporal database structure.
3Reliability
If transaction commit time is used for timestamping, then all modifications appear atomic and consistent, but the ability to track individual modification timing is lost
Solution Approach 1:
The patent introduces an intermediary timestamping mechanism that captures the moving system time at the moment each modification is applied, serving as a mediator between the actual modification event and the final committed state. This preserves both the atomicity of the transaction (all modifications are committed together) and the timing information (each modification has its own timestamp), resolving the information loss problem.
Data Source
AI summary
A temporal table is present in an active data warehouse (“ADW”) on a massively parallel processing system (“MPP system”). The MPP system includes a plurality of units of parallelism. Each of the plurality of units of parallelism maintains its own moving system time separate from that of the other units of parallelism. A load-job for the ADW is accepted. The load-job includes a requested modification to an identified row. The identified row is stored in the temporal table on a first of the MPP system's units of parallelism. The requested modification is implemented on the identified row. The identified row is timestamped with the moving system time maintained by the first of the MPP system's units of parallelism at the time of the implementation of the modification.