Historical Queries
Vertica can execute historical queries, which execute on a snapshot of the database taken at a specific timestamp or epoch. Historical queries can be used to evaluate and possibly recover data that was deleted but has not yet been purged.
You specify a historical query by qualifying the SELECT statement with one of the following clauses:
AT EPOCH LATEST
|
Queries all data in the database up to but not including the current epoch. The result set includes data from the latest committed DML transaction |
AT EPOCH epoch‑number
|
Queries all data in the database up to and including the specified epoch. |
AT TIME
|
Identifies the epoch that spans the specified timestamp. |
Historical queries return data only from the specified epoch. Because they do not return the latest data, historical queries hold no locks or blocking write operations.
Query results are private to the transaction and valid only for the length of the transaction. Query execution is the same regardless of the transaction isolation level.
Restrictions
- The specified epoch, or epoch of the specified timestamp, cannot be less than the Ancient History Mark epoch.
- Vertica does not support running historical queries on temporary tables.
Important: Any changes to a table schema are reflected across all epochs. For example, if you add a column to a table and specify a default value for it, all historical queries on that table display the new column and its default value.
