The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

get_last_good_epoch() not advancing so can't drop old projections

Hi, We cannot drop old projections due to the following message: ROLLBACK 4470: Projection cannot be dropped because history after AHM would be lost We tried running make_ahm_now() but it returns the following: select make_ahm_now(); make_ahm_now ----------------------------------------- AHM already at LGE (AHM Epoch: 1898150) (1 row) Is this normal? Note that the last_good_epoch value has not changed in quite a few days: select * from vs_epochs where epoch_number = 1898150; epoch_number | time --------------+------------------------------- 1898150 | 2013-09-25 13:05:08.127051-04 (1 row)

Comments

  • Hi Emanuel,
    There might be a variety of reasons. Could you please open a case in Salesforce so the Tech support person will assist you to resolve the issue. It would be best if you upload the logs by the time when you open the case.

    Please let me know if you have any questions.

    Thanks

    Bhawana

  • HistoryRetentionTime -

    Determines how long deleted data is saved (in seconds) as a historical reference. The default is 0, which means that Vertica saves historical data only when nodes are down. Once the specified time has passed since the delete, the data is eligible to be purged. Use the -1 setting if you prefer to use HistoryRetentionEpochs for determining which deleted data can be purged.

    Note: The default setting of 0 effectively prevents the use of the Administration Tools 'Roll Back Database to Last Good Epoch' option because the AHM remains close to the current epoch and a rollback is not permitted to an epoch prior to the AHM.

    Tip: If you rely on the Roll Back option to remove recently loaded data, consider setting a day-wide window for removing loaded data; for example:

    SELECT SET_CONFIG_PARAMETER ('HistoryRetentionTime', '86400');

  • Hi,


    LGE not advancing because of old data stuck in WOS due to moveout stuck in replay delete due to large number of deletes in WOS data. On move out of the old data from WOS, LGE advanced. Suggested  migrating all their scripts to verify the existence of records before issuing a delete to avoid hitting this again in the future.

    Also redesigning projections for optimizing for delete, update also helps in preventin moveout stuck in replay delete. 

    https://my.vertica.com/docs/6.1.x/HTML/index.htm#12675_1.htm


    Thank you


    Bhawana

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.