Is it possible to know when the data in a particaluar table was updated

ayang_xu

in MySQL, we can use the following SQL statement to get the last update time:
SELECT UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '${TABLE_NAME}'

Is there any SQL doing the same thing in Vertica?

eli_revach

Hi Vertica has an hiden column named by epoch this column hold the last epoch id that the given record has at a commit time . the column is created automaticly at the creation time of the table and you can simply query is value. Thanks

knicely87

http://vertica-forums.com/viewtopic.php?f=3&t=1868

Navin_C

Hi Jim,

I have seen epochs table does not keep the data for long time.

So if we update the table and use this method to check the time of the epoch, at the same instant, it will show the last updated time, but if we  query the epochs table after long interval, you will find  the epoch which the table had for last inserted record, does not exist in epochs table.

Is there a parameter to increase data retention of this table,
Ideally this table should show records for all epochs and there closed time as per the docs.

Is there any other table where we can track this information.

Thanks

knicely87

Maybe you can create your own epochs table and update it every hour or so vis a cron job?

See here for an example:
http://vertica-forums.com/viewtopic.php?f=3&t=1868&p=6544#p6544

ayang_xu

thanks, is there a way to get the last update time of the table?

eli_revach

Hi ,
In Vertica 7.1 we can now set retention policy for the Data Collector tables on time based interval unites .

 

Example :

=> SELECT set_data_collector_time_policy('TransactionEnds', '1 day'::interval);
set_data_collector_time_policy
--------------------------------
SET

 

This command  set  retention policy for  dc_transaction_ends table for one day , having this setting set ,  we can easily know the update time of the last day data  using below query :

 

 

select count(*) ,
time_slice(tx.time,600, 'second') AS moving_interval
from <Your table> as arc ,(select max(time) as time ,end_epoch from v_internal.dc_transaction_ends where time between sysdate()-1 and sysdate() group by end_epoch ) tx
where arc.epoch=tx.end_epoch
group by moving_interval
order by moving_interval

 

Thanks