AHM cannot lag behind

Navin_CNavin_C Vertica Customer
Hello All,

A very strange issue coming up when trying to drop projections.

I am trying to drop unwanted projections which are also super projections, but there is a exact copy of these projections segmented on lesser nodes.

So I go ahead and drop projections this way

drop projection xx.xxxx_b0, xx.xxxx_b1

After doing this I get such a message 
ROLLBACK 4470:  Projection cannot be dropped because history after AHM would be lost
DETAIL:  Current AHM epoch = 143745
HINT:  Use make_ahm_now() to move the AHM epoch to the present. Use set_ahm_epoch() only if you rely on historical queries (AT EPOCH/TIME)
I checked the epochs:
dbadmin=> select current_epoch,ahm_epoch,last_good_epoch,refresh_epoch from system; current_epoch | ahm_epoch | last_good_epoch | refresh_epoch
        145135 |    143745 |          145134 |        143746
Now when I try using make_ahm_now() as in the hint.
dbadmin=> select make_ahm_now();ERROR 2154:  AHM must lag behind the create epoch of unrefreshed projection xx.xxxx_v1_b0 (Create epoch: 143746)
Checked in the docs

ERROR 2154: AHM must lag behind the create epoch of unrefreshed projection string (Create epoch: value)

also checked the timing for epochs

select * from vs_epochs where epoch_number in(145135,143745,145134,143746);
143745 - 2014-04-01 19:41:14
143746  -2014-04-03 11:40:47
145134  -2014-04-09 10:40:03
145135  -2014-04-09 11:53:17

Why is the ahm not advancing further, Also to add I had just deployed new projection and done refresh operation on database for many projections one day before.



  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee

    There can be any reason for AHM not advancing, which can be clear from logs. It can be because of "too many ros containers" or delete vectors present in 
    system or some other reason, whcih can be more clear from vertica.log

    You could use a query as below to find all projections with a large % of deletions. 
    Deletes cause query performance issues and can hold back the AHM/LGE as you saw.

    => select dv.projection_name,deleted_row_count*100/row_count as PctDeleted from delete_vectors dv,projection_storage ps 
    where ps.projection_schema = dv.schema_name and ps.projection_name = dv.projection_name order by projection_name;

    Try to do following:

    1. Either, refresh the projection xx.xxxx_v1_b0 and then advance AHM using "select make_ahm_now();

    2. OR, drop projection xx.xxxx_v1_b0 and then advance AHM using "select make_ahm_now(); 


  • Navin_CNavin_C Vertica Customer
    Thanks Abhishek,

    I figured it out.
    There were few projection/tables which were not refreshed.

    Refreshed them and make_ahm_now started working. 
    Any good document where I can read about epochs other then Vertica documentations ?

Leave a Comment

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