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

Emanuel_Pordes

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)

Bhawana

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

Daniel_Leybovic

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 <CODE>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:

<CODE>SELECT SET_CONFIG_PARAMETER ('HistoryRetentionTime', '86400');

Bhawana

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