System versioned table with transactional system time
A method may include receiving a first transaction inserting a record into a database and a second transaction deleting the record from the database. A validity period for the record may be determined based on a first commit time at which the first transaction is committed and a second commit time at which the second transaction is committed. A current table and/or a history table of a system versioned table may be updated to include the record based on the validity period of the record. One or more temporal operations may be performed based on the system versioned table. For example, a time travel operation may be performed to retrieve, based on the system versioned table, one or more records that are valid at a given point in time. Related systems and computer program products are also provided.
1. A system, comprising:
at least one data processor; and
at least one memory storing instructions which, when executed by the at least one data processor, cause operations comprising:
committing a first transaction at a first commit time and a second transaction at a second commit time, wherein the first transaction comprises inserting a record into a database and the second transaction comprises deleting the record from the database;
determining, based at least on the first commit time at which the first transaction is committed and the second commit time at which the second transaction is committed, a validity period of the record; and
updating, based at least on the validity period of the record, a current table and/or a history table of a system versioned table to include the record, wherein the record is moved from the current table to the history table upon an expiration of the validity period, wherein the record is moved from the current table to the history table upon an expiration of the validity period.
2. The system of claim 1 , wherein the validity period comprises a valid-from timestamp and a valid-to timestamp.
3. The system of claim 2 , wherein the valid-from timestamp corresponds to the first commit time of the first transaction inserting the record, and wherein the valid-to timestamp corresponds to the second commit time of the second transaction deleting the record.
4. The system of claim 1 , wherein the record is stored in the current table during the validity period of the record.
5. The system of claim 1 , wherein the record is omitted from the history table in response to the first transaction and the second transaction being a same transaction.
6. The system of claim 1 , wherein the validity period is determined based on the first commit time of the first transaction and the second commit time of the second transaction instead of a first start time of the first transaction and/or a second start time of the second transaction.
7. The system of claim 1 , wherein the operations further comprise:
performing, based at least on the system versioned table, a time travel operation by at least identifying one or more data records that are valid at a given point in time.
8. The system of claim 1 , wherein the operations further comprise:
determining, based at least on the system versioned table, a version history of the record.
9. The system of claim 1 , wherein the database comprises an in-memory column-oriented relational database.
10. A computer-implemented method, comprising:
committing a first transaction at a first commit time and a second transaction at a second commit time, wherein the first transaction comprises inserting a record into a database and the second transaction comprises deleting the record from the database;
determining, based at least on the first commit time at which the first transaction is committed and the second commit time at which the second transaction is committed, a validity period of the record; and
updating, based at least on the validity period of the record, a current table and/or a history table of a system versioned table to include the record, wherein the record is moved from the current table to the history table upon an expiration of the validity period, wherein the record is moved from the current table to the history table upon an expiration of the validity period.
11. The computer-implemented method of claim 10 , wherein the validity period comprises a valid-from timestamp and a valid-to timestamp.
12. The computer-implemented method of claim 11 , wherein the valid-from timestamp corresponds to the first commit time of the first transaction inserting the record, and wherein the valid-to timestamp corresponds to the second commit time of the second transaction deleting the record.
13. The computer-implemented method of claim 10 , wherein the record is stored in the current table during the validity period of the record.
14. The computer-implemented method of claim 10 , wherein the record is omitted from the history table in response to the first transaction and the second transaction being a same transaction.
15. The computer-implemented method of claim 10 , wherein the validity period is determined based on the first commit time of the first transaction and the second commit time of the second transaction instead of a first start time of the first transaction and/or a second start time of the second transaction.
16. The computer-implemented method of claim 10 , comprising:
performing, based at least on the system versioned table, a time travel operation by at least identifying one or more data records that are valid at a given point in time.
17. The computer-implemented method of claim 10 , comprising:
determining, based at least on the system versioned table, a version history of the record.
18. The computer-implemented method of claim 10 , wherein the database comprises an in-memory column-oriented relational database.
19. A non-transitory computer readable medium storing instructions, which when executed by at least one data processor, result in operations comprising:
committing a first transaction at a first commit time and a second transaction at a second commit time, wherein the first transaction comprises inserting a record into a database and the second transaction comprises deleting the record from the database;
determining, based at least on the first commit time at which the first transaction is committed and the second commit time at which the second transaction is committed, a validity period of the record; and
updating, based at least on the validity period of the record, a current table and/or a history table of a system versioned table to include the record, wherein the record is moved from the current table to the history table upon an expiration of the validity period, wherein the record is moved from the current table to the history table upon an expiration of the validity period.