Database Virtual Column for Commit Time Access
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems lack an effective method to provide timing information about the last committed change to a row in a database table, limiting concurrency control, client cache invalidation, and auditing capabilities.
Innovation Solution
A technique that uses a virtual column, or pseudo-column, to temporarily store and expose logical time values representing commit times, allowing clients to query and utilize these values as if they were real columns, while the database query optimizer ignores them, enabling block-dependent or row-dependent commit time values to be retrieved and used for operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If logical time values are made accessible to database clients, then concurrency control and auditing capabilities are improved, but system complexity increases
Solution Approach 1:
The patent introduces a pseudo-column as an intermediary mechanism that bridges the gap between internal logical time values and client-accessible data structures. The pseudo-column acts as a mediator that allows clients to query commit time information without requiring direct access to internal database structures, thereby improving concurrency control capability while minimizing system complexity
Solution Approach 2:
The patent creates a copy of the logical time value and stores it in a pseudo-column that clients can access. This copying approach allows the internal logical time value structure to remain unchanged while providing client access to the timing information, thus improving reliability without significantly increasing system complexity
2Reliability
If commit time information is exposed to clients, then auditing capability is improved, but query performance deteriorates
Solution Approach 1:
The pseudo-column serves as an intermediary that allows auditing queries to access commit time information without requiring complex joins or access to internal transaction tables. This mediator approach improves auditing capability while maintaining query performance by providing direct access to timing data through the table's column structure
Solution Approach 2:
The patent merges the logical time value access with the existing table column access mechanism by implementing a pseudo-column. This combining of functions allows clients to retrieve commit time information using standard SQL queries without additional performance overhead, thus improving auditing capability while preserving query performance
3Ease of operation
If a virtual column is used to store logical time values, then ease of operation is improved, but device complexity increases
Solution Approach 1:
The pseudo-column implementation allows the database system to automatically manage the logical time value storage and retrieval without requiring complex client-side processing. The infrastructure serves itself by integrating with existing query optimization mechanisms, improving ease of operation while keeping the complexity increase minimal and centralized within the database engine
Data Source
AI summary
Processing a database statement received from a database client, which requests information about the timing of a last committed change to a row in a database table, comprises retrieving a logical time value that represents a commit time associated with the row and providing the value to the client. In one embodiment, providing the logical time value to the client is enabled by database infrastructure in which a virtual column is used to temporarily “store” the value so that the value can be manipulated as if the value resided in a column of the row. Performance of operations that involve the virtual column are processed similar to real columns, as if data was actually logically stored in the virtual column. For example, the virtual column can be used in condition clauses of database statements for concurrency control.