Timing-Based System-Period Temporal Table Timestamp Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Temporal databases face discrepancies between data creation time and query results due to the lack of precise timestamp management, leading to odd retrieval of uninputted data in system-period temporal tables.
Innovation Solution
A timing-based system-period temporal table is introduced, featuring a transaction management table with unique identifiers and timestamps for each transaction's first operation, last operation, and commit, along with a system-period temporal table using row identifiers and dummy transaction identifiers for accurate data management and querying.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the row begin column is set to the system clock value at the time of the first data change statement, then the timestamp reflects the data change time, but discrepancies occur between data creation time and query results causing retrieval of data not yet inputted
Solution Approach 1:
The patent introduces an intermediary mechanism (transaction management table with commit timestamps) between the data change operation and the temporal table row begin column. Instead of directly using the system clock at data change time, the row begin column now reflects the commit timestamp from the transaction management table, which serves as a reliable mediator ensuring data is only visible after successful commit.
Solution Approach 2:
The patent implements preliminary recording of transaction metadata (including commit timestamps) in a transaction management table before finalizing the temporal table entries. This preliminary action ensures that the commit timestamp is captured and verified before it is used to set the row begin column, preventing premature data visibility.
2Ease of manufacture
If the system clock value is used for row begin column, then timestamp recording is simple, but data consistency issues arise when transactions are not yet committed
Solution Approach 1:
The patent segments the timestamp recording process into two distinct parts: (1) transaction metadata recording in the transaction management table with commit timestamps, and (2) temporal table row begin column setting using those commit timestamps. This segmentation separates the simplicity of system clock recording from the complexity of consistency assurance.
Solution Approach 2:
The transaction management table acts as an intermediary layer between the simple system clock and the temporal table. It captures the commit timestamp as a reliable marker, mediating between the simplicity of time recording and the requirement for data consistency.
3Device complexity
If no transaction management table is used, then the system-period temporal table structure is simple, but precise timestamp management for querying is not achieved
Solution Approach 1:
The patent adds another dimension to the system by introducing a transaction management table alongside the system-period temporal table. This additional dimension provides the necessary transaction metadata and commit timestamps without altering the core temporal table structure, enabling precise query timestamp management.
Data Source
AI summary
A method of querying data including: providing a transaction management table; providing a system-period temporal table; assigning a unique identifier to a transaction; performing a first operation at a first timestamp; writing a first row identifier in the system-period temporal table; writing in the row corresponding to the first row identifier the unique identifier of the first operation in the row begin entry of the system-period temporal table; writing in the row corresponding to the first row identifier a dummy transaction identifier corresponding to a maximum timestamp value of the database system in the row end entry of the system-period temporal table; committing the transaction at a commit timestamp such that the commit timestamp is later than the first timestamp; inserting a row in the transaction management table and inserting values for the unique transaction identifier, the first timestamp and the commit timestamp in the transaction management table.