Low-overhead built-in timestamp column for relational database systems

Abstract

An improved automatically updated timestamp for database systems is disclosed. The automatically updated timestamp can be provided in a hidden timestamp column for a table, where the value of the timestamp column can be retrieved with a query that calls the column by name. Optionally, the timestamp column can be provided for each table in the database system to ensure its availability to applications. For cases where a timestamp for each row in a table is not desired, an automatically updated timestamp can be provided for a data page. This timestamp can be retrieved from an update timestamp recorded on disk and in the buffer pool or from a log relative byte address. Although this is a page-level timestamp, its use may be desirable for infrequently updated tables or where space on the disk and buffer pool is at a premium.

1 . A computer readable medium with program instructions for providing an automatically updated timestamp for database systems, comprising instructions for:
 providing a table in the database system, the table including a plurality of rows of data;   providing a hidden timestamp column in the table of the database system, the hidden timestamp column including a timestamp value for each row of data in the table, the timestamp value indicating a last time a corresponding row of data in the table was previously modified, wherein the hidden timestamp column does not appear in the database schema by default and exposes the timestamp value for a given row of data in the table only to a query that calls the timestamp column by name;   receiving a query from an application to obtain a timestamp value from the hidden timestamp column, the query calling the timestamp column by name; and   in response to the query, the hidden timestamp column returning the timestamp value to the application for use by the application,   wherein the application uses the returned timestamp value for controlling a locking scheme associated with recording data updates in the database system wherein the locking scheme retrieves the hidden timestamp column.   
   
   
       2 . The medium of  claim 1 , wherein the timestamp value corresponding to a given row of data in the table is automatically updated each time data in the given row has been modified. 
   
   
       3 . A system, comprising:
 a database system comprising at least one table, the at least one table including a plurality of rows of data;   an application capable of querying the database system;   a hidden timestamp column in the at least one table in the database system, the hidden timestamp column including a timestamp value for each row of data in the table, the timestamp value indicating a last time a corresponding row of data in the table was previously modified, wherein the hidden timestamp column does not appear in the database schema by default and exposes the timestamp value for a given row of data in the table only to a query that calls the timestamp column by name; and   means for receiving a query from an application to obtain a timestamp value from the hidden timestamp column, the query calling the timestamp column by name;   wherein the application uses the timestamp value for controlling a locking scheme associated with recording data updates in the database system wherein the locking scheme retrieves the hidden timestamp column.   
   
   
       4 . The system of  claim 3 , wherein the timestamp value corresponding to a given row of data in the table is automatically updated each time data in the given row has been modified.