Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Purge does not remove deleted data

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

 

 

Comments

  •  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

     

  • 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

     

     

     

  • 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.

  •  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."

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.