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

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?

Comments

  • 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
  • Navin_CNavin_C Vertica Customer
    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

  • 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


  • thanks, is there a way to get the last update time of the table?
  • 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 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file