Unfortunately there isn't any transactional or table level "undo". Hence here you'd have to figure out if you can reload the deleted data. However, You can rollback the entire database to some previous LGE. It would let you go back to the epoch for the AHM. If your history retention is set to 0 then AHM is one epoch prior to current epoch and probably won't get you back far enough in time. The HistoryRetentionTime configuration parameter determines how long deleted data is available before it's eligible to be purged.
You can find the history settings and the current and AHM epoch values as below.
If you have history retention setup to maintain 3 hour history (configuration parameters "HistoryRetentionTime" or "HistoryRetentionEpochs"),then you can probably query those deleted records using a historical query.
vsql=> AT TIME 'timestamp' select * from table; or vsql=> AT EPOCH <epoch number> select * from table;
Comments
Unfortunately there isn't any transactional or table level "undo". Hence here you'd have to figure out if you can reload the deleted data.
However, You can rollback the entire database to some previous LGE. It would let you go back to the epoch for the AHM. If your history retention is set to 0 then AHM is one epoch prior to current epoch and probably won't get you back far enough in time. The HistoryRetentionTime configuration parameter determines how long deleted data is available before it's eligible to be purged.
You can find the history settings and the current and AHM epoch values as below.
select current_epoch,ahm_epoch from system;
> current_epoch | ahm_epoch
---------------+-----------+
98 | 97
vsql=> AT TIME 'timestamp' select * from table;
or
vsql=> AT EPOCH <epoch number> select * from table;
Vertica Doc:
https://my.vertica.com/docs/7.1.x/HTML/index.htm
Programmer's Guide --> Writing Queries --> Historical (Snapshot) Queries
If history retention is off, these records are probably purged by now.
Hope this helps.
Thanks.