Flashback Query?

Moderator: NorbertKrupa

Post Reply
User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Flashback Query?

Post by fsalvelt » Fri Mar 23, 2012 8:56 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Flashback Query?

Post by JimKnicely » Mon Mar 26, 2012 4:09 pm

Hi,

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.
Here is an example using Epochs.

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
Let's get the EPOCH information from the system table:

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)
Now let's add another row to out table:

Code: Select all

dbadmin=> insert into hist_test values (2, 'Value #2');
 OUTPUT
--------
      1
(1 row)

If we do a generic SELECT we should see all the rows we've inserted:

Code: Select all

dbadmin=> select * from hist_test;
 col1 |   col2
------+----------
    1 | Value #1
    2 | Value #2
(2 rows)
Now lets run some SELECTS at different EPOCHS. Notice how the data returned changes and that we always only see committed data:

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)
I hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Flashback Query?

Post by scutter » Tue May 21, 2013 2:40 am

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 Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “New to Vertica Database Development”