Hi Robert, as of 10.11 there is @@system_versioning_insert_history, so the only thing you need is the ability to delete history rows. As UPDATE may be done as DELETE + INSERT. You should not have many such kind of operations so it would be performance-critical, should you? If we can delete the whole history why not delete the specific rows? The original idea was to keep contiguity and consistency of all history, but surely the user requirements may be much more versatile. I personally vote for maximum power for users keeping the limitations as non-default minor utilities for specific areas like banking, government, etc. So, yes DELETE HISTORY .. WHERE is a nice idea and coincidentally I just thought about it yesterday when it was needed for implementation of System Versioning in Spider.
Why must the history be dropped when I disable it for a table again? Could it be kept?> By the way the "simple" drop of system versioning didn't work. Needed to drop ts, te cols, period and the system versioning in one alter.
So it is one more feature to drop just SYSTEM VERSIONING and it retains the old history but does not generate new history. Again, it is something that violates the original idea of history consistency and contiguity, but finally why not? So if you've dropped just SYSTEM VERSIONING but hadn't dropped PERIOD history should not be generated but temporal queries should work. When dropped PERIOD system fields should become normal fields and history should become current data. I think that is the most user-friendly way, as you have the freedom to delete history or not. Before dropping PERIOD history may be deleted by DELETE HISTORY, after dropping PERIOD history may be deleted by the usual "DELETE .. WHERE row_end <" command. Thanks, Aleksey On Sun, Jan 15, 2023 at 6:34 PM Robert Palm <developer@robert-palm.de> wrote:
Dear devs,
I am reaching out to you, because I wanted to discuss a feature request for the history of system versioned temporal tables.
Is it possible to add a special setting so that I can update and delete the history?
Update, e.g. because sometimes it is necessary to pseudo-/anonymize former customer data.
Delete, e.g. because former customers demand all data must be deleted.
German dsgvo rules...
I know it opposes the idea of having a non changeable history.
But I think there are cases where it is justified and needed.
So I am asking for something like
system_versioning_delete_history system_versioning_update_history
Or does there exist some other workaround to do this?
Other ideas: - add a where clause to "delete history" - add update history with where clause - drop system versioning, do the update/ delete and enable system versioning again in one commit?
Why must the history be dropped when I disable it for a table again? Could it be kept?
By the way the "simple" drop of system versioning didn't work. Needed to drop ts, te cols, period and the system versioning in one alter.
Another question: How to migrate system versioned tables with SQL?
Thank you!
Kind regards
Robert _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp