Can I remove some data from immutable table without leaving a trace

Sergey_Cherepan_1Sergey_Cherepan_1 ✭✭✭
edited March 2023 in General Discussion

Hi,

I just read in Vertica docs about immutable tables in v 12.0.3 - cool feature!

And I got an idea how I can remove recent data from immutable table without leaving a trace.

I will set AHM to stop advancing, had data loaded into immutable table, and then rollback to AHM.

That should remove newly inserted data from immutable table without leaving a trace.

Please let me know if it is going to work.

Thank you
Sergey

Best Answer

  • VValdarVValdar Vertica Employee Employee
    Answer ✓

    You can also drop or truncate an immutable table, it only deny update / delete operations.
    So you can perfectly create a classic table, insert data from immutable, change whatever you want in the classic, truncate the immutable and move back the data.
    It's extra work and requires more privileges (create & truncate table), but still doable.

Answers

  • Sergey_Cherepan_1Sergey_Cherepan_1 ✭✭✭
    edited March 2023

    @Bryan_H @SruthiA @VValdar any comments?

  • Vertica_CurtisVertica_Curtis Employee

    I'm not sure "rollback to AHM" is actually a thing. Also, how exactly would you stop the AHM from advancing?

  • Sergey_Cherepan_1Sergey_Cherepan_1 ✭✭✭

    You can stop advancing AHM in several ways - shutdown node, special database parameters, or using undocumented functions.
    Though rollback to AHM it is not needed, immutable table really prevent only delete and update. As been correctly pointed out, you can drop or truncate whole table, or drop partitions.
    Immutable table is not designed to prevent any changes to data.
    Immutable table is a nice safeguard against accidental data loss, definitely very useful feature.

This discussion has been closed.