Flashback Query?
Moderator: NorbertKrupa
Flashback Query?
Hi, In Oracle I can do Oracle flashback queries where I can query data as it was as of a certain time stamp or scn (system change number). Is this possible in Vertica?
Thank, Fred
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Flashback Query?
Hi,
There is a similar feature in Vertica implemented as Historical (Snapshot) Queries.
There are two options:
First I'll create a test table:
Let's get the EPOCH information from the system table:
Now let's add another row to out table:
If we do a generic SELECT we should see all the rows we've inserted:
Now lets run some SELECTS at different EPOCHS. Notice how the data returned changes and that we always only see committed data:
I hope this helps!
There is a similar feature in Vertica implemented as Historical (Snapshot) Queries.
There are two options:
- Query all committed data in the database up to, but not including, the current epoch
Query all committed data in the database up to the time stamp specified. AT TIME 'timestamp' queries are resolved to the next epoch boundary before being evaluated.
First I'll create a test table:
Code: Select all
dbadmin=> create table hist_test (col1 int, col2 varchar(100)) order by col1;
CREATE TABLE
dbadmin=> insert into hist_test values (1, 'Value #1');
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
Code: Select all
dbadmin=> select current_epoch, ahm_epoch, last_good_epoch from system;
current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
299261 | 299258 | 299259
(1 row)
Code: Select all
dbadmin=> insert into hist_test values (2, 'Value #2');
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> select * from hist_test;
col1 | col2
------+----------
1 | Value #1
2 | Value #2
(2 rows)
Code: Select all
dbadmin=> at epoch 299259 select * from hist_test;
col1 | col2
------+------
(0 rows)
dbadmin=> at epoch 299260 select * from hist_test;
col1 | col2
------+----------
1 | Value #1
(1 row)
dbadmin=> at epoch 299261 select * from hist_test;
ERROR: Epoch number out of range
HINT: Epochs prior to [299259] do not exist. Epochs [299261] and later have not yet closed.
dbadmin=> commit;
COMMIT
dbadmin=> at epoch 299261 select * from hist_test;
col1 | col2
------+----------
1 | Value #1
2 | Value #2
(2 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Flashback Query?
Note that if you are going to truly rely on the availability of past epochs, you'd want to change the configuration parameters that control how many epochs / how much time the Ancient History Mark preserves. By default it's very aggressive and stays within minutes of the current epoch.
--Sharon
--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC