AHM cannot lag behind

Navin_C

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.

Thanks

Abhishek_Rana

Hi,

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(); 

Regards'

Abhishek

Navin_C

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 ?