Purge does not remove deleted data

Arvind_Kumar

Hi 

 

I am using 7.1.2 and i purge tables having highest deleted data , i purge the data but deleted data does not go away.

 

 

##Deleted rows

Step 1. SELECT schema_name
,projection_name
,count(*) num_ros
,sum(total_row_count) num_rows
,sum(deleted_row_count) num_deld_rows
,sum(delete_vector_count) Num_dv
,(sum(deleted_row_count) / sum(total_row_count) * 100)::INT per_del_rows
FROM storage_containers
WHERE node_name = ( SELECT local_node_name())
GROUP BY 1, 2
HAVING sum(deleted_row_count) > 0
ORDER BY 6 DESC;

 

step 2. select make_ahm_now();

step 3. vertify AHM

step 4. purge table corresponding to projections which have high deleted rows

 

when i check deleted rows for tables for which i purged it shows the same deleted rows count.

 

 

it happens only for some tables

 

Any help

 

 

Adrian_Oprea_1

 Do you use

 

SELECT PURGE_TABLE();

 

for you purge part ? 

 

Note:

twill purge purge historical data up to and including the epoch in which the Ancient History Mark is contained.

 

Look at you epochs to see until when the purge will reach

SELECT * FROM epochs;

 

 

Maybe This query can help you:

SELECT
    node_name,
    projection_schema schama,
    projection_name   p_name,
    is_up_to_date     UTD,
    checkpoint_epoch  CPE,
    would_recover     WR,
    is_behind_ahm     BAHM
FROM
    projection_checkpoint_epochs;

and lastly but i think is important check out this paper of Epochs

https://community.dev.hpe.com/t5/Vertica-Knowledge-Base/Understanding-Vertica-Epochs/ta-p/233749

 

Arvind_Kumar

Adrian,Thanks for your reply

 

 

 I am using select purge_table('schema_name.table_name');

 

this my delete vector 

schema_name | projection_name | num_ros | num_rows | num_deld_rows | Num_dv | per_del_rows
----------------+------------------------------------+---------+------------+---------------+--------+--------------
schema_name | Prohjection_name_aggr_sp_b0 | 195 | 2092070626 | 9289732 | 20 | 0
schema_name | Prohjection_name_aggr_sp_b1 | 195 | 2092029113 | 9294447 | 20 | 0
schema_name | Prohjection_name_aggr_wkly_v1_b0 | 195 | 2090318534 | 9285078 | 20 | 0
schema_name | Prohjection_name_aggr_wkly_v1_b1 | 195 | 2093123136 | 9280697 | 20 | 0

 

select make_ahm_now();

current_epoch | ahm_epoch | last_good_epoch 

---------------+-----------+-----------------

        764314 |    764313 |          764313

 

select purge_table('schema_name.table_name');

 

after purge getting same data as before

 

schema_name | projection_name | num_ros | num_rows | num_deld_rows | Num_dv | per_del_rows
----------------+------------------------------------+---------+------------+---------------+--------+--------------
schema_name | Prohjection_name_aggr_sp_b0 | 195 | 2092070626 | 9289732 | 20 | 0
schema_name | Prohjection_name_aggr_sp_b1 | 195 | 2092029113 | 9294447 | 20 | 0
schema_name | Prohjection_name_aggr_wkly_v1_b0 | 195 | 2090318534 | 9285078 | 20 | 0
schema_name | Prohjection_name_aggr_wkly_v1_b1 | 195 | 2093123136 | 9280697 | 20 | 0

 

select epoch from schema_name.projection_name where epoch > ahm_epoch;

 epoch 

-------

(0 rows)

 

It means all epochs in table are behind the ahm_epoch it should be purged but it did not got purged

 

 

 

Arvind_Kumar

HI

 

when i run purge_table(schema_name.table_name) it does not purge but when i replace this purge_table with

select do_tm_task('mergeout', 'schema_name.table_name);

 

it works.

 

Why this happens ?

 

effectively purge_table should work effectively but this is not happening.

Adrian_Oprea_1

 Ok , Not sure why the purge wont actually remove them, but mergeout will trigger a purge on the objects your try to merge.

"A mergeout is the process of consolidating ROS containers and purging deleted records."